Rank: Super forum user 
|
Hi
I have our in-house list of MSDS's broken down to component parts. I also have the latest list of SVHC's. Both are held on separate Excel spreadsheets. I have been told that there is a way of setting one spread sheet to check the other for matches (in my case chemical names). Have tried the help menu but it didn't make sense.
So the question is, has anyone made use of the 'Go Compare' feature AND how the frel do you do it? Any help on this subject will be greatly appreciated.
Badger
|
|
|
|
|
|
Rank: Forum user 
|
Barrie
Chemical names won't work. I went through this years ago designing COSHH databases for Rover. There are different names for the same thing. Matching against CAS or ?EINEC? numbers is more promising but even these are not always accurate, and of course you'll have multiple CAS numbers for any product, and also multiple CAS numbers for the SVHC's.
If you can get a single unique identifier, then you could create a multi-page spreadsheet and use a conditional lookup function to flag records which matched, but I think your problem is data, not process.
Dave
|
|
|
|
|
|
Rank: Super forum user 
|
As above you need to have the same identifier in each list in order to match (other than the missing items). If you do then a further question
Will one of the lists be a master list ie will have all items and so just searching for things missing from one of the lists. Or could there be items missing from either list (hope this makes sense).
If there is a master list and id’s match then you could try :-
=ISNA(MATCH(C3,$A$3:$A$7,False))
list 1 is in column "A" and list 2 is in column "C", list 2 is the master list. (the lists can be on different sheets or workbooks). If there is NO match found it will read "True". Note A3 to A7 in my example is the full length of the list to be checked (not the master list). Also note I think the lists need to be in order (ie alphanumeric). The formula needs copying down a column across from the list of items (so the C3 reference looks at each item in turn)
Is this a one off exercise or an ongoing one?
Have fun
|
|
|
|
|
|
Rank: Super forum user
|
Just checked neither list needs to be in alphanumeric order. It seems to work if they are not.
|
|
|
|
|
|
Rank: Super forum user
|
Thanks Chris
It looks to be an ongoing exercise but with new lists when they come out. I believe I understand the formla you've given unless i missed it where would the address go for describing the two different sheets?
Badger
|
|
|
|
|
|
Rank: Super forum user
|
OK
I have now put the two lists on different workbooks first workbook is called list1.xlsx and the second workbook is called list2.xlsx. List2 is the master list (all records / items) and list1 is the incomplete list. I have kept the data in list1 in column A and in column C on list2 as per the original example. Data in both workbooks is in sheet1. I also mixed up the order of the items.
The formula is now:-
=ISNA(MATCH(C3,[list1.xlsx]Sheet1!$A$3:$A$7,FALSE))
My test data was:-
List1.xlsx List2.xlsx
Row Column A Row Column C Column D
3 abcd 3 abcd False
4 bcde 4 bcde False
5 cdef 5 defg False
6 fghi 6 efgh True
7 defg 7 fghi False
. 8 cdef False
Column D has the formula and is showing it is true there is no match for efgh as it is not is list1. If you still have problems then you will have to pm me for my email and then send me the two files and I may have a look over the weekend.
I wish the reply box you type in was the same width as the final posted entry box.
|
|
|
|
|
|
Rank: Super forum user
|
grrr
It took all the spaces out, try this
My test data was:-
List1.xlsx……………....List2.xlsx
Row…Column A……. Row….Column C…. Column D
3……..abcd……………3………abcd………...False
4……..bcde……………4………bcde………...False
5……..cdef………….…5………defg………….False
6……..fghi……………..6………efgh……….…True
7……..defg…………….7………fghi………..…False
………………………......8………cdef……….…False
|
|
|
|
|
|
Rank: Super forum user
|
Chris
You have mail
Badger
|
|
|
|
|
|
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.