Ranking text with multiple periods

[Si_Slack]

Hi, I have a table with various references with multiple periods and i'm looking to RANK them from lowest to highest (1.2.3.2.1 first the 1.7.1.1.2 last). The table also contains blank cells which. Also, the references are being pulled from another table with a formula of =IF(LEFT(C5,1)="1",C5,"").

Example:

1.7.1.1.2
1.2.3.2.1
1.5.2.1.1
1.2.10.1.1

1.2.5.1.10

I've been trying to use =COUNTIF(P$5:P$40,"<="&P5) but the blanks and the double digit reference (1.2.10.1.1) are throwing things off.

Can anyone assist?

Thanks

[#2 · Accepted solution]

@Si_Slack hello. If you're up for a bit of formula fun, this can be done, although you'll need a few helper columns. I'm assuming you want to perform this for only column P. If you wanted to rank all WBS# together (e.g. from column C) it can be done, you'd just need the below formulas and point to that column. If you want it for each 'Table1', 'Table2', 'Table3', etc., you'll need each of the 7 calculated columns for each.

The below solution consists of 7 total columns: 5 columns to extract each quintet item, 1 column to assign a value, and 1 column to perform the rank. The formulas are based on your example looking at column P. They start on row 5.

To extract each quintet item, enter the formula in Z5:AD40:

=IF($P5="","",TRIM(MID(SUBSTITUTE($P5,".",REPT(" ",LEN($P5))),(COLUMN(A$1)-1)*LEN($P5)+1,LEN($P5))))

To assign a value to the extracted parts, enter the formula in AE5:AE40:

=IFERROR(SUMPRODUCT(Z5:AD5*(((COLUMN($A$1:$E$1)-6)*-1)^10)),"")

To assign a rank to the values, enter the formula in AF5:AF40:

=IFERROR(RANK(AE5,$AE$5:$AE$40,1),"")

This could get messy if you want to do them for each 'Table' item you have as opposed to ranking them all against each other, although I'm not sure the exact need or specifications if that is possible or not.

HTH

[#3]

Thanks for the help @Zack Barresse. This worked a treat!!