Hello,
Ive got two columns with staff ID numbers which Id like to firstly match
and then sort so that identical ID numbers are on the same row. For example,
unsorted the columns look like this:
A B
3345 3876
3654 3980
2872 3545
3980 2872

Id like to have the ID numbers matched and sorted by row like this:
A B
3345 3345
3654
2872 2872
3980 3980
3876

Column A has about 3000 entries and Column B has about 400 entries, so that
not all ID numbers in Column B appear in Column A. Columns D, E, F etc have
other information such as the persons name, telephone number, email address
etc. How do I do this? I am a very basic Excel user so Im not very familiar
with formulas etc. I'm using Excel 2007.

Thanks in advance,
Eilean

Try this play ..

Assume data as posted in cols A and B, from row2 down
(you had a typo for 3545 in col B, it should read as 3345, I believe)

First, insert 2 new cols C & D

Place in C2: =IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),A2,""))
Copy C2 down to the last row of data in col A
This returns the desired results
(Col A found in col B)

Place in D2: =IF(B2="","",IF(ISNUMBER(MATCH(B2,A:A,0)),"",B2))
Copy D2 down to the last row of data in col B
This should return acceptable "converse" results
(Col B NOT found in col A)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"Eilean" wrote:
Ive got two columns with staff ID numbers which Id like to firstly match
and then sort so that identical ID numbers are on the same row. For example,
unsorted the columns look like this:
A B
3345 3876
3654 3980
2872 3545
3980 2872

Id like to have the ID numbers matched and sorted by row like this:
A B
3345 3345
3654
2872 2872
3980 3980
3876

Column A has about 3000 entries and Column B has about 400 entries, so that
not all ID numbers in Column B appear in Column A. Columns D, E, F etc have
other information such as the persons name, telephone number, email address
etc. How do I do this? I am a very basic Excel user so Im not very familiar
with formulas etc. I'm using Excel 2007

"Max" wrote:

Try this play ..

Assume data as posted in cols A and B, from row2 down
(you had a typo for 3545 in col B, it should read as 3345, I believe)

First, insert 2 new cols C & D

Place in C2: =IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),A2,""))
Copy C2 down to the last row of data in col A
This returns the desired results
(Col A found in col B)

Place in D2: =IF(B2="","",IF(ISNUMBER(MATCH(B2,A:A,0)),"",B2))
Copy D2 down to the last row of data in col B
This should return acceptable "converse" results
(Col B NOT found in col A)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"Eilean" wrote:
Ive got two columns with staff ID numbers which Id like to firstly match
and then sort so that identical ID numbers are on the same row. For example,
unsorted the columns look like this:
A B
3345 3876
3654 3980
2872 3545
3980 2872

Id like to have the ID numbers matched and sorted by row like this:
A B
3345 3345
3654
2872 2872
3980 3980
3876

Column A has about 3000 entries and Column B has about 400 entries, so that
not all ID numbers in Column B appear in Column A. Columns D, E, F etc have
other information such as the persons name, telephone number, email address
etc. How do I do this? I am a very basic Excel user so Im not very familiar
with formulas etc. I'm using Excel 2007


Thanks Max, for the matching solution (and for noting the typo). I can now
see the numbers that are in both column A and B (and those that are not in
Column A). Now, is there a way of sorting the 2 columns so that the matching
numbers appear on the same row? For example, while I can sort both columns
from smallest to largest, can I somehow sort both columns so that the
matching numbers across both columns appear on the same row? Something like
"if number in column A is identical to number in Column B then sort them in
the same row". I'm probably missing some fundamental point here... Actually I
could merge the two columns and then sort the numbers, and the matching
numbers would be under each other, but I was hoping that would be my last
resort. Any other ideas?
Thanks in advance,
Eilean

Welcome Eilean
.. a way of sorting the 2 columns so that
the matching numbers appear on the same row?
Do believe that's exactly the effect that's achieved.
Pl mark the earlier response. Click the YES button there.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"Eilean" wrote:
Thanks Max, for the matching solution (and for noting the typo). I can now
see the numbers that are in both column A and B (and those that are not in
Column A). Now, is there a way of sorting the 2 columns so that the matching
numbers appear on the same row? For example, while I can sort both columns
from smallest to largest, can I somehow sort both columns so that the
matching numbers across both columns appear on the same row? Something like
"if number in column A is identical to number in Column B then sort them in
the same row". I'm probably missing some fundamental point here... Actually I
could merge the two columns and then sort the numbers, and the matching
numbers would be under each other, but I was hoping that would be my last
resort. Any other ideas?
Thanks in advance,
Eilean