Inserting Part Blank Row when results of formula is false

[Oberon70]

Hi,

I am wanting to write a VBA code that inserts a blank row when the conditions of the below formula are false.

=AND($G2=$A2,$H2=$B2)

What I am wanting to do is compare (File Reference in Column A against File Ref in Column F) and (Cost in Column B against Cost in Column G). If the results is false then I would like to insert a partial blank Row only the area Column A and Be.

Example of data not compared.

File Ref	Cost			File Ref	Cost
BE4564	4.13​			NB9999	20​
BE4564	4.13​			NB9999	20​
AC1234	13.75​			NB9999	20​
EV3432	5.5​			NB9999	20​
EV3432	5.5​			NB9999	20​
HF2343	1.38​			BE4564	4.13​
HF2343	1.38​			BE4564	4.13​
AK2434	1.38​			BE4564	30​
AK2434	1.38​			BE4564	30​
AK2434	1.38​			AC1234	13.75​
AK2434	1.38​			AC1234	100​
UI9833	6.88​			EV3432	5.5​
OP3435	2.75​			EV3432	5.5​
OP3435	2.75​			EV3432	40​
				EV3432	40​
				HF2343	1.38​
				HF2343	1.38​
				HF2343	10​
				HF2343	10​
				AK2434	1.38​
				AK2434	1.38​
				AK2434	1.38​
				AK2434	1.38​
				AK2434	10​
				AK2434	10​
				AK2434	10​
				AK2434	10​
				UI9833	6.88​
				UI9833	50​
				OP3435	2.75​

Example of what I am looking for the results to be.

File Ref	Cost			File Ref	Cost
				NB9999	20​
				NB9999	20​
				NB9999	20​
				NB9999	20​
				NB9999	20​
BE4564	4.13​			BE4564	4.13​
BE4564	4.13​			BE4564	4.13​
AC1234	13.75​			BE4564	30​
				BE4564	30​
				AC1234	13.75​
				AC1234	100​
EV3432	5.5​			EV3432	5.5​
EV3432	5.5​			EV3432	5.5​
				EV3432	40​
				EV3432	40​
HF2343	1.38​			HF2343	1.38​
HF2343	1.38​			HF2343	1.38​
				HF2343	10​
				HF2343	10​
AK2434	1.38​			AK2434	1.38​
AK2434	1.38​			AK2434	1.38​
AK2434	1.38​			AK2434	1.38​
AK2434	1.38​			AK2434	1.38​
UI9833	6.88​			AK2434	10​
OP3435	2.75​			AK2434	10​
OP3435	2.75​			AK2434	10​
				AK2434	10​
UI9833	6.88​			UI9833	6.88​
OP3435	2.75​			UI9833	50​
OP3435	2.75​			OP3435	2.75​

Sorry, at work, so I had to pate the sheet in due to security.

 

[alansidman]

I have been able to do this power query, but because you have duplicate type entries on both sides, it matches each time so that there are multiple pairings. Is your sample realistic? Here is the mcode for joining the two tables.

let
    Source = Table.NestedJoin(Table2, {"File Ref", "Cost"}, Table1, {"File Ref", "Cost"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"File Ref", "Cost"}, {"File Ref.1", "Cost.1"})
in
    #"Expanded Table2"

 

[Oberon70]

Yes, my sample is realistic. The example used is based on a Data File I receive from an external company and at this stage a reference number does have multiple entries. In the future, early next year the transactions will be consolidated, so they will make my life simpler, but there are IT Teams with the external companies working on this.

 

[Oberon70]

What I am having to manually do at this stage is fine the transactions that did not auto receipt. The column on the right is the Data from a statement. The column on the left is the results of what has successful auto receipted. What I need is to find out what didn't auto receipt, so that I can manually receipt the cost.