Creating a Master List from sub-lists
 

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!

Creating a Master List from sub-lists
 

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!

Creating a Master List from sub-lists
 

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" wrote:

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!

Creating a Master List from sub-lists
 

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.


--
Biff
Microsoft Excel MVP


"Fleone" wrote in message
...
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" wrote:

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!

Creating a Master List from sub-lists
 

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.

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"Fleone" wrote in message
...
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" wrote:

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!