Here's one way...
Here's my sheet "Old" with columns A to C in rows 1 to 18 containing my data:
First Name
| Surname | Corporation
|
Susan | Smith | ACME Widgets |
Bert | Jones | Altwood Bits |
Alf | Carruthers | Bridges Stuff |
Sarah | Pollard | Blacks Things |
Zoe | Green | Carter PLC |
Vanessa | Attwood | Dry & Co. |
Hannah | Ross | Easy Partners |
John | Connelly | Green & Son |
Lily | Smithers | Hope & Sky Inc. |
Keith | Johnson | India Trading |
Bob | Green | Carter PLC |
Stella | Mitchell | Krill Corp. |
John | Tripp | Lily Interiors |
John | Jackson | Mint Trading |
Charlie | White | White Sky Inc. |
Nigel | Black | Whites |
Zoe | Zoomer | Zoe & Partners |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
On my "New" sheet I first want to figure out what the sequence (Seq.) should be alphabetically but also allowing for duplicate surnames. In E2 I enter
=COUNTIF(Old!$B$2:$B$18,"<"&Old!B2)+COUNTIF(Old!B$2:B2,Old!B2)
...and copy it down as many rows as I have data.
Now I can just INDEX into the columns I want to retrieve using the ROW()-1 (so I'm starting the formula in row 2 but want to retrieve the 1st entry) by matching the sequence number:
=INDEX(Old!$B$2:$B$18,MATCH(ROW()-1,$E$2:$E$18,0)) gets me the Surname
=INDEX(Old!$A$2:$A$18,MATCH(ROW()-1,$E$2:$E$18,0)) gets me the First Name and
=INDEX(Old!$C$2:$C$18,MATCH(ROW()-1,$E$2:$E$18,0)) gets me the Corporation
Surname
| First Name | Corporation | | Seq.
|
Attwood | Vanessa | Dry & Co. | | 13 |
Black | Nigel | Whites | | 9 |
Carruthers | Alf | Bridges Stuff | | 3 |
Connelly | John | Green & Son | | 11 |
Green | Zoe | Carter PLC | | 5 |
Green | Bob | Carter PLC | | 1 |
Jackson | John | Mint Trading | | 12 |
Johnson | Keith | India Trading | | 4 |
Jones | Bert | Altwood Bits | | 14 |
Mitchell | Stella | Krill Corp. | | 8 |
Pollard | Sarah | Blacks Things | | 6 |
Ross | Hannah | Easy Partners | | 10 |
Smith | Susan | ACME Widgets | | 15 |
Smithers | Lily | Hope & Sky Inc. | | 7 |
Tripp | John | Lily Interiors | | 16 |
White | Charlie | White Sky Inc. | | 2 |
Zoomer | Zoe | Zoe & Partners | | 17 |
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>