Anyone got a talent for excel?

[Chad Sexington]

I'm bashing my head against a wall. I feel like the answer is so obvious but it's eluding me. Included is a screenshot of an example.

Basically I need to compare two columns, and say how many times for Column A there is a unique value in Column B. In the screenshot I've attached, Columns D and E show my ideal output, but how do I write a formula that will display that? Aaaaah.

Thank you.

 

[Dave]

How big is your data set? Does it have to be a formula? This is perfect for a pivot table.

 

[Dave]

But if it HAS to be a formula, use:

=SUMPRODUCT((($A$2:$A$24=D2))/COUNTIFS($A$2:$A$24,$A$2:$A$24&"",$B$2:$B$24,$B$2:$B$24&""))

Be sure you hit Ctrl+Shift+Enter on this instead of just Enter.

 

[Chad Sexington]

How big is your data set? Does it have to be a formula? This is perfect for a pivot table.

14000 lines in the real version

 

[chris]

14000 lines in the real version

Yeah, like @Dave said. The pivot table will be your friend with so many lines.

 

[Dave]

But the formula works just fine if you use Ctrl+Shift+Enter after putting it in. In fact, you can cut & paste what I put into the sheet example you gave in cell E2 and then drag it down to E3 as well. It's why I used the hard coded ranges.

 

[Chad Sexington]

Yeah, like @Dave said. The pivot table will be your friend with so many lines.

I need to learn more about these. I tried using one earlier on but I wasn't getting what i needed

 

[Chad Sexington]

Thanks @Dave I continue to think I'm good at excel just bash into a new and exciting challenge!

 

[Dave]

Pivot tables are stupid easy once you've played with them a bit.

I have attached a sample Excel sheet. One tab has the data as well as a formula set up as above. The second tab has a pivot table that shows pretty much the exact same thing. The formula took a pretty decent amount of time to type out & get right. The pivot table took like 4 seconds.

Check it out. I left the pivot data side open so you could see what I did.

 

[Chad Sexington]

Pivot tables are stupid easy once you've played with them a bit.

I have attached a sample Excel sheet. One tab has the data as well as a formula set up as above. The second tab has a pivot table that shows pretty much the exact same thing. The formula took a pretty decent amount of time to type out & get right. The pivot table took like 4 seconds.

Check it out. I left the pivot data side open so you could see what I did.

You're a fucking gem.

Where should I go to learn excel, if I wanted to 'git gud'?

 

[Dave]

LinkedIn Learning is decent.

 

[Bubble181]

Or you can just be around when things are starting to get invented and get in on the ground floor, like @Dave.

... What? Excel isn't that old!

Okay, okay, I admit I was going to follow up with him taking courses with Pythagoras and Euclid. But I didn't! See? I'm learning!

... Enough to know I'll just head for that corner over there now.

... That right angle, which has lots of interesting properties, as @Dave can explain to you, no doubt.

... Sorry, I'm on vacation with some people who share my sense of humor, it's not going well

 

[Dave]

You're on vacation with people who have the same sense of humor as you? Is the tour bus really short?

 

[jwhouk]

...I learned on Lotus 1-2-3, Owl Boy.

 

[Bubble181]

You're on vacation with people who have the same sense of humor as you? Is the tour bus really short?

I'm driving we haven't died yet!