Compare two files and highlight differences

rorozco

New Member
Joined
Apr 21, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

I need to compare two files (File A and FIle B) and highlight the differences (in File A).

The two Files may have different columns but I only want to compare two of the columns with the same name. See below for Column info:

Identification Number (ID)ModuleFunction BlockComponent(s)Failure Rate
BC01BPCBC ConnectorsCON1A
0.109617​
BC02BPCFC ConnectorsCON1B
0.109617​
BC03BPCBC to FC ConnectorsCON1C
0.109617​


I want to search Column A (Identification Number ID) each cell and Compare Column C (Function Block) row if anything has changed, if so then highlight it.

Typically we get a new set of data every few weeks and there are hundreds of rows. The Identification Number ID never changes but the content in the other columns might. How can I go by doing this easily and time efficient? Currently I try to compare row by row.

Thank you.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,761
Office Version
  1. 365
Platform
  1. Windows
I would join the two tables in Power Query with the unique identifier in Column A as the field to join on.

Once joined, in PQ add a column and compare Column C of each file to see if they match.

If you post some sample data for the second table, I will demonstrate the Mcode to make this happen.
 

rorozco

New Member
Joined
Apr 21, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I would join the two tables in Power Query with the unique identifier in Column A as the field to join on.

Once joined, in PQ add a column and compare Column C of each file to see if they match.

If you post some sample data for the second table, I will demonstrate the Mcode to make this happen.

Hello Alan, see below for example of second table. Can you please show me how to do this in Mcode? I would appreciate it.

Identification Number (ID)ModuleFunction BlockComponent(s)Failure Rate
BC01BPCBPC to FIC ConnectorsCON1A
0.109617​
BC02BPCDTD ConnectorCON1B
0.109617​
BC03BPCDTD Power EnableCON1C
0.109617​
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,761
Office Version
  1. 365
Platform
  1. Windows
Once you have brought each table into Power Query, then Merge as new. Here is the Mcode to produce the result below

Power Query:
let
    Source = Table.NestedJoin(Table1, {"Identification Number (ID)"}, Table2, {"Identification Number (ID)"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Function Block"}, {"Table2.Function Block"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Same/Different", each if[Function Block]=[Table2.Function Block] then "Same" else "Different")
in
    #"Added Custom"

Book10
ABCDEFG
1Identification Number (ID)ModuleFunction BlockComponent(s)Failure RateTable2.Function BlockSame/Different
2BC01BPCBC ConnectorsCON1A0.109617BPC to FIC ConnectorsDifferent
3BC02BPCFC ConnectorsCON1B0.109617DTD ConnectorDifferent
4BC03BPCBC to FC ConnectorsCON1C0.109617DTD Power EnableDifferent
Merge1
 

rorozco

New Member
Joined
Apr 21, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Once you have brought each table into Power Query, then Merge as new. Here is the Mcode to produce the result below

Power Query:
let
    Source = Table.NestedJoin(Table1, {"Identification Number (ID)"}, Table2, {"Identification Number (ID)"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Function Block"}, {"Table2.Function Block"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Same/Different", each if[Function Block]=[Table2.Function Block] then "Same" else "Different")
in
    #"Added Custom"

Book10
ABCDEFG
1Identification Number (ID)ModuleFunction BlockComponent(s)Failure RateTable2.Function BlockSame/Different
2BC01BPCBC ConnectorsCON1A0.109617BPC to FIC ConnectorsDifferent
3BC02BPCFC ConnectorsCON1B0.109617DTD ConnectorDifferent
4BC03BPCBC to FC ConnectorsCON1C0.109617DTD Power EnableDifferent
Merge1
Awesome thanks I will try this out right now, question what if I want to check for both columns "Function Block" and "component(s)?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,761
Office Version
  1. 365
Platform
  1. Windows
You can change the Add Custom Column to compare both fields. Delete any fields you do not require

Power Query:
let
    Source = Table.NestedJoin(Table1, {"Identification Number (ID)"}, Table2, {"Identification Number (ID)"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Function Block", "Component(s)"}, {"Table2.Function Block", "Table2.Component(s)"}),
    SameDiffFunction = Table.AddColumn(#"Expanded Table2", "Function", each if [Function Block] = [Table2.Function Block] then "Same" else "Different"),
    SameDiffComp = Table.AddColumn(SameDiffFunction, "Component", each if [#"Component(s)"] = [#"Table2.Component(s)"] then "Same" else "Different")
in
    SameDiffComp

Book10
ABCDEFGHI
1Identification Number (ID)ModuleFunction BlockComponent(s)Failure RateTable2.Function BlockTable2.Component(s)FunctionComponent
2BC01BPCBC ConnectorsCON1A0.109617BPC to FIC ConnectorsCON1ADifferentSame
3BC02BPCFC ConnectorsCON1B0.109617DTD ConnectorCON1BDifferentSame
4BC03BPCBC to FC ConnectorsCON1C0.109617DTD Power EnableCON1CDifferentSame
Merge1
 
Solution

rorozco

New Member
Joined
Apr 21, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I would join the two tables in Power Query with the unique identifier in Column A as the field to join on.

Once joined, in PQ add a column and compare Column C of each file to see if they match.

If you post some sample data for the second table, I will demonstrate the Mcode to make this happen.
Hello,

I am having problems brining both tables into Power Query (sorry I am new to PQ). I have file 1 called Export, file 2 called Export 2. Can you tell me exactly how I can bring both tables together?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,761
Office Version
  1. 365
Platform
  1. Windows
Highlight the table you wish to bring into PQ. Click on Data-->Get and Transform-->from table/range.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,002
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top