Dec 09 2021 04:49 AM
I have two worksheets in a workbook, 'Master' and 'TB43-180'. I need something that will check each line in the Master against each line in TB43-180, such that if the model, nomenclature, and Syscode match but the INTVL doesn't it will say FALSE. In other words, if all four match it is TRUE but if the INTVL does not it is FALSE. It is guaranteed that model, nomenclature, and Syscode will match because the Master spreadsheet is, in essence, derived from TB43-180. What I am looking for is where the INTVL doesn't match when compared to TB43-180 because those are the records I will need to change. I envision a formula or whatever that I can put into a column in the Master and just drag it down to populate. Pretty sure this will involve INDEX, IF, and ANDs but I just don't have a grasp on it. Thank you all!
Dec 09 2021 05:20 AM - edited Dec 09 2021 05:22 AM
Hi.
Given both sheets have identical structure, field and cell data and formatting, and line ordering, I'd try the 1st of the following options, but the 2nd may be just useful as well in case the differences between both spreadsheets is expected to be minimal.
1. Create a new sheet in your file or a new book in case you don't want to touch your source data file, and put in A1 a comparison between each A1 cell from both Master and TB43-180.
eg.: Cell A1: ='[YourFile.xlsx]Master'!$A$1='[YourFile.xlsx]TB43-180'!$A$1
And copy it to the right and bottom to the extent of both tables.
You will get a TRUE/FALSE as result for each corresponding cell. For easier spotting of FALSES you can apply conditional formatting to the entire range with 'red' for FALSES or the like.
2. Another possibility is to export both sheets as csv files and just compare them with a text comparison program (WinMerge is a nice and free opensource alternative). It will highlight every line and differing data between both csv files.
Hope this helps. Please note both solutions are intended to be simple. It can be done with more elaborate programming or more complex formulas, but given the case, it may not be deemed necessary.
Dec 09 2021 05:25 AM
@surefirewizard Perhaps the easiest method to follow would be to add a column to each table where you concatenate the four relevant columns. Then in the second table, add yet another column where you VLOOKUP (or XLOOKUP if your Excel version supports it) the concatenated values from Table2 in the added column in Table1. Anything other than #NA means that a matching value was found. #NA is thus equivalent to FALSE in your example.
But, if these are very large tables and you have to this on a regular basis, I would use Power Query.
Dec 09 2021 05:32 AM
Dec 09 2021 05:55 AM
Dec 09 2021 11:37 AM
It looks like you have structured tables in two sheets
You may add to Master another column (Test) with formula in it like
=IF( ISNA(
XMATCH( [@Model]&[@Nomenclature]&[@Syscode]&[@Intvl],
TB43_180[Model]&TB43_180[Nomenclature]&TB43_180[Syscode]&TB43_180[Intvl]) ),
"not match", "" )
and apply conditional formatting to highlight not matched records.
Similar could be done with ranges and MATCH().
Dec 09 2021 11:53 AM
@Sergei Baklan Thanks, figured it out on my own but your solution is much more elegant!
Dec 09 2021 11:56 AM
Dec 09 2021 12:16 PM
With MATCH() just add to it third parameter as zero:
=IF( ISNA(
MATCH( [@Model]&[@Nomenclature]&[@Syscode]&[@Intvl],
TB43_180[Model] &
TB43_180[Nomenclature] &
TB43_180[Syscode] &
TB43_180[Intvl],
0 ) ),
"not match", "" )
I'm almost sure you have structured tables. But if ranges it depends on how data is structured exactly, plus it'll be better to use dynamic range. In simple case with hardcoding ranges
=IF( ISNA(
MATCH( $C3 & $D3 & $E3 & $F3,
'TB43-180'!$B$5:$B$16 &
'TB43-180'!$C$5:$C$16 &
'TB43-180'!$D$5:$D$16 &
'TB43-180'!$E$5:$E$16,
0 ) ),
"not match", "" )
and drag or Ctrl+D such cell down.