Matching items in 2 columns

[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)

 

[muddan madhu]

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

 

[Max]

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

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
---