0

I needed to take columns A, B, and C and compare D, E and F. Here's an example:

A                B                  C                  D              E               F  
Jump             Smith              5                  Jump           Smith           8  
Run              Naylor             2                  Swim           Fran            4  
Swim             Fran               7                  Jog            Dylan           1  
Jump             Fran               3                  Jog            Smith           4  

So I want to match column A and B with D and E but still have both number related C for 2011 and F for 2012.

Can anyone please help with that formula? My data is from A3-C4344 and D3 - D4470.

3
  • Question as written doesn't make much sense. You need to simplify the example. Dec 4, 2012 at 15:54
  • It would be clearer if you took a snapshot of your Excel screen, and uploaded it here.
    – wonea
    Dec 4, 2012 at 16:13
  • 1
    You want to compare / match the columns, then what? What is your end goal? You've left out some details needed for anyone to help you.
    – CharlieRB
    Dec 4, 2012 at 21:00

2 Answers 2

2
  1. Select the multi-columnar data. This technique works with two or more columns. It’s important to remember that the feature compares all selected columns to the first column in the selection (as anchored, which isn’t necessarily the left-most column).

  2. Press [F5] to launch the Go To dialog.

  3. Click the Special button at the bottom.

  4. In the resulting dialog, select Row Differences.

  5. Click OK and Excel highlights the values in the columns that don’t match their corresponding values in the other columns.

2

I can think of a multi-step way of doing this.

I. Combine A & B and D & E in new columns ...

   a) in I3 write `=A3 & " " & B3`
   b) in J3 write `2011`
   c) in K3 write `=C3`
   d) in L3 write `=D3 & " " & E3`
   e) in M3 write `2012`
   f) in N3 write `=F3`

Then copy these cells all the way down your spreadsheet. This should give you

Jump Smith  2011    5   Jump Smith  2012    8
Run Naylor  2011    2   Swim Fran   2012    4
Swim Fran   2011    7   Jog Dylan   2012    1
Jump Fran   2011    3   Jog Smith   2012    4

II. Copy each section's values into one long set of three columns ...

a) select I3 .. K4344, copy and paste-special just values to P:3
b) select L3 .. N4470, copy and paste-special just values to P:4345

III. Select P3 .. R8810 (or whatever the last row is) and Sort them.

This should give you ...

Jog Dylan   2012    1
Jog Smith   2012    4
Jump Fran   2011    3
Jump Smith  2011    5
Jump Smith  2012    8
Run Naylor  2011    2
Swim Fran   2011    7
Swim Fran   2012    4

IV. Create a reconfigured section ...

a) in U2 type `2011`
b) in V2 type `2012`
c) in T3 type `=P3`
d) in U3 type `=IF(Q3=2011,R3,"")`
e) in V3 type `=IF(Q3=2012,R3,"")`
f) select T3 .. V3, copy and paste all the way down

This should give you

           2011 2012
Jog Dylan       1
Jog Smith       4
Jump Fran   3   
Jump Smith  5   
Jump Smith      8
Run Naylor  2   
Swim Fran   7   
Swim Fran       4

V. Create a consolidated section ...

a) in Y2 type `2011`
b) in Z2 type `2012`
c) in X3 type `=IF(T3=T2,"",T3)`
d) in Y3 type `=IF(T3=T2,"",IF(T3=T4,U3&U4,U3))`
e) in Z3 type `=IF(T3=T2,"",IF(T3=T4,V3&V4,V3))`
f) select X3 .. Z3, copy and paste all the way down

This should give you ...

        2011    2012
Jog Dylan       1
Jog Smith       4
Jump Fran   3   
Jump Smith  5   8

Run Naylor  2   
Swim Fran   7   4

VI. Select this last section of 3 columns, copy and paste-special just values to a new section.

VII. Select and sort this new section

VIII. Delete the blank rows in this section

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .