Forum Discussion
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 cell info once) then count cell A to correspond with the results of cell B.
How many different places did 10- go to without duplicating the places?
How many different places did 30- go to without duplicating the places?
How many different places did 80- go to without duplicating the places?
I used the following formula to determine how many individual area's were attended without duplicating the information:
{=SUM(IF(FREQUENCY(IF(B80:B390<>"",MATCH(B80:B390,B80:B390,0)),ROW(B80:B390)-ROW(B80)+1),1))}
But I don't know how to break it down further to see how many of those places 10-, 30-, and 80- were at individually?
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.
2 Replies
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.
- This is exactly what I needed. The information was easy to extract without a complicated formula. Thank you for your assistance.
