# Creating a Master List from sub-lists

F

#### 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
012346 Bob
012347 Charlie

List 2
Column B Column C
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
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

#### T. Valko

One way...

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.

F

#### 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

#### 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.

F

#### 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.