Creating a Master List from sub-lists

[Fleone]

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!

 

[T. Valko]

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:B109,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(ARRAY.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$138,0)))

This will extract the names that correspond to the login numbers.

Select both B150 and C150 and copy down until you get blanks.

 

[Fleone]

Biff,
I must be missing something. When I paste the formula into B150, I get a
circular reference error (which is correct as far as I can tell) and the
result of the formula is the number 0. I tried it as it exists here and also
as an array with the same result.
I did install the Morefunc add-in and it is available in my list of add-ins.

 

[T. Valko]

I did install the Morefunc add-in and it is

available in my list of add-ins

Ok, here's a small sample file that demonstrates this:

xExtract Uniques from multiple rngs.xls 16kb

http://cjoint.com/?elxWRqCjVI

Uses functions from the Morefunc add-in.

 

[Fleone]

The sample file does in fact demonstrate that it works. One small
problem...the list that contains the summary is moved a few columns to the
right. I really need it to work at the bottom of the range.
I will keep working at it and see what I can come up with.