Compare two columns in Excel 1

[tikual]

Dear all,

I have a problem as follows:

Total four columns, I would like to compare the country code.

A B C D

England Eng United States US
United States USA England ED
Japan JAP Chile CL
Chile CLE Japan JP

How to change the above records to these? (Column A and C are not same sequence)

England Eng ED
United States USA US
Japan JAP JP
Chile CLE CL

Thanks!!


tikual

 

[Loomah]

I bet I'm missing something here but...

Why not:
Insert a column between columns B & C then sort each list so that they are in the same order;

If your counties match (ie they are in the same row) then just delete the columns you don't need.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[tikual]

Loomah:

Column A and B are copied from one files.(About 40000 rows)
Column C and D are copied from another files. (About 500 rows)

So I am doing a mapping of these records. I want a help.

Thanks!

tikual

 

[Loomah]

Only a small detail, 39.5k rows difference in tables!!

I'd (again) suggest separating the two sets of data then using VLOOKUP to get matching data from your shorter table into your longer one.

Something like
=VLOOKUP(A1,D1:E500,2,FALSE)

Note that the second, shorter list willneed to be sorted in ascending order

Hope this is of more use!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[tikual]

Loomah,

Could you pls do a example on these four rows? I can't get the result after using your suggestion.

Thanks!

tikual

 

[bluedragon2]

For your example above, insert a row between Col C and D.

In the new blank Col C put =VLOOKUP(A1,$D$1:$E$4,2,FALSE)

in C1 and copy/paste it down to C4

What this should do for you:

In cell C1, it will look for England (A1) in the array D1:E4. If it finds it, it will take the value in the second column (D) and return its value (ED).

For C2, It will look for A2 (United States) in the same array and will return the value associated.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[tikual]

Thanks all,

One more question, how to compare these?

A A 0
B C 1
C C 0
D E 1

Just want to use a digit to show the different of column A and B.

Thank you very much!!

tikual

 

[bluedragon2]

=IF(A1=B1,1,0)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[cLFlaVA]

This should get the job done:

=CODE(LOWER(B1))-CODE(LOWER(A1))

It takes the numerical code associated with the lowercase value of the letter in each of the two columns, and then finds the difference.

Note: This will return a negative value if the letter in column B comes before the letter in column A. To avoid the negative, wrap the entire formula with the ABS() function.

HTH.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Loomah]

tikual
bluedragon has given as good (if not better) an explanation as I would have done!

Two small things though
1) In Blue's first line it should be "insert a column between Col C and D" not row. Minor detail!
2) You must sort your lookup table (the short set of data) in ascending order otherwise VLOOKUP will not work properly - if at all.

If it still won't work let me/us know.
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

Speedy connections!!

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[tikual]

Thanks all guys!!! All of you give me great solutions and Blue's one is most suitable for me.

tikual

 

[bluedragon2]

Thanks Loomah for that, I get typing and rows and columns all blend together

Actually, you do not have to sort the array in this case.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...