Matching numbers

  • Thread starter Thread starter Chi
  • Start date Start date
C

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
 
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.
 
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.
 
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?
 
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.
 
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
 
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?
 
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
 
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?
 
Hi Sean,

It works!! Thank you very much!
Chi

Sean Timmons said:
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?
Click to expand...
 
Back
Top