Trying to compare data in three lists

R

rickott1

I have three different columns/list all different lengths.

Column A: contains a list of datasets that have been created

Column B: contains a list of all datasets that are in existence

Column C: contains a list of all datasets that need to be discontinued

I am trying to:

1) Find all datasets that exist in column/list 2 as well as i
column/list 1 and mark the results/hits "Ok"
and
2) Find all datasets that exist in column/list 2 but not in column/lis
1 and mark the results/hits "Review"
3) Find all datasets that exist in column/list1 and in column/list3 an
mark the results/hits "Delete"

By using the Consolidation menu I was able to get results indicatin
wether a dataset appeared in List 1 and not in 2, or appeared in Lis
2 but not in 1, or if it appeared in both lists. I was not able to ge
results for a compare of list 1 and 3,a nd in addition I was not abl
to mark the results as described above.

Should you need further calrification, please feel free to email me
[email protected]

Thank you
 
D

Dave Peterson

I think I'd do this:

Create a new worksheet.
Copy each of your lists into column A (one giant list with only row of headers)
Then use data|filter|advanced filter to eliminate all the duplicates
(Copy the unique values to a new range (B1 of the same sheet).
delete column A.

Sort the unique list in Column A (it's moved over one) (if you want).

Then put this in B1: In List 1
C1: In List 2
D1: In list 3

In b2, put this:
=isnumber(match(a2,sheet1!A:A,0))
in C2, put this:
=isnumber(match(a2,sheet1!B:B,0))
In D2, (you got it!)
=isnumber(match(a2,sheet1!C:C,0))

Now copy down your unique list.

You'll end up with a bunch of True's and false's.

Put this in E1: Message.

select columns A:E
Apply Data|filter|autofilter.

Filter to show TRUE in column B and then Filter for True in column C.
Put a your message in those visible cells in E: OK

data|filter|show all (to see all the data)

filter on True in column B. Filter for False for column C.
Put Review in the visible cells in column E.

data|filter|show all
filter on true in both B and C
put Delete in the visible cells in column E
(or F if you have some messages you don't want to lose)

Then data|filter|show all.

filter on column E to print(?) just the ones you want.
 
Top