Summarize range of comma delimited values

[Bassey]

Hi,

I have a sheet in Excel 2011 for mac that thankfully uses the UDF in this link.

LookUp Value and Concatenate All Found Results

It gave me ranges of up to 20 comma delimited numbers in one cel. Which is hard to read sometimes. So my wish was to summarize this in a way.

The data would first look like:
487, 488, 489, 490, 564, 565, 566, 567, 568, 569, 570

And the result should be:
487- 490, 564- 570

I made a formula that does this by using text to column and then a whole lot of IF's and AND's. Where the data will be in cel c4 trhough w4 By expanding the third line i was able to summarize up to 20 positions.

=IF(D4="";"";D4)
&IF(E4="";"";(IF(AND(E4-D4=1;F4-E4=1);"";IF(E4-D4>=1;", "&E4;""))))
&IF(F4="";"";(IF(AND(F4-E4=1;G4-F4=1);"";IF(AND((F4-E4)>1;((F4-E4=1)));", "&F4;IF(AND((E4-D4)>1;G4="");", "&F4;IF(AND(F4-E4=1;(E4-D4=1));"-"&F4;", "&F4))))))

I reduced the amount of columns to 3 by using the UDF in this link

http://www.mrexcel.com/forum/excel-...alues-single-cells-finding-values-within.html

Wich made the formula even more huge (multiply the third formula by 17 and that's the size . Also I had to split the end result in half with a formula for position one through 10 and one for position 11 to 20. The data is in c9 here

=IF(IFERROR(getitem(C9;0);"")="";"";getitem(C9;0))

&IF(IFERROR(getitem(C9;1);"")="";"";IF(AND((IFERRO R(getitem(C9;1);0))-(getitem(C9;0))=1;(IFERROR(getitem(C9;2);0))-(IFERROR((getitem(C9;1));0))=1);"";IF((IFERROR(get item(C9;1);0))-(IFERROR(getitem(C9;0);0)>=1);", "&(IFERROR(getitem(C9;1);""));"")))

&IF(IFERROR(getitem(C9;2);"")="";"";IF(AND((IFERRO R(getitem(C9;2);0))-(IFERROR(getitem(C9;1);0))=1;(IFERROR(getitem(C9;3 );0))-(IFERROR((getitem(C9;2));0))=1);"";IF(AND(((IFERRO R(getitem(C9;2);0))-(IFERROR(getitem(C9;1);0)))>1;(((IFERROR(getitem(C 9;2);0))-IFERROR(getitem(C9;1);0))=1));", "&(getitem(C9;2));IF(AND((IFERROR(getitem(C9;2);0) )-(IFERROR(getitem(C9;1);0))=1;IFERROR(getitem(C9;1) ;0)-(IFERROR(getitem(C9;0);0))=1);"-"&(getitem(C9;2));", "&(IFERROR(getitem(C9;2);""))))))

So the big question is of course. Can this be made simpler? With a new UDF or by adapting the LookupConcat UDF? Did i make this too difficult?

Thanks and regards,

Sebastiaan

 

[mole999]

To start with I don't know an answer but I have questions

You say upto 20 CSV, and demonstrate 11 values

Underneath that you show 487-490, 564-570 (am I right in suggesting that you use the first number to group by, is that because its within hundreds, what would happen if the first number was 4 or 5 as in single digits

 

[Bassey]

To start with I don't know an answer but I have questions

You say upto 20 CSV, and demonstrate 11 values

Underneath that you show 487-490, 564-570 (am I right in suggesting that you use the first number to group by, is that because its within hundreds, what would happen if the first number was 4 or 5 as in single digits

Hi thanks,

The 11 values where an example that exist in my sheet. there could be up to 20 values, but there could also be just one or even none.

If there was a single digit like 4 previous to the 487 it would look like: 4, 487-490, 564-570

My formula does exactly what I need and want. I was just wondering if it could be made simpler by altering my UDF or with a new UDF.

 

[mikerickson]

You might like this UDF.
CondenseList("2, 3, 4, 10, 15, 16, 17, 20") returns the string "2 - 4, 10, 15 - 17, 20"

Function CondenseList(aString As String, Optional Delimiter As String = ",") As String
    Dim Elements As Variant
    Dim lastNum As Double, Suffix As String, curElement As String
    Dim i As Long
    Elements = Split(aString, Delimiter)
    For i = 0 To UBound(Elements)
        curElement = Elements(i)

        If IsNumeric(curElement) And (Val(curElement) = (lastNum + 1)) Then
            Suffix = " - " & curElement
        Else
            CondenseList = CondenseList & Suffix & Delimiter & curElement
            Suffix = vbNullString
        End If
        
        lastNum = Val(curElement)
    Next i
    CondenseList = Mid(CondenseList & Suffix, Len(Delimiter) + 1)
End Function

 

[Bassey]

You might like this UDF.
CondenseList("2, 3, 4, 10, 15, 16, 17, 20") returns the string "2 - 4, 10, 15 - 17, 20"

I most certainly do like this. I'm getting almost identical results as in my formula, except when the range starts with a 1.

1,2,3,4,5,6,7,8,9,10 returns: - 10

1,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40 returns: - 1,21 - 40

1,41,42,43,44,45,46,47,48,49,50,61,62,53,54,55,56,57,58,59,601 returns: - 1,41 - 50,61 - 62,53 - 59,601

1,101,102,103,105,106,107 returns: - 1,101 - 103,105 - 107

I'm not really good with the VBA but perhaps the code should start with the first digit if present and then start comparing after the second digit?

Thank you!

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

 

[mikerickson]

Adding one line will fix that.

lastNum = Val(Elements(0)) - 2
For i = 0 To UBound(Elements)

 

[Bassey]

You are making me very happy. May I have one more request?

If the data where just two consecutive numbers, or if somewhere in the the range there are just two consecutive numbers. I would prefer a comma in between the values instead of the "-". For instance: 153, 154 would remain 153, 154 instead of 153 - 154. And 1,2,3,5,6,8,9,10 Would result in 1 - 3, 5, 6, 8 - 10.

Not life threatening if it was not possible, but if it would take another single line?

Thank you very much. Maybe I should have asked this before I made my monster formula. Although I enjoyed solving the puzzle.

 

[mikerickson]

try this

Function CondenseList(aString As String, Optional Delimiter As String = ",") As String
    Dim Elements As Variant
    Dim lastNum As Double, Suffix As String, curElement As String
    Dim i As Long
    Dim continuationDelimiter As String
    
    Elements = Split(aString, Delimiter)
    lastNum = Val(Elements(0)) - 2
    continuationDelimiter = Delimiter
    For i = 0 To UBound(Elements)
        curElement = Elements(i)

        If IsNumeric(curElement) And (Val(curElement) = (lastNum + 1)) Then
            Suffix = continuationDelimiter & curElement
            continuationDelimiter = " - "
        Else
            CondenseList = CondenseList & Suffix & Delimiter & curElement
            Suffix = vbNullString
            continuationDelimiter = Delimiter
        End If
        
        lastNum = Val(curElement)
    Next i
    CondenseList = Mid(CondenseList & Suffix, Len(Delimiter) + 1)
End Function

 

[Rick Rothstein]

Here is another macro you can consider...

Function Spans(CommaDelimitedText As String, Optional Delimiter As String = ", ") As String
  Dim X As Long, Numbers() As String
  Numbers = Split(Application.Trim(Replace(CommaDelimitedText, ",", " ")) & " 999999999")
  Spans = Numbers(0)
  For X = 1 To UBound(Numbers)
    If Numbers(X) <> Numbers(X - 1) + 1 Then
      If Numbers(X - 1) <> Right(Spans, Len(Numbers(X - 1))) Then
        Spans = Spans & IIf(Mid(Spans, InStrRev(Spans, ",") + 1) + 1 = Numbers(X - 1), Delimiter, "-") & Numbers(X - 1)
      End If
      Spans = Spans & Delimiter & Numbers(X)
    End If
  Next
  Spans = Replace(Spans, Delimiter & "999999999", "")
End Function

 

[Bassey]

Yes!! Works like a charm. The last one gave me Value! errors on the empty cells but solved nicely with an IFERROR

Thank you very very much!