Matching items in 2 columns

  • Thread starter Thread starter RW
  • Start date Start date
R

RW

Hi all,

This is probably elementary for this group, but here's my question.

Suppose I have 2 columns of items, with unequal numbers of items; fewer in
the second colum; and the everything in the second column is in the first
column. Something like this:

A A
B C
C F
D H
E K
F L
G
H
I
J
K
L


Is there an easy way I can get identical items into the same ROWs, so that I
can match them? I can do it manually, of course, but I sometimes have
hundreds or even thousands of items. I know how to sort, but that doesn't
get them into matching rows. I don't know Excel formulas or advanced
things.

I do a lot of statistics (SPSS, etc), and use Excel mostly for data
management and display. My question is a common task for me, but is very
hard, given my limited Excel skills.

Any help very much appreciated!!

RW (sociology prof)
 
try this
Assumed your data starts from row 2.

in Cell C2 put this formula
=IF(ISERROR(INDEX($B$2:$B$6,MATCH(A2,$B$2:$B$6,0))),"",INDEX($B$2:$B
$6,MATCH(A2,$B$2:$B$6,0))) and drag it down
 
Source data assumed in cols A and B, from row1 down
It's also assumed, as you mentioned, that:
everything in the second column is in the first column
Click to expand...

Place in C1: =IF(COUNTIF(B:B,A1),A1,"")
Copy C1 down to the last row of data in col A to return the desired
alignment results. Freeze col C with an "in-place" copy n paste special as
values. Delete col B. You'll end up with the desired result below:

A A
B
C C
D
E
F F
G
H H
I
J
K K
L L

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
 
Back
Top