asked on
Comparing listing in xls and access
Hello, with the help of people on the site, I created a word document that allows me to reconcil a listing of documents delivered to me using a reconcil file from my vendor and inventory document that is created from a script that I wrote.
With that being said the spreadsheet works fine to compare one cell from another but if I have files that are missing from the list I can't which files are missing. I've attached my spreadsheet showing what I am talking about.
Essentially anything with a False does not match while anything that is true does match. I sort A-Z but when I am missing files names it gives the impression that I am missing files that may not be missing
Overall I would see if I can do a compare to see if what is missing and what is not missing.
Also, I'm a MYSQL / oracle person so I was curious how would I move this xls into Access to do a query there as well
Final-Reconsoliation---Shipment-4---.xls
With that being said the spreadsheet works fine to compare one cell from another but if I have files that are missing from the list I can't which files are missing. I've attached my spreadsheet showing what I am talking about.
Essentially anything with a False does not match while anything that is true does match. I sort A-Z but when I am missing files names it gives the impression that I am missing files that may not be missing
Overall I would see if I can do a compare to see if what is missing and what is not missing.
Also, I'm a MYSQL / oracle person so I was curious how would I move this xls into Access to do a query there as well
Final-Reconsoliation---Shipment-4---.xls
Microsoft Excel
Last Comment
Ryan Chong
I use a similar formula to find if the values in Column B exists in Column A by using formula:
=IF(ISNA( MATCH("*\" & B2,A:A,0) ), FALSE, TRUE)
which it will return true if it's found and false if it's not found.
Final-Reconsoliation---Shipment-4_b.xls
=IF(ISNA( MATCH("*\" & B2,A:A,0) ), FALSE, TRUE)
which it will return true if it's found and false if it's not found.
Final-Reconsoliation---Shipment-4_b.xls
ASKER
Question,
The A:A,0 portion of the equation actually tells excel to compare B2 to all the values in column A correct?
The A:A,0 portion of the equation actually tells excel to compare B2 to all the values in column A correct?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
All file names in column A has a match in column B.
I have added this formula to column D
=MATCH(RIGHT(A1,LEN(A1)-37
The formula looks in column B to see if the file name in column A can be found in column B.
There is a number in all cells in column D which means that all files have a match.
The number shows the row where the match is.
For the first False in row 165, the file name is in row 172.
Final-Reconsoliation---Shipment-4---.xls