Pulling specific info from one worksheet to another

[export9]

I have two worksheets.
One is old, one is new.
The new sheet needs the same info pulled in as the old sheet, but in a different order, by last name.

There are two columns of info associated with the last name, each will need pulled in separately. I have tried a couple different formula with no luck. Any ideas?

 

[Toadstool]

Here's one way...

Here's my sheet "Old" with columns A to C in rows 1 to 18 containing my data:

[TABLE="width: 259"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]First Name
[/TD]
[TD]Surname[/TD]
[TD]Corporation
[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]Smith[/TD]
[TD]ACME Widgets[/TD]
[/TR]
[TR]
[TD]Bert[/TD]
[TD]Jones[/TD]
[TD]Altwood Bits[/TD]
[/TR]
[TR]
[TD]Alf[/TD]
[TD]Carruthers[/TD]
[TD]Bridges Stuff[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Pollard[/TD]
[TD]Blacks Things[/TD]
[/TR]
[TR]
[TD]Zoe[/TD]
[TD]Green[/TD]
[TD]Carter PLC[/TD]
[/TR]
[TR]
[TD]Vanessa[/TD]
[TD]Attwood[/TD]
[TD]Dry & Co.[/TD]
[/TR]
[TR]
[TD]Hannah[/TD]
[TD]Ross[/TD]
[TD]Easy Partners[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Connelly[/TD]
[TD]Green & Son[/TD]
[/TR]
[TR]
[TD]Lily[/TD]
[TD]Smithers[/TD]
[TD]Hope & Sky Inc.[/TD]
[/TR]
[TR]
[TD]Keith[/TD]
[TD]Johnson[/TD]
[TD]India Trading[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Green[/TD]
[TD]Carter PLC[/TD]
[/TR]
[TR]
[TD]Stella[/TD]
[TD]Mitchell[/TD]
[TD]Krill Corp.[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Tripp[/TD]
[TD]Lily Interiors[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Jackson[/TD]
[TD]Mint Trading[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]White[/TD]
[TD]White Sky Inc.[/TD]
[/TR]
[TR]
[TD]Nigel[/TD]
[TD]Black[/TD]
[TD]Whites[/TD]
[/TR]
[TR]
[TD]Zoe[/TD]
[TD]Zoomer[/TD]
[TD]Zoe & Partners[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 371"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Surname
[/TD]
[TD]First Name[/TD]
[TD]Corporation[/TD]
[TD][/TD]
[TD]Seq.
[/TD]
[/TR]
[TR]
[TD]Attwood[/TD]
[TD]Vanessa[/TD]
[TD]Dry & Co.[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD]Nigel[/TD]
[TD]Whites[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Carruthers[/TD]
[TD]Alf[/TD]
[TD]Bridges Stuff[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Connelly[/TD]
[TD]John[/TD]
[TD]Green & Son[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Zoe[/TD]
[TD]Carter PLC[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]Bob[/TD]
[TD]Carter PLC[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Jackson[/TD]
[TD]John[/TD]
[TD]Mint Trading[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Johnson[/TD]
[TD]Keith[/TD]
[TD]India Trading[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]Bert[/TD]
[TD]Altwood Bits[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Mitchell[/TD]
[TD]Stella[/TD]
[TD]Krill Corp.[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Pollard[/TD]
[TD]Sarah[/TD]
[TD]Blacks Things[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Ross[/TD]
[TD]Hannah[/TD]
[TD]Easy Partners[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Susan[/TD]
[TD]ACME Widgets[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Smithers[/TD]
[TD]Lily[/TD]
[TD]Hope & Sky Inc.[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Tripp[/TD]
[TD]John[/TD]
[TD]Lily Interiors[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]Charlie[/TD]
[TD]White Sky Inc.[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Zoomer[/TD]
[TD]Zoe[/TD]
[TD]Zoe & Partners[/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[/TR]
</tbody>[/TABLE]