I am trying top place a sequential value to my booth numbers by name. I am having issues when there are: 2 instances of non sequential numbers "example: KS GOLF KARTS, INC" 144-155 [Desired Result: 144-146 & 153-155] When there is an Alpha character in the numeric values "example: GARAGE DOOR NARCISSIST" 0 [Desired Result: C01-C02] The array in the 3rd column is as follows: =IF(MIN(IF($B$2:$B$15=B2,$A$2:$A$15))=MAX(IF($B$2:$B$15=B2,$A$2:$A$15)),MIN(IF($B$2:$B$15=B2,$A$2:$A$15)),CONCATENATE(MIN(IF($B$2:$B$15=B2,$A$2:$A$15)),"-",MAX(IF($B$2:$B$15=B2,$A$2:$A$15)))) ty -J

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Retrieve sequential numbers with multiple instances and alpha/numeric characters.

Thread starter HockeyDiablo
Start date May 5, 2017
Tags

min if array min if; max if sequence

HockeyDiablo

Board Regular

May 5, 2017

I am trying top place a sequential value to my booth numbers by name. I am having issues when there are:

2 instances of non sequential numbers "example: KS GOLF KARTS, INC" 144-155 [Desired Result: 144-146 & 153-155]
When there is an Alpha character in the numeric values "example: GARAGE DOOR NARCISSIST" 0 [Desired Result: C01-C02]

The array in the 3rd column is as follows:

Code:

=IF(MIN(IF($B$2:$B$15=B2,$A$2:$A$15))=MAX(IF($B$2:$B$15=B2,$A$2:$A$15)),MIN(IF($B$2:$B$15=B2,$A$2:$A$15)),CONCATENATE(MIN(IF($B$2:$B$15=B2,$A$2:$A$15)),"-",MAX(IF($B$2:$B$15=B2,$A$2:$A$15))))

ty

-J

Last edited: May 5, 2017