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