I have two tables both on the same sheet, firstly I want to compare two
columns (A and J) and get any missing values (show the answers in Column M),
then I want to compare the "sum" of two columns (A&B) from one table with two
columns (J&K) in the second table to show the differences (show the answers
in Column N).

Column A Column B ------ Column J Column K ------ Column M Column N
712 AB 712 AB
714 713 NA
713 AB 713 NA
719 717 SA
714 AB 715 AB
715 AB 716 AB
716 AB 717 SA
717 AB 718 AB
718 AB 720 AB
719 SA 721 AB
720 AB 722 AB

If I understand you first question correctly, in M2, you would have

=if(OR(A2="",A2=J2,if(J2="",J2=A2))

If I understand you second question correctly,in N, you'd have:

=sumproduct(--(B2:B1000="AB"),A2:A1000)-sumproduct(--(K2:K1000="AB"),J2:J1000)

"Rob" wrote:

I have two tables both on the same sheet, firstly I want to compare two
columns (A and J) and get any missing values (show the answers in Column M),
then I want to compare the "sum" of two columns (A&B) from one table with two
columns (J&K) in the second table to show the differences (show the answers
in Column N).

Column A Column B ------ Column J Column K ------ Column M Column N
712 AB 712 AB
714 713 NA
713 AB 713 NA
719 717 SA
714 AB 715 AB
715 AB 716 AB
716 AB 717 SA
717 AB 718 AB
718 AB 720 AB
719 SA 721 AB
720 AB 722 AB

Unfortunately the table is not displayed correctly. the 714 and 713 NA on
line three sould be the answers in column M & N. The same with 719 and 717 SA
liune 5 these should be line 3 in columns M & N.
I want to look down the whole of column A and compare it with the whole of
column J and only display the differences in column M

"Sean Timmons" wrote:

If I understand you first question correctly, in M2, you would have

=if(OR(A2="",A2=J2,if(J2="",J2=A2))

If I understand you second question correctly,in N, you'd have:

=sumproduct(--(B2:B1000="AB"),A2:A1000)-sumproduct(--(K2:K1000="AB"),J2:J1000)

"Rob" wrote:

I have two tables both on the same sheet, firstly I want to compare two
columns (A and J) and get any missing values (show the answers in Column M),
then I want to compare the "sum" of two columns (A&B) from one table with two
columns (J&K) in the second table to show the differences (show the answers
in Column N).

Column A Column B ------ Column J Column K ------ Column M Column N
712 AB 712 AB
714 713 NA
713 AB 713 NA
719 717 SA
714 AB 715 AB
715 AB 716 AB
716 AB 717 SA
717 AB 718 AB
718 AB 720 AB
719 SA 721 AB
720 AB 722 AB

OK, so to further understand... are you looking for column M to have a value
in each row?

Please provide an example of what column M would look like in the below...

"Rob" wrote:

Unfortunately the table is not displayed correctly. the 714 and 713 NA on
line three sould be the answers in column M & N. The same with 719 and 717 SA
liune 5 these should be line 3 in columns M & N.
I want to look down the whole of column A and compare it with the whole of
column J and only display the differences in column M

"Sean Timmons" wrote:

If I understand you first question correctly, in M2, you would have

=if(OR(A2="",A2=J2,if(J2="",J2=A2))

If I understand you second question correctly,in N, you'd have:

=sumproduct(--(B2:B1000="AB"),A2:A1000)-sumproduct(--(K2:K1000="AB"),J2:J1000)

"Rob" wrote:

I have two tables both on the same sheet, firstly I want to compare two
columns (A and J) and get any missing values (show the answers in Column M),
then I want to compare the "sum" of two columns (A&B) from one table with two
columns (J&K) in the second table to show the differences (show the answers
in Column N).

Column A Column B ------ Column J Column K ------ Column M Column N
712 AB 712 AB
714 713 NA
713 AB 713 NA
719 717 SA
714 AB 715 AB
715 AB 716 AB
716 AB 717 SA
717 AB 718 AB
718 AB 720 AB
719 SA 721 AB
720 AB 722 AB

Hi Sean, All I want in column M a list of the ones that are missing when
doing a comparison between column A and column J

"Sean Timmons" wrote:

OK, so to further understand... are you looking for column M to have a value
in each row?

Please provide an example of what column M would look like in the below...

"Rob" wrote:

Unfortunately the table is not displayed correctly. the 714 and 713 NA on
line three sould be the answers in column M & N. The same with 719 and 717 SA
liune 5 these should be line 3 in columns M & N.
I want to look down the whole of column A and compare it with the whole of
column J and only display the differences in column M

"Sean Timmons" wrote:

If I understand you first question correctly, in M2, you would have

=if(OR(A2="",A2=J2,if(J2="",J2=A2))

If I understand you second question correctly,in N, you'd have:

=sumproduct(--(B2:B1000="AB"),A2:A1000)-sumproduct(--(K2:K1000="AB"),J2:J1000)

"Rob" wrote:

I have two tables both on the same sheet, firstly I want to compare two
columns (A and J) and get any missing values (show the answers in Column M),
then I want to compare the "sum" of two columns (A&B) from one table with two
columns (J&K) in the second table to show the differences (show the answers
in Column N).

Column A Column B ------ Column J Column K ------ Column M Column N
712 AB 712 AB
714 713 NA
713 AB 713 NA
719 717 SA
714 AB 715 AB
715 AB 716 AB
716 AB 717 SA
717 AB 718 AB
718 AB 720 AB
719 SA 721 AB
720 AB 722 AB