Duplicate Formula with Multiple Data Cells

I have a spreadsheet with multiple duplicates which I need to sort using multiple cells. In the attached spreadsheet I need to count column B input without counting the duplicates (only count same ce...

Sample.xlsx10 KB

excel

Excel on Mac

office 365

Detlef_Lewin
Oct 10, 2018
I'm a lazy person and don't want to think about complex formulas.
So, insert a helper column with: LEFT(A1,3)
Insert a row above the data and name the columns. My names are: AA, Place and AA-Group.
Insert a pivot table.
Check the option "Add to the data model" in the pivot table dialogue box.
"AA-Group" into rows area. "Place" into values area. Change the summarize function to "Distinct count".
And you're done.

Detlef_Lewin

Silver Contributor

Oct 10, 2018

I'm a lazy person and don't want to think about complex formulas.

So, insert a helper column with: LEFT(A1,3)

Insert a row above the data and name the columns. My names are: AA, Place and AA-Group.

Insert a pivot table.

Check the option "Add to the data model" in the pivot table dialogue box.

"AA-Group" into rows area. "Place" into values area. Change the summarize function to "Distinct count".

And you're done.

Wayde Morrison
Copper Contributor
Oct 15, 2018
This is exactly what I needed. The information was easy to extract without a complicated formula. Thank you for your assistance.

Forum Discussion

Duplicate Formula with Multiple Data Cells