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

 

[Bassey]

Rick, Will try your macro tomorrow. It's bedtime in the Netherlands. Thank you very much for the lookupconcat! I used it three different ways in my sheet thanks!