Excel

[mister d]

Lets say the cities are in column D, you'd copy column D into a new column (pretend its column G) then go to Data > Remove Duplicates to create your list of unique cities. Then in Column H next to each city, you'd do =COUNTIF(D:D,H1) and H2, H3, H4 ... on down. The graph would run off G-H.

[mister d]

The only real issue would be some new-to-the-list city being added after the summary/graph are created.

[brian]

The only real issue would be some new-to-the-list city being added after the summary/graph are created.

That's not a concern since the data set is final. So this helps. I'll play around and see if I can't figure it out. Thanks.

[mister d]

I can email an example later on if you need it.

[brian]

I can email an example later on if you need it.

No that worked perfectly. And I don't even need the graph I just need the numbers for each city so that was easier than I would have guessed. I owe you a beer.

[wlu_lax6]

Would building a summary section, using COUNTIF and running the graph off there count as manual?

No, I meant literally manual. Like all I would know how to do is sort by that column and then "tally" up the results. So anything that involves even a few steps is fine.

A simple Pivot chart should do this.
You could also do a Pivot Table and then copy the table and use the graph feature.

[brian]

Yeah, I need to learn more about pivot tables. Mr. D's suggestion worked fine but it didn't look great, but for these customers I don't care because they're assholes. But for customer I like I might want to jazz it up a bit.

[mister d]

If the list doesn’t look pretty you didn’t make a pretty list.

[brian]

If the list doesn’t look pretty you didn’t make a pretty list.

Yeah I should say I could have put more work into presentation but that’s on me.

[Square Rob]

If you want to make pretty presentations and charts I highly suggest ‘think-cell’. It’s a German software which piggy backs off of both excel and PowerPoint. They’ve done a fantastic job of integrating the two (much better than Microsoft), plus their graphical program is much more easily manipulated to make very pretty charts. You can also easily add additional bells and whistles to your charts to show things like peak to peak differences (and report the difference as an absolute or a %), etc.

It’s a great program and it can make your presentations stand out from the typical.

[Square Rob]

Let's say you're hypothetically dealing with a bunch of lazy Canadians.

And let's say you're providing them data in an Excel file that lists a city for each person in a column and there are 25,000 rows. (There's other data in there, but they don't care about that.)

How could you use Excel to list the number of times each city appears in the report in a graph or some other way to represent how many times each city is represented without having to compile it manually?

For example:

Toronto - 352
Oshawa - 283
Thunder Bay - 218

etc., etc.

Pivot table definitely the easiest way. Highlight all of your cities in the raw data, including a column header (‘cities’ for the example below. Go -> Data -> Insert -> Pivot Table. Click through and let it create a new sheet. When the pivot table template shows up, drag your cities header rectangle block down into the summary box (bottom right) and then click on that block to make sure it’s on ‘count’. It should show you exactly what you want. You can sum, average, stdev, plus a lot of other functions. You can also sum data related to the cities data if you wanted. If say you had additional data in different columns in your raw data table, you can drag the cities block to the rows box (bottom left), and then pull whatever other variables you want to the sum box and pick your calculations.

Pivot tables are extremely useful for parsing big data sets into more manageable data. They are also really useful for grouping time based data into days/weeks/months/years, etc.

[wlu_lax6]

Let's say you're hypothetically dealing with a bunch of lazy Canadians.

And let's say you're providing them data in an Excel file that lists a city for each person in a column and there are 25,000 rows. (There's other data in there, but they don't care about that.)

How could you use Excel to list the number of times each city appears in the report in a graph or some other way to represent how many times each city is represented without having to compile it manually?

For example:

Toronto - 352
Oshawa - 283
Thunder Bay - 218

etc., etc.

Pivot table definitely the easiest way. Highlight all of your cities in the raw data, including a column header (‘cities’ for the example below. Go -> Data -> Insert -> Pivot Table. Click through and let it create a new sheet. When the pivot table template shows up, drag your cities header rectangle block down into the summary box (bottom right) and then click on that block to make sure it’s on ‘count’. It should show you exactly what you want. You can sum, average, stdev, plus a lot of other functions. You can also sum data related to the cities data if you wanted. If say you had additional data in different columns in your raw data table, you can drag the cities block to the rows box (bottom left), and then pull whatever other variables you want to the sum box and pick your calculations.

Pivot tables are extremely useful for parsing big data sets into more manageable data. They are also really useful for grouping time based data into days/weeks/months/years, etc.

And the newest version has a "Recommend Pivot Table" feature that is also good for getting your started.

[EnochRoot]

OK so my father has a spreadsheet where he tracks all the golfers in his community. His job is to coordinate the Saturday start times.

Probably 20 golfers. Any given Saturday there’s probably a dozen golfers who need a tee time. He wants to setup a formula that, when there’s a ‘1’ in a golfer’s row, then that golfer’s email address (found in another column) will append to a list of email addresses. He’d then copy that cell and dump it in an email to announce their times, etc.

Say, column A is the name, B is the email address, and C is the ‘1’ or ‘0’ if he’s golfing that Saturday.

Is this concatenation? Appending strings?

Let’s cut to the chase: how the hell do I do this?

[degenerasian]

sounds like a conditional statement, if status = 1 then give him a tee time. do you need another column with the tee times? pairings, threesomes?

[EnochRoot]

sounds like a conditional statement, if status = 1 then give him a tee time. do you need another column with the tee times? pairings, threesomes?

No nothing that complicated.

So how would the formula look to append a list? All apologies here - I haven’t dealt in Excel in about 10-12 years.

[EnochRoot]

So you’d select all the email addresses in the email column, and then something like IF “1” in column C, then place email in a string in the target cell?

Sounds good. But no idea how to build it.

[EnochRoot]

I’m doing some digging...

https://www.howtoexcel.org/formulas/how ... e-formula/

Is this what I want?

[degenerasian]

That might work, i'm trying to find an easier way

[degenerasian]

I think this will work

column A is the name, B is the email address, and C is the ‘1’ or ‘0’

In column D put in the first row =IF(C1=1,B1&"")
then in the second row put in =IF(C2=1,D1&" "&B2,D1)

from cell D2 drag the formula down to the bottom of your list, the last cell should be all the emails you want.

[EnochRoot]

I think this will work

column A is the name, B is the email address, and C is the ‘1’ or ‘0’

In column D put in the first row =IF(C1=1,B1&"")
then in the second row put in =IF(C2=1,D1&" "&B2,D1)

from cell D2 drag the formula down to the bottom of your list, the last cell should be all the emails you want.

Thank you. I’ll give this a shot.

[EnochRoot]

I think this will work

column A is the name, B is the email address, and C is the ‘1’ or ‘0’

In column D put in the first row =IF(C1=1,B1&"")
then in the second row put in =IF(C2=1,D1&" "&B2,D1)

from cell D2 drag the formula down to the bottom of your list, the last cell should be all the emails you want.

This is great! How would we build this string to delimit the values with a comma?

[mister d]

=if(C1=0,"",D1&",")&if(C2=0,"",D2&",")&if(D3= ... until the end. It'll build it out into a single cell skipping all of the column D cells that don't have emails.

[A_B]

I was thinking a series of if statements but I got distracted and didn’t come back to it.

[EnochRoot]

Thanks guys. We just added a comma in the cell after the email address and that did the trick.

[mister d]

Next level excel is oversolving your problems to avoid any manual fixes at all.

[A_B]

Next level excel is oversolving your problems to avoid any manual fixes at all.

I have definitely spent a day gettin a furmila right when I could have done same with simple math in an hour.

[EnochRoot]

OK, so let's say you have a spreadsheet chart where you're attempting to show revenue by quarter over a 3 year period, but for four different products. What's the most effective chart to show this? I want to say Pivot Chart where you could just activate different years, but what about a scatter chart for all 3 years in one chart? With 12 data points each, is that overkill?

[teeteebee]

A bar graph could work. With each qtr broken down into four colored bars. Each quarter shows the change by product.

[EnochRoot]

A bar graph could work. With each qtr broken down into four colored bars. Each quarter shows the change by product.

Yeah. I can see that. But that's 12 data points each. Is that sensory overload?

Might you use a table with embedded charts for each product?

[mister d]

Stacked bar chart?


(It’s somewhat limited in you can only really sight compare the bottom product and overall, but it’s cleaner than 48+ bars.)

[teeteebee]

Stacked bar chart?


(It’s somewhat limited in you can only really sight compare the bottom product and overall, but it’s cleaner than 48+ bars.)

Stacked bar chart would look good. 48 narrow bars wouldn’t be too bad. Believe I used something similar before - for regional analysis by month.

[EnochRoot]

Stacked bar chart?


(It’s somewhat limited in you can only really sight compare the bottom product and overall, but it’s cleaner than 48+ bars.)

Maybe I'm just thinking too linear, but how would 12 stacked bars be more convincing than 4 lines?

[mister d]

Just depends on what you want to show I guess? If total revenue isn’t a factor, 4 lines works. If total revenue matters and you want to show components, the stacked bar is probably better.

[Shirley]

Yeah, it all depends on the goal of the chart. If each product needs to be tracked separately, then a grouped bar chart is probably best. If it's total revenue that's most important, but you still want to see the contribution of each product, then a stacked bar chart is better.

[Square Rob]

Yeah, it all depends on the goal of the chart. If each product needs to be tracked separately, then a grouped bar chart is probably best. If it's total revenue that's most important, but you still want to see the contribution of each product, then a stacked bar chart is better.

I’d do a combination chart, where the total revenue is represented by a line which is overlaid on a 100% stacked bar chart showing the % rev for each product line. That shows the overall revenue dynamic quite nicely.

[EnochRoot]

Thanks for the help guys. I’m going to start with the grouped bar chart and see where it goes from there.

[brian]

This is peak Harbaugh here.

[mister d]

Word sucks ass. Team Harbaugh.

[wlu_lax6]

This is peak Harbaugh here.

As a person who worked for the Microsoft Office team....I am with the coach here.

[A_B]

I love me some excel. But that’s a crime against humanity.