Alining data in 2 columns

  • Thread starter Thread starter Snipes
  • Start date Start date
S

Snipes

Hello folks,

Could someone please help with this simple in theory, but difficult to
implement problem I have with lining data up in 2 columns in excel.

I have the following 2 colums:

A B
ALC AAE
AMS AAL
BCN ABJ
SXF ABZ
BIO ACA
BHX ALC
BLQ ACE
BOS BCN
BRS ADD
BRU AMS
BUD BHX
ORD DUB
CPH AES
ORK AGA
DUB AGP
BEW
BEY
BFN
BFS
BGF
BGI
BGO
BGW
BHX
BHZ
BIA
BIK
BIO
BIQ
BJL
BJM
BJS
BKI
BKK



Column B continues for another 300 or so lines. Where as column A ends
there. As you can see, there are only some items in column A that
match column B. What I would like is this:

A B
AAE
AAL
ABJ
ABZ
ACA
ALC ALC
ACE
BCN BCN
ADD
AMS AMS
BHX BHX
DUB DUB
AES
AGA
AGP
BEW
BEY
BFN
BFS
BGF
BGI
BGO
BGW
BHZ
BIA
BIK
BIO
BIQ
BJL
BJM
BJS
BKI
BKK




So now I have a output that if the item in column A appears in column
B it is moved to the same row as it was in column B.

I think that makes sense.

Any suggestions please, this seems so easy, but I can't find a way to
do it!

Thank you very much!
 
select column B. Do insert => Column so B is now in C.

In the new B in B1 put in a formula

=if(iserror(match(A1,C:C,0),"",A1)

then drag fill it down the column.

Now select column B and do Edit=>Copy, and immediately Edit=>Paste Special
and select Values. This will replace the formulas with the values returned.
Now you can delete column C.
 
taych said:
Tom, seems like the formula should be :

=IF(ISERROR(MATCH(A1,C:C,0)),"",A1)

Taych
Click to expand...



I really appreciate you taking the time to reply, however, I did
follow Tom Ogilvy instructions, and there is a slight error in the
formula.

I also tried Taych's "fix" but I'm afraid it doesn't do what I was
looking for, when it's inserted, all it appears to do is copy into the
new B column, the same 3 letters as is in the A column.

If those letters do not exist in column C, there is a blank for that
row.

What I was hoping for was to "shunt" down, or re-align the letters in
column A so that they were on the same row, as they appeared in (the
original post above) column B.

Any tweaks to the above?

Thank you once again.
 
Snipes,

I think you did not follow Tom Ogilvy's instruction exactly. Pleas
try again as it works out fine for me.

Tayc
 
taych said:
Snipes,

I think you did not follow Tom Ogilvy's instruction exactly. Please
try again as it works out fine for me.

Taych
Click to expand...


OK found out the problem, the formatting of my initial post got
screwed up by posting to usenet. It was column B that was longer than
column A (not what appears above), so the formula was "the wrong way
round" if you know what I mean.

I swapped the 2 columns around and now it does work.

Thank you Tom and Taych. Final formula used (for clarity in case
someone else needs it):


=IF(ISERROR(MATCH(A1,C:C,0)),"",A1)
 
Back
Top