Matching numbers

[Chi]

Hi,

I have two columns- A and B- store numbers. Is there a way I can match the
numbers so that the numbers on column A will be the same numbers on the
column B?

Ex: ColumnA ColumnB
100 105
106 103
105 106
103 100

I would like them look like this: Column A Column B
100 100
103 103
105 105
106 106

Thank you
Chi

 

[Luke M]

If it's the same list of numbers in each column (as in your example) you can
sort each columns seperately.

If that's not what you're looking for, you might want to take a look at the
MATCH or INDEX functions.

 

[Sean Timmons]

so, are you looking to sort them each individually?

Highlight column A by itself,

Select Continue with current selection...

Sort ascending.

Do the same for column b.

 

[Chi]

Hi Luke and Sean,

Sorry, It isn't the same list of numbers in each column. Moreover, some
numbers on column A and B are duplicates.

Would you please show me how to use MATCH or INDEX function?

 

[Sean Timmons]

OK, so wil lcolumn a have all numbers and colum B have a subset, or will each
have numbers the other doesn't?

to remove the duplicates, You can use Advanced filter:

Data > filter > Advanced Filter

Select Column A as your List Range, copy to another location, E1 with unique
records only.

Same for column B to the bottom of your new E list.

Then, do one more advanced filter to drop the numbers into C and D as unique.

 

[Chi]

Hi Sean,

Thank you very much for your patience!

I got the unique records on both column A and B, but each has numbers the
other doesn't.

Sorry again. Here is my real work with has column C.
A B C
36415 36415 $3.00
80048 78267 $11.48
80050 78268 $98.35
80051 80047 $12.36
80053 80048 $12.36
80055 80051 $10.24
80061 80053 $15.44

------------------------------
A B C
36415 36415 $3.00 is correct since it lines up the number 36415 in both
column and match with $3.00.

Is there a way I can bring up (line up) number 80048 (B5) and $12.36 (C5) to
B2 and C2 so that it can match with 80048 on A2 ?


And 78267 $11.48
78268 $98.35
80047 $12.36 will be at the end of the list since they don't have a maching
numbers on A column.

Since English is my second language, I have a hard time explaining the
problem. Please ask if you don't understant my question.

Thanks
Chi

 

[Sean Timmons]

No problem. I suppose my main question , then, is does column a need to be
included?

It seems you always match column B to column C where the values in A differ
from B.

If that's the case, you can have an extra column that shows

=if(ISNA(VLOOKUP(B2,A:A,1,0)),"",B2)

This will return the value in A if itmatches your B value or blank if column
A does not have a match with your B value. so, in the below example:

A B C D
36415 36415 $3.00 36415
80048 78267 $11.48
80050 78268 $98.35
80051 80047 $12.36
80053 80048 $12.36
80055 80051 $10.24 80051
80061 80053 $15.44 80053

would be the result.

At the end, you can merely copy column D, highlight Column A, Edit > Paste
special/Values.

Otherwise, I may be missing something?

 

[Chi]

Hi Sean,

Thank you very much again!

I added the formula in cell D2, but it returns blank

In cell D2= if(ISNA(VLOOKUP(B2,A:A,1,0)),"",B2)

Please help
Thanks

 

[Sean Timmons]

Not sure if you have spaces in there...

Highlight columns a and B, Edit>Replace enter a space in the find what box
and leave the replace with box blank. Relace all.

Does that resolve the issue?

 

[Chi]

Hi Sean,

It works!! Thank you very much!
Chi

Not sure if you have spaces in there...

Highlight columns a and B, Edit>Replace enter a space in the find what box
and leave the replace with box blank. Relace all.

Does that resolve the issue?

 

[Sean Timmons]

Not a problem! Happy we got there eventually!