Need ideas for formula to display groups of data in certain format

[Dman333]

Hello All:

I'm looking for a formula that can go through a list of Items and Categories and then create a more readable output without having to list each item. Below I have Items and a category / bucket. I'd like to be able to have a formula that would show the results in the Desired Summary / Item #'s column like below. So instead of a list showing every number, it can display 1-3, 7, 17-20. There are a bunch of different formulas that do parts of this, I just can't figure out how to put them together so when you change an Item from Bucket A to Bucket B the Item #'s Summary will update.

Any help or ideas is greatly appreciated and thanks for the time.

Unique Identifier	Category		Desired Summary
Item #	Bucket		Summary	Item #'s
1	A		Bucket A	1-3, 7, 17-20
2	A		Bucket B	4-6, 8, 13
3	A		Bucket C	9-11
4	B		Bucket D	12, 14-16
5	B
6	B
7	A
8	B
9	C
10	C
11	C
12	D
13	B
14	D
15	D
16	D
17	A
18	A
19	A
20	A

 

[Dman333]

[TR]
[TD]=IF(D2="","",SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(B$2:B$21=D2,IF(B$1:B$20<>D2,A$2:A$21,IF(B$3:B$22<>D2,A$2:A$21," ")),",")))," ","-"),","," "))," ",", "))[/TD]
[/TR]

Peter_SSs:
You have the winning solution. This works exactly the way I wanted. I'm going to take some time dissect this one as I'm getting a little lost in exactly how you are using spaces and commas to get the final format.

Thanks for your time.
Cheers

 

[Peter_SSs]

You're welcome.

To help with understanding the space/comma issues I suggest selecting say cell E2 in my sample and use the 'Evaluate Formula' tool (on the Formulas ribbon tab and keep clicking 'Evaluate' until the Concat has been completed. You would see this

TRIM is then applied to remove any multiple spaces and then the remaining spaces are replaced (using SUBSTITUTE) with "-" which gives the number range parts like this

Then all the commas are replaced with spaces, TRIMed again and this time the remaining individual spaces replaced with ", " to give the final result.

 

[Dman333]

You're welcome.

To help with understanding the space/comma issues I suggest selecting say cell E2 in my sample and use the 'Evaluate Formula' tool (on the Formulas ribbon tab and keep clicking 'Evaluate' until the Concat has been completed. You would see this

View attachment 31600

TRIM is then applied to remove any multiple spaces and then the remaining spaces are replaced (using SUBSTITUTE) with "-" which gives the number range parts like this

View attachment 31601

Then all the commas are replaced with spaces, TRIMed again and this time the remaining individual spaces replaced with ", " to give the final result.

View attachment 31602

Thanks again for all your help with this. I've been using it and ran into a snag that I fear eliminates this option. As it turns out, there are occasions where the item # has to go into a different column now. If you delete any item in the list you can see what I mean. The items are always numbers. Can you think of any other way to get the same result with this new issue?
Thanks in advance for all your efforts.
Cheers

 

[Peter_SSs]

Could you give us some sample data and expected results that demonstrates the new issue?