One way...
Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/morefunc/english/index.htm
Then, enter this formula in B149:
=COUNTDIFF(ARRAY.JOIN(B5:B22,B34:B51,B63:B80,B92:B 109,B121:B138))
This will return the count of unique login numbers.
Enter this formula in B150:
=IF(ROWS(B$150:B150)<=B$149,INDEX(UNIQUEVALUES(ARR AY.JOIN(B$5:B$22,B$34:B$51,B$63:B$801,B$92:B$109,B $121:B$138),1),ROWS(B$150:B150)),"")
This will extract the unique login numbers.
Enter this formula in C150:
=IF(B150="","",INDEX(C$5:C$138,MATCH(B150,B$5:B$13 8,0)))
This will extract the names that correspond to the login numbers.
Select both B150 and C150 and copy down until you get blanks.
--
Biff
Microsoft Excel MVP
"Fleone" wrote in message
...
I am trying to find a way to create a single listing that would contain all
unique values from several smaller listings.
I have 5 lists that cover B5:C22, B34:C51, B63:C80, B92:B109, B121:C138.
The
rows between these ranges have a combination of blanks and other data that
I
would not want in my master list.
List 1
Column B Column C
012345 Adam
012346 Bob
012347 Charlie
List 2
Column B Column C
012345 Adam
012346 Bob
012348 Dan
These lists cover login numbers and names. In each list there might be a
new
login number or name, or a previously used number or name might be gone.
The
lists are not named ranges.
I want to be able to look at each list (column by column) and build a
master
list
in range B150:C167 that would cover each used login and name without
allowing duplicates.
Master List based on lists 1 and 2
Column B Column C
012345 Adam
012346 Bob
012347 Charlie
012348 Dan
I tried this array formula that I found here and modified but it doesn't
quite work as it leaves items out.
I made B150 = B5 and C150 = C5 to start the range and copied this array
formula down through row 167 of each column.
=IF(ISERR(MATCH(0,COUNTIF(B$150:B166,$B$5:$B$138&" "),0)),"",INDEX(IF(ISBLANK($B$5:$B$138),"",$B$5:$B $138),MATCH(0,COUNTIF(B$150:B166,$B$5:$B$138&""),0 )))
Thanks for any assistance!