Finding differences in 2 tables where Id and start date matches but location is different.

starheartbeam

New Member
Joined
Aug 8, 2018
Messages
18
Id
location
start
%
Id
location
start
%
1234
US
Jan 1
100
1234
US
Jan 1
100
1234
US
Feb 1
100
1234
CA
Feb 1
90
9876
CA
Jan 1
100
9876
US
Jan 1
100
9876
FR
Feb 1
100
9876
FR
Feb 1
100
7532
US
Jan 1
100
7532
FR
Jan 1
100

<tbody>
</tbody>

I have 2 tables each on different tabs. I am trying to write a macro that will lookat the Id and start of each tab and if those both match but the location doesnot match in the second tab to the first tab then it would copy that row and paste itonto a 3rd table. So in the exampleabove the 3rd tab would have.

Id location start %

1234 CA Feb 1 90
9876 US Jan 1 100
7532 FR Jan 1 100
 

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.
This can be done in Excel365 with one formula in a single cell.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Book1
ABCDEFGHI
1Idlocationstart%Idlocationstart%
21234US01-Jan1001234US01-Jan100
31234US01-Feb1001234CA01-Feb90
49876CA01-Jan1009876US01-Jan100
59876FR01-Feb1009876FR01-Feb100
67532US01-Jan1007532FR01-Jan100
7
8
9Idlocationstart%
101234CA01-Feb90
119876US01-Jan100
127532FR01-Jan100
Sheet4
Cell Formulas
RangeFormula
A10=FILTER(IF(A2:A6&C2:C6&B2:B6<>F2:F6&H2:H6&G2:G6,F2:I6),A2:A6&C2:C6&B2:B6<>F2:F6&H2:H6&G2:G6)
[/FONT]
 
Upvote 0
The line might not always been on the same row between the two different sheets. How would I search all of sheet one against sheet 2 for I'd and date that is the same but the location is not? I know how to create a key of id-date but I don't know how to look for key that matches but location that does not match.
 
Last edited:
Upvote 0
you can try PowerQuery

Idlocationstart%Idlocationstart%Idlocationstart%
1234​
US
Jan-01​
100​
1234​
US
Jan-01​
100​
1234​
CA
Feb-01​
90​
1234​
US
Feb-01​
100​
1234​
CA
Feb-01​
90​
9876​
US
Jan-01​
100​
9876​
CA
Jan-01​
100​
9876​
US
Jan-01​
100​
7532​
FR
Jan-01​
100​
9876​
FR
Feb-01​
100​
9876​
FR
Feb-01​
100​
7532​
US
Jan-01​
100​
7532​
FR
Jan-01​
100​

then format column start mmm-dd

Code:
[SIZE=1]let
    Source = Table.NestedJoin(Table14,{"Id", "location"},Table15,{"Id", "location"},"Table15",JoinKind.RightAnti),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Table15"}),
    #"Expanded Table15" = Table.ExpandTableColumn(#"Removed Other Columns", "Table15", {"Id", "location", "start", "%"}, {"Id", "location", "start", "%"})
in
    #"Expanded Table15"[/SIZE]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top