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

[starheartbeam]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Id
[/TD]
[TD]location
[/TD]
[TD]start
[/TD]
[TD]%
[/TD]
[TD][/TD]
[TD]Id
[/TD]
[TD]location
[/TD]
[TD]start
[/TD]
[TD]%
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]US
[/TD]
[TD]Jan 1
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]1234
[/TD]
[TD]US
[/TD]
[TD]Jan 1
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]US
[/TD]
[TD]Feb 1
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]1234
[/TD]
[TD]CA
[/TD]
[TD]Feb 1
[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]9876
[/TD]
[TD]CA
[/TD]
[TD]Jan 1
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]9876
[/TD]
[TD]US
[/TD]
[TD]Jan 1
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]9876
[/TD]
[TD]FR
[/TD]
[TD]Feb 1
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]9876
[/TD]
[TD]FR
[/TD]
[TD]Feb 1
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]7532
[/TD]
[TD]US
[/TD]
[TD]Jan 1
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]7532
[/TD]
[TD]FR
[/TD]
[TD]Jan 1
[/TD]
[TD]100
[/TD]
[/TR]
</tbody>[/TABLE]

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 3^rd table. So in the exampleabove the 3^rd tab would have.

Id location start %

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

 

[DRSteele]

This can be done in Excel365 with one formula in a single cell.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I
1	Id	location	start	%		Id	location	start	%
2	1234	US	01-Jan	100		1234	US	01-Jan	100
3	1234	US	01-Feb	100		1234	CA	01-Feb	90
4	9876	CA	01-Jan	100		9876	US	01-Jan	100
5	9876	FR	01-Feb	100		9876	FR	01-Feb	100
6	7532	US	01-Jan	100		7532	FR	01-Jan	100
7
8
9	Id	location	start	%
10	1234	CA	01-Feb	90
11	9876	US	01-Jan	100
12	7532	FR	01-Jan	100
Sheet4

Cell Formulas
Range	Formula
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]

 

[starheartbeam]

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.

 

[sandy666]

you can try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Id[/td][td=bgcolor:#5B9BD5]location[/td][td=bgcolor:#5B9BD5]start[/td][td=bgcolor:#5B9BD5]%[/td][td][/td][td=bgcolor:#5B9BD5]Id[/td][td=bgcolor:#5B9BD5]location[/td][td=bgcolor:#5B9BD5]start[/td][td=bgcolor:#5B9BD5]%[/td][td][/td][td=bgcolor:#70AD47]Id[/td][td=bgcolor:#70AD47]location[/td][td=bgcolor:#70AD47]start[/td][td=bgcolor:#70AD47]%[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1234​

[/td][td=bgcolor:#DDEBF7]US[/td][td=bgcolor:#DDEBF7]

Jan-01​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#DDEBF7]

1234​

[/td][td=bgcolor:#DDEBF7]US[/td][td=bgcolor:#DDEBF7]

Jan-01​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#E2EFDA]

1234​

[/td][td=bgcolor:#E2EFDA]CA[/td][td=bgcolor:#E2EFDA]

Feb-01​

[/td][td=bgcolor:#E2EFDA]

90​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1234​

[/td][td]US[/td][td]

Feb-01​

[/td][td]

100​

[/td][td][/td][td]

1234​

[/td][td]CA[/td][td]

Feb-01​

[/td][td]

90​

[/td][td][/td][td]

9876​

[/td][td]US[/td][td]

Jan-01​

[/td][td]

100​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

9876​

[/td][td=bgcolor:#DDEBF7]CA[/td][td=bgcolor:#DDEBF7]

Jan-01​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#DDEBF7]

9876​

[/td][td=bgcolor:#DDEBF7]US[/td][td=bgcolor:#DDEBF7]

Jan-01​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#E2EFDA]

7532​

[/td][td=bgcolor:#E2EFDA]FR[/td][td=bgcolor:#E2EFDA]

Jan-01​

[/td][td=bgcolor:#E2EFDA]

100​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

9876​

[/td][td]FR[/td][td]

Feb-01​

[/td][td]

100​

[/td][td][/td][td]

9876​

[/td][td]FR[/td][td]

Feb-01​

[/td][td]

100​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

7532​

[/td][td=bgcolor:#DDEBF7]US[/td][td=bgcolor:#DDEBF7]

Jan-01​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#DDEBF7]

7532​

[/td][td=bgcolor:#DDEBF7]FR[/td][td=bgcolor:#DDEBF7]

Jan-01​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

then format column start mmm-dd

[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]