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
[TABLE="width: 334"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Electricity HH[/TD]
[TD]ABC 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Electricity HH[/TD]
[TD]ABC 2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Electricity NHH[/TD]
[TD]ABC 287[/TD]
[TD]287[/TD]
[/TR]
[TR]
[TD]Electricity NHH[/TD]
[TD]ABC 348[/TD]
[TD]348[/TD]
[/TR]
[TR]
[TD]Natural Gas[/TD]
[TD]ABC 526[/TD]
[TD]526[/TD]
[/TR]
[TR]
[TD]Natural Gas[/TD]
[TD]ABC 556[/TD]
[TD]556[/TD]
[/TR]
[TR]
[TD]Natural Gas[/TD]
[TD]ABC 537[/TD]
[TD]537[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer Name[/TD]
[TD]Next Gas Ref[/TD]
[TD]Next HH Ref[/TD]
[TD]Next NHH Ref[/TD]
[/TR]
[TR]
[TD]ABC Company Ltd[/TD]
[TD]ABC 557[/TD]
[TD]ABC 3[/TD]
[TD]ABC 349[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

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

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Customer Name
[/TD]
[TD]Next Gas Ref
[/TD]
[TD]Next HH Ref
[/TD]
[TD]Next NHH Ref
[/TD]
[/TR]
[TR]
[TD]ABC Company Ltd
[/TD]
[TD]ABC 557
[/TD]
[TD]ABC 3
[/TD]
[TD]ABC 349
[/TD]
[/TR]
</TBODY>[/TABLE]

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

[TABLE="width: 231"]
<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>[TR]
[TD="class: xl63, width: 140, bgcolor: transparent"]Product[/TD]
[TD="class: xl63, width: 104, bgcolor: transparent"]Ref[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Ext[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 140, bgcolor: white"]Electricity HH[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]ABC 1[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 140, bgcolor: white"]Electricity HH[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]ABC 2[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 140, bgcolor: white"]Electricity NHH[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]ABC 287[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]287[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 140, bgcolor: white"]Electricity NHH[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]ABC 348[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]348[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 140, bgcolor: white"]Natural Gas[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]ABC 526[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]526[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 140, bgcolor: white"]Natural Gas[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]ABC 556[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]556[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 140, bgcolor: white"]Natural Gas[/TD]
[TD="class: xl64, width: 104, bgcolor: white"]ABC 537[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]537[/TD]
[/TR]
</TBODY>[/TABLE]

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...

[TABLE="width: 546"]
<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>[TR]
[TD="class: xl66, width: 199, bgcolor: transparent"]Customer Name[/TD]
[TD="class: xl66, width: 175, bgcolor: transparent"]Natural Gas[/TD]
[TD="class: xl66, width: 170, bgcolor: transparent"]Electricity HH[/TD]
[TD="class: xl66, width: 185, bgcolor: transparent"]Electricity NHH[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 199, bgcolor: transparent"]ABC[/TD]
[TD="class: xl65, width: 175, bgcolor: transparent"]ABC 557[/TD]
[TD="class: xl65, width: 170, bgcolor: transparent"]ABC 3[/TD]
[TD="class: xl65, width: 185, bgcolor: transparent"]ABC 349[/TD]
[/TR]
</TBODY>[/TABLE]

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")