counting duplicate names

  • Thread starter Thread starter twototango
  • Start date Start date
T

twototango

I have a lot of names in rows across several spreadsheets. I need to know
the number of unduplicated names across all spreadsheets. Have not gotten
anything to work thus far.

Thanks!
 
Some thoughts. Do a one-time manual copy n sequential paste of all names from
all sheets into a single col in a new sheet, eg into col A. Then you could
apply Advanced Filter > Uniques on that col A. Or perhaps better still, just
insert a col header in A1, eg: xxx, then create a pivot table on that col A,
with "xxx" dragged into both the ROW and DATA areas. The pivot will return a
col containing the unique list of names, and the corresponding counts next to
it.
 
I need to know the number of unduplicated names

I'm not sure how this could be done without consolidating the data. It would
take some time to figure out (if it can be done).
=COUNTDIFF(THREED(Sheet1:Sheet7!A1:A100))
Click to expand...

That will count unique items, not the unduplicated names.

Sheet1
Tom,Joe,Sue

Sheet2
Joe,Lisa,Tom

=COUNTDIFF(THREED(Sheet1:Sheet2!A1:A100)) = 4
Tom, Joe, Sue, Lisa

The correct answer for unduplicated names is 2, Sue and Lisa
 
counting unique items would be ok; however my spreadsheet does not recognize
COUNTDIFF.
 
With the Morefunc add-in installed:

Count of non-duplicated items across sheets...

Assume items are in the range Sheet1 A1:A10 and Sheet2 A1:A10

Create this defined name:

Goto Insert>Name>Define
Name: array
Refers to: =ARRAY.JOIN(Sheet1!$A$1:$A$10,Sheet2!$A$1:$A$10)
OK

Then, this array formula** :

=SUM(--(FREQUENCY(IF(ISNUMBER(MATCH(array,array,0)),MATCH(array,array,0)),IF(ISNUMBER(MATCH(array,array,0)),MATCH(array,array,0)))=1))

Note that the ARRAY.JOIN function is limited in size. The resulting array
must be <=65535 items and the number of range arguments is limited to 29.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Back
Top