bassey
06-21-2014, 07:57 PM
Hi,
I have a sheet that thankfully uses Rick's LookupConcat function.
http://www.excelfox.com/forum/f22/lookup-value-and-concatenate-all-found-results-345/
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
Multiple Values in single cells - finding values within (http://www.mrexcel.com/forum/excel-questions/708118-multiple-values-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.
=IF(IFERROR(getitem(C9;0);"")="";"";getitem(C9;0))
&IF(IFERROR(getitem(C9;1);"")="";"";IF(AND((IFERROR(getitem(C9;1);0))-(getitem(C9;0))=1;(IFERROR(getitem(C9;2);0))-(IFERROR((getitem(C9;1));0))=1);"";IF((IFERROR(getitem(C9;1);0))-(IFERROR(getitem(C9;0);0)>=1);", "&(IFERROR(getitem(C9;1);""));"")))
&IF(IFERROR(getitem(C9;2);"")="";"";IF(AND((IFERROR(getitem(C9;2);0))-(IFERROR(getitem(C9;1);0))=1;(IFERROR(getitem(C9;3 );0))-(IFERROR((getitem(C9;2));0))=1);"";IF(AND(((IFERROR(getitem(C9;2);0))-(IFERROR(getitem(C9;1);0)))>1;(((IFERROR(getitem(C9;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
I have a sheet that thankfully uses Rick's LookupConcat function.
http://www.excelfox.com/forum/f22/lookup-value-and-concatenate-all-found-results-345/
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
Multiple Values in single cells - finding values within (http://www.mrexcel.com/forum/excel-questions/708118-multiple-values-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.
=IF(IFERROR(getitem(C9;0);"")="";"";getitem(C9;0))
&IF(IFERROR(getitem(C9;1);"")="";"";IF(AND((IFERROR(getitem(C9;1);0))-(getitem(C9;0))=1;(IFERROR(getitem(C9;2);0))-(IFERROR((getitem(C9;1));0))=1);"";IF((IFERROR(getitem(C9;1);0))-(IFERROR(getitem(C9;0);0)>=1);", "&(IFERROR(getitem(C9;1);""));"")))
&IF(IFERROR(getitem(C9;2);"")="";"";IF(AND((IFERROR(getitem(C9;2);0))-(IFERROR(getitem(C9;1);0))=1;(IFERROR(getitem(C9;3 );0))-(IFERROR((getitem(C9;2));0))=1);"";IF(AND(((IFERROR(getitem(C9;2);0))-(IFERROR(getitem(C9;1);0)))>1;(((IFERROR(getitem(C9;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