1

I need to compare a group of values in Table A to another group of values in Table B. If the column headers (the name of the columns in each table) are the same in both tables, then the respective values in the rows below will be compared. My question is 2 things, 1) how can I check that the column headers in one table match the column headers in the other, and 2) how can I select and compare values in the respective columns?

In the first example below, all 3 column headers do not match in both tables, although they have one matching column header, ID. What formula can I use to check a table that has several column headers and return a value of TRUE, if all column headers are the same in both tables?

ID  Name    Address           ID    Item    Quan
1   A       123A               1    U       22
2   B       234B               2    V       33
3   C       345C               3    W       44
4   D       456D               4    X       55
5   E       567E               5    Y       66
6   F       678F               6    Z       77
7   G       789G               7    T       88

In the second example below, the column headers match. However, although the values in both ID and Address columns match, the values in the Name address does not. What formula can I utilise that compares the values in all 3 columns, once it has become TRUE that all column headers match?

ID  Name    Address               ID    Name    Address
1   A       123                    1    D   123
2   B       234                    2    C   234
3   C       345                    3    E   345
4   D       456                    4    B   456
5   E       567                    5    A   567
6   F       678                    6    F   678

My last example is below, where although the names of the column headers match, the order of the columns is different. What formula could I utilise that compares the values in the ID, Name and Address columns regardless of their order?

ID  Name    Address               ID    Address Name
1   A       123                    1    123     A
2   B       234                    2    234     B
3   C       345                    3    345     C
4   D       456                    4    456     D
5   E       567                    5    567     E
6   F       678                    6    678     F

Any help is much appreciated.

0

Not sure what you are looking for by "compares the values", but for the purposes of this answer, I am using MATCH to return the row where a matching value is found.

I took the data you provided and arranged it, and the corresponding formula as follows ...

enter image description here

To determine if the headers match ...

  • Cell I1 contains the Control-Shift-Enter (CSE) formula {=IF(ISERROR(SUM(MATCH(E2:G2,A2:C2,0))),FALSE,TRUE)}.
  • Cell I11 contains the CSE formula {=IF(ISERROR(SUM(MATCH(E12:G12,A12:C12,0))),FALSE,TRUE)}.
  • Cell I20 contains the CSE formula {=IF(ISERROR(SUM(MATCH(E21:G21,A21:C21,0))),FALSE,TRUE)}

To put up headers for the comparison ...

  • Cell I2, filled over to K2, contains =IF($I$1,OFFSET($E$2,0,MATCH(A$2,$E$2:$G$2,0)-1),"")
  • Cell I12, filled over to K12, contains =IF($I$11,OFFSET($E$12,0,MATCH(A$12,$E$12:$G$12,0)-1),"")
  • Cell I21, filled over to K21, contains =IF($I$20,OFFSET($E$21,0,MATCH(A$21,$E$21:$G$21,0)-1),"")

To do the comparisons on values ...

  • Cell I3, filled down to I9, filled across to K9, contains =IF($I$1,MATCH(A3,OFFSET($E$3,0,MATCH(A$2,$E$2:$G$2,0)-1,COUNT($A$3:$A$9),1),0),"")
  • Cell I13, filled down to I18, filled across to K18, contains =IF($I$11,MATCH(A13,OFFSET($E$13,0,MATCH(A$12,$E$12:$G$12,0)-1,COUNT($A$13:$A$18),1),0),"")
  • Cell I22, filled down to I27, filled across to K27, contains =IF($I$20,MATCH(A22,OFFSET($E$22,0,MATCH(A$21,$E$21:$G$21,0)-1,COUNT($A$22:$A$27),1),0),"")
0

It depends whether you want to quickly do this on a not very regular basis, or if it's a frequently occurring job. If the former, my suggestion would be to do some manipulation on the worksheets and then use IF function formulae. If the latter, then some macro work (essentially to manipulate the worksheets, then perform the same calculations) will be your best answer. I'll answer using the former approach.

Ex1: I would copy row 1 of both sheets and Paste:Transform them to a new worksheet so they take up a single column each (eg Cols A and B). If the columns need to be in order to return TRUE, I would then add the following formula to C1 and copy it down to the last row of column heading info.

IF(A1=B1, TRUE, FALSE)

Then, in D1 insert the formula:

=COUNTIF(C:C, FALSE)

Any non-zero value in D1 means the columns do not match.

If the columns don't need to be in the same order to return TRUE, after the copy process, sort each column and then add the IF and COUNTIF formulae.

Ex2: After you've decided the columns do match (and they're in the same order), the way I would compare values is to insert a new column to the right of each of the worksheets that CONCATENATEs all the data in the original columns). Given your supplied Ex2 data, this formula will do that in cell D1 of each worksheet (copy down the formula to the last row):

=CONCATENATE(A1, "##", B1, "##", C1)

I add the hashes to keep the joined data discrete. Otherwise, if you have say, 1 in Col A and 10 in Col B of worksheet A and 11 in Col A and 0 in Col B of worksheet B then they will both concatenate into 110.

Next, you add a similar IF formula to one of the sheets:

=IF(sheet1!D1=sheet2!D1, TRUE, FALSE)

And again, add the COUNTIF formula to the next column to see if any values don't match.

=COUNTIF(D:D, FALSE)

Ex3 (where the order doesn't matter), I would sort the data so the columns are in alphabetical order and then perform the solution for Ex2. To sort the data correctly, select the information and in the 'Data'->'Sort & Filter' ribbon group click Sort. In the dialog that appears, click the Options.... button and select Sort left to right. When the column header in the fields area changes from 'Column' to 'Row', select Row 1 in the Sort by box. This will change the column order in both worksheets to (in your examples): Address, Id, Name. Then, as I said, follow the process for Ex2.

Now, if you want to perform these calculations on a frequent basis, you could code the above steps into an Excel macro. This may be the subject of another question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.