|
VLOOKUP or Array or neither
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 |
VLOOKUP or Array or neither
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 |
VLOOKUP or Array or neither
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 |
VLOOKUP or Array or neither
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 |
VLOOKUP or Array or neither
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 |
| All times are GMT +1. The time now is 06:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com