Taking Two columns and doing matching with Phone Numbers

[lizardbreath]

Hello Mr Excel Wizards,

I have been trying to figure this out and have been unable to do so on my end. I have two columns in a spreadsheet.

Column A - A unique Identifier for a record
Column B - The phone number for the record

What I am trying to do is sort the records based on the phone number, then the Id (Id from Greatest to Least). After doing the sort, Column B will have the phone numbers in a list with any duplicate numbers one on top of the other.

Here is where it gets tricky,

I want to take the Ids that are below the highest Id of the phone number set (There could be several records with the same phone #), and then match it against the Id that is the highest number of that phone number set.

Here is an example set of data that I can get from sorting the information on the spreadsheet.

[TABLE="width: 307"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Identifier[/TD]
[TD]Phone Number[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1111111111[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1111111111[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1111111111[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2222222222[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]2222222222[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4444444444[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4444444444
[/TD]
[/TR]
</tbody>[/TABLE]

Here is what I would want to be spit out from the example set of data. Basically the way our CRM is designed is that it can take records and merge them on the back-end of other records if they are duplicates. So I need to take the older records (The lower numbers) of the phone number set and match it against the higher Ids of that phone number set.

[TABLE="width: 307"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]DupRecordId[/TD]
[TD]ParentRecordId[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[AFPathfinder]

So, you want a list of all the duplicate phone numbers and the ID they are duplicated by? I would copy all data to the place you want to create that list and sort column A (ID) as Largest to Smallest. Then, input this formula into cell C3:

=IFERROR(INDEX($A$2:A2,MATCH(B3,$B$2:B2,0)),"")

Use the fill handle to drag the formula down to the bottom of the data (or just double click the fill handle). The formula will input the ID of the highest matching phone number. You can then copy/paste special: values, sort column C and delete out rows that contain a blank value in column C.

If this would be a repeatable process, I would have the macro do mostly those exact steps.