re-numbering a list of numbers based on another list of numbers to get a sequential order

[ryan.wherry]

I have a list of numbers in a column. They are stops on a truck route. in another column, i have a list of numbers. They are backhauls that are inserted into the route. I need a formula to re-number the stops based on where the backhauls in the route have been inserted. ex:
Stops: (re-numbered): Backhauls:
1 1 4
2 2 5
3 3 7
4 6
5 8
6 9

[mcmahobt]

The formatting of the example you provided (at least to me) does not provide much insight as to what you need done. Can you upload a sample workbook with a "Before" setup, as well as an "After" setup of how you wish your data to look?

[ryan.wherry]

Sample book.xlsxSorry. First time i've used a forum. I've attached a sample. hope this makes more sense.

[mcmahobt]

No worries! Welcome to the forum...quick question about your data; will it be dynamic? That is, will you only ever have 6 route stops as a maximum?

[ryan.wherry]

Yes. it will have to be dynamic. the # of stops will always be different. anywhere from 1 to 22 stops. where i get into trouble is when i put 2 backhauls back to back. for instance: adding a backhaul as stops 4 and 5, so every route stop after increases by 2.

[mcmahobt]

For your spreadsheet, are you allowed to have a helper column that you can hide? If so, I think I have a solution. See attached:

[ChemistB]

See attached.
In H1, I calculated the number of unique stops
=SUMPRODUCT(--(--(ISNUMBER(MATCH($A$3:$A$8,$C$3:$C$8,0)))=0))+COUNT($C$3:$C$8)

In G3 copied down, I entered this Arrayed Formula

=IF(ROWS($A$1:$A1)>$H$1,"",IFERROR(INDEX($A$3:$A$8, SMALL(IF(NOT(ISNUMBER(MATCH($A$3:$A$8,$C$3:$C$8,0))), $A$3:$A$8),ROWS($A$1:$A1))),G2+1))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.