Sorting and finding the last number in a range that includes text

[PE New User]

I am trying to find the last number in a range of cells that include both text and numbers - the range is not fixed and will always change.

I know that if I sort the range with the text included it will not truly sort the numbers, so realise that I need to separate the text from the numbers and have found a way to do this using =MID(J7,FIND(" ",J7)+1,10).

So, I now have the following three columns:

Product Ref MID

Electricity HH	ABC 1	1
Electricity HH	ABC 2	2
Electricity NHH	ABC 287	287
Electricity NHH	ABC 348	348
Natural Gas	ABC 526	526
Natural Gas	ABC 556	556
Natural Gas	ABC 537	537

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The data needs to be sorted firstly by Product and then by Ref, using the MID results.

Following the sort process, I then need to find the highest number that has a Product type of Electricity HH and the output required needs to be the next number.

I also need to do this for the Electricity NHH and Natural Gas, but the output will be in a separate cell for each Product.

I need assistance with trying to work out the best way to achieve this, possibly through use of a macro to insert a column in order to put the MID calculation in, then do the sort in order to find the next available number for each Product.

I hope this makes sense - have tried to put as much information as reading some threads I know that sometimes not enough information is provided on why something is trying to be achieved!

 

[Aladin Akyurek]

Could you post the expected results?

 

[PE New User]

Results would be seen on separate page, but would look something like this:

Customer Name	Next Gas Ref	Next HH Ref	Next NHH Ref
ABC Company Ltd	ABC 557	ABC 3	ABC 349

<tbody>
</tbody>

 

[Aladin Akyurek]

Results would be seen on separate page, but would look something like this:

Customer Name	Next Gas Ref	Next HH Ref	Next NHH Ref
ABC Company Ltd	ABC 557	ABC 3	ABC 349

<TBODY>
</TBODY>

Sheet1, A:C, the data plus the Ext range...

Product	Ref	Ext
Electricity HH	ABC 1	1
Electricity HH	ABC 2	2
Electricity NHH	ABC 287	287
Electricity NHH	ABC 348	348
Natural Gas	ABC 526	526
Natural Gas	ABC 556	556
Natural Gas	ABC 537	537

<COLGROUP><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 4977" width=140><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3697" width=104><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

C2, just enter and copy down:

=REPLACE($B2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$B2&"0123456789"))-1,"")+0

Sheet2, A:D, the final processing...

Customer Name	Natural Gas	Electricity HH	Electricity NHH
ABC	ABC 557	ABC 3	ABC 349

<COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7082" width=199><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6229" width=175><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6030" width=170><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6570" width=185><TBODY>
</TBODY>

B2, control+shift+enter, not just enter, and copy across:

=IFERROR($A2&" "&MAX(IF(ISNUMBER(SEARCH($A2,Sheet1!$B$2:$B$8)),
  IF(Sheet1!$A$2:$A$8=B$1,Sheet1!$C$2:$C$8)))+1,"Not Found")