Help with Generating Ranges from list of numeric/character mix

[Rob5555]

Hello,

I have a list of numbers and number/character mixes like the below:

1000

1001

1002

1004

1005

1006

1M27

1M28

1M29

1M31

1M32

1J78

1J79

1J80

I would like the output to look like the following:

Is there any way it is possible to create a flow to do this automatically?

I was able to create the flow I'll attach below to perform my ask for numeric values, but can't figure out how to get it to work on the number/character mix values like "1J78"

I need a solution that would be able to be used for more 4 character numeric/string mix values than just what is shown in the example above. I have close to 2,000 values comprised of 4 character numbers and numeric/string mixes that I would need this solution to work for.

I would really appreciate and input on this issue, thank you in advance!

Rob

[BS_THE_ANALYST]

@Rob5555 here's one way:

I'd love to explain how it works, but it's beer'o'clock. Have a nice weekend! 😁.

On a side note, make sure the data is sorted, either at source, or drag a sort tool onto the start of the workflow, and sort by the field.

[BS_THE_ANALYST]

@Rob5555 add the sort tool at the start, on the off chance something is in the wrong place on the source data:

[Rob5555]

Thank you for the response! This is working with the sample I provided but not with my larger amount of data.

For example, I'm trying to have the above data output as:

Min         Max       Count

1P08       1P08        1

1P53        1P53        1

1P78        1P79        2

Instead of how it is being output below:

I'd really appreciate any thoughts on why this is occurring with a larger data set. Thank you in advance!

[BS_THE_ANALYST]

Hi! The issue here arises from the fact that you haven't determined what the logic is to create the min and max groupings. For example, you've got 4 values with the prefix 1P above. Do values only fall into the same range if they have the same prefix AND the numbers after are within the same tens range? I.e 0-9, 10-19 etc 

If you make that part clear, there's certainly a logic to create the grouping 🙂

[Pang_Hee_Choy · Accepted solution]

easy but hard plan, hard code very thing in excel file for each group and use that in alteryx.

OR

for my understanding, it seems like split the group when the sequence is break. 

so,

sort first, thanks @BS_THE_ANALYST for mention that, I forget that as well.

split to 2 group, letter and sequence. 

use multi-row to split group if series break.

group and use first and last for min and max (as text is involved and sort nicely)

you can use select to rename and remove the extra column afterward.

[BS_THE_ANALYST]

@Pang_Hee_Choy Nice! 

[Rob5555]

This is working perfectly, thank you so much!