Comparing values in xls file

DancingFighterG
DancingFighterG used Ask the Experts™
on
Hello, I'm want to compare two column contents between each other and to make sure they match up. How would I do this with the following document attached
TS-Reconcil-Final.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
If you cut'n'paste column [A] of the [Compare] worksheet to another worksheet, sort that data into alphabetical (ascending A-Z order), & return it back into column [A] of the [Compare] worksheet, then repeat the process for column [ B ], you can then add the following formula to cell [C1] of the [Compare] worksheet:

=MID(A1,LEN("\\govnas03\TS_Alfresco_TABS\Shipment\")+1,255)=B1

Copy that down from [C1] to [C716], & you will see all 716 rows say TRUE.

That is, all the data from column [A] matches the corresponding data in column [ B ] (after the path/folder has been removed from the data in column [A]).

Please see the attached workbook for the end result.

I have also added some in-cell formulae in columns [F]/[G]:

Count of rows in column [A] (non-blank cells): =COUNTA(A:A)
Count of rows in column [ B ] (non-blank cells): =COUNTA(B:B)
Count of rows that match (TRUE) in column [C]: =COUNTIF(C:C,TRUE)

All three show a total of 716 (as expected).
Q-28705663.xls
DancingFighterG

Author

Commented:
Great quick solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial