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

 

[alansidman]

The following presentation can be done with Power Pivot by creating a measure.

Book1
	D	E
1	Bucket	Items
2	A	1, 2, 3, 7, 17, 18, 19, 20
3	B	4, 5, 6, 8, 13
4	C	9, 10, 11
5	D	12, 14, 15, 16
Sheet1

Here is a link to a tutorial if this presentation is acceptable to you.

IMA Site Maintenance

 

[mamady]

Hi Dear,

I am not sure which MS Excel version you use, but in case you have recent versions, let me know if the below formula satisfy your requirement

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

Cell Formulas
Range	Formula
E3:E6	E3	=TEXTJOIN("-",TRUE,FILTER($A$3:$A$22,(RIGHT(D3)=$B$3:$B$22)*1))

Regards

 

[Dossfm0q]

Try This

Sub certain_format()

Dim Cll As Range, Rng As Range
Dim Nary As Variant
Dim Arr As Variant
Dim Arr2 As Variant
   Set Rng = Sheets("Sheet1").Range("A1").CurrentRegion
   ReDim Nary(1 To Rng.Rows.Count, 1 To 2) As Variant

   TXT = ""
   T = ";Bucklet "
   For Each Cll In Rng
    If Cll.Row >= 3 Then
        If Cll.Column = 2 Then
            If InStr(1, TXT, T & Cll.Value, vbTextCompare) = 0 Then
            TXT = TXT & IIf(TXT <> "", Chr(10), "") & T & Cll.Value
                If Cll.Value = Cll.Offset(-1, 0).Value And Cll.Value <> Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, Cll.Value, Cll.Offset(0, -1).Value & "," & Cll.Value)
                ElseIf T & Cll.Value <> T & Cll.Offset(-1, 0).Value And T & Cll.Value = T & Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "-" & T & Cll.Value)
                ElseIf T & Cll.Value <> T & Cll.Offset(-1, 0).Value And T & Cll.Value <> T & Cll.Offset(1, 0).Value Then
                     TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "," & T & Cll.Value)
                End If
            Else
                If T & Cll.Value = T & Cll.Offset(-1, 0).Value And T & Cll.Value <> T & Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "," & T & Cll.Value)
                ElseIf T & Cll.Value <> T & Cll.Offset(-1, 0).Value And T & Cll.Value = T & Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "-" & T & Cll.Value)
                ElseIf T & Cll.Value <> T & Cll.Offset(-1, 0).Value And T & Cll.Value <> T & Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "," & T & Cll.Value)
                End If
            
            End If
            p = Cll.Value
        End If
    End If
   Next
   Arr = Split(Replace(TXT, ",;", ";"), Chr(10))
   For x = LBound(Arr) To UBound(Arr)
    Arr2 = Split(Arr(x), ";")
        For y = UBound(Arr2) To LBound(Arr2) Step -1
        
            Nary(x + 1, (UBound(Arr2) + 1 - y)) = "'" & Arr2(y) ' add apostrophes to avoid change format such as date Format if need
        Next
   Next
 Sheets("Sheet1").Range("D3").Resize(UBound(Arr) + 1, 2) = Nary
End Sub

Need ideas for formula to display groups of data in certain format.xlsm
	A	B	C	D	E
1	unique Identifier	Category		Desired Summary
2	Item #	Bucket		Summary	Item #'s
3	1	A		Bucklet A	1-3,7,17-20
4	2	A		Bucklet B	4-6,8,13
5	3	A		Bucklet C	9-11
6	4	B		Bucklet D	12,14-16
7	5	B
8	6	B
9	7	A
10	8	B
11	9	C
12	10	C
13	11	C
14	12	D
15	13	B
16	14	D
17	15	D
18	16	D
19	17	A
20	18	A
21	19	A
22	20	A
Sheet1

 

[Dman333]

Hi Dear,

I am not sure which MS Excel version you use, but in case you have recent versions, let me know if the below formula satisfy your requirement

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

Cell Formulas
Range	Formula
E3:E6	E3	=TEXTJOIN("-",TRUE,FILTER($A$3:$A$22,(RIGHT(D3)=$B$3:$B$22)*1))

Regards

The following presentation can be done with Power Pivot by creating a measure.

Book1
	D	E
1	Bucket	Items
2	A	1, 2, 3, 7, 17, 18, 19, 20
3	B	4, 5, 6, 8, 13
4	C	9, 10, 11
5	D	12, 14, 15, 16
Sheet1

Here is a link to a tutorial if this presentation is acceptable to you.

IMA Site Maintenance

Thanks for your time, but a pivot won't work for the end user.
Cheers

 

[Dman333]

Hi Dear,

I am not sure which MS Excel version you use, but in case you have recent versions, let me know if the below formula satisfy your requirement

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

Cell Formulas
Range	Formula
E3:E6	E3	=TEXTJOIN("-",TRUE,FILTER($A$3:$A$22,(RIGHT(D3)=$B$3:$B$22)*1))

Regards

Thanks for this and your time. I like what you did here and this may end up being what I have to use. I was hoping to not have to list each number, but I'm not sure that can be done without VBA.
Cheers

 

[Dman333]

Try This

Sub certain_format()

Dim Cll As Range, Rng As Range
Dim Nary As Variant
Dim Arr As Variant
Dim Arr2 As Variant
   Set Rng = Sheets("Sheet1").Range("A1").CurrentRegion
   ReDim Nary(1 To Rng.Rows.Count, 1 To 2) As Variant

   TXT = ""
   T = ";Bucklet "
   For Each Cll In Rng
    If Cll.Row >= 3 Then
        If Cll.Column = 2 Then
            If InStr(1, TXT, T & Cll.Value, vbTextCompare) = 0 Then
            TXT = TXT & IIf(TXT <> "", Chr(10), "") & T & Cll.Value
                If Cll.Value = Cll.Offset(-1, 0).Value And Cll.Value <> Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, Cll.Value, Cll.Offset(0, -1).Value & "," & Cll.Value)
                ElseIf T & Cll.Value <> T & Cll.Offset(-1, 0).Value And T & Cll.Value = T & Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "-" & T & Cll.Value)
                ElseIf T & Cll.Value <> T & Cll.Offset(-1, 0).Value And T & Cll.Value <> T & Cll.Offset(1, 0).Value Then
                     TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "," & T & Cll.Value)
                End If
            Else
                If T & Cll.Value = T & Cll.Offset(-1, 0).Value And T & Cll.Value <> T & Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "," & T & Cll.Value)
                ElseIf T & Cll.Value <> T & Cll.Offset(-1, 0).Value And T & Cll.Value = T & Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "-" & T & Cll.Value)
                ElseIf T & Cll.Value <> T & Cll.Offset(-1, 0).Value And T & Cll.Value <> T & Cll.Offset(1, 0).Value Then
                    TXT = Replace(TXT, T & Cll.Value, Cll.Offset(0, -1).Value & "," & T & Cll.Value)
                End If
           
            End If
            p = Cll.Value
        End If
    End If
   Next
   Arr = Split(Replace(TXT, ",;", ";"), Chr(10))
   For x = LBound(Arr) To UBound(Arr)
    Arr2 = Split(Arr(x), ";")
        For y = UBound(Arr2) To LBound(Arr2) Step -1
       
            Nary(x + 1, (UBound(Arr2) + 1 - y)) = "'" & Arr2(y) ' add apostrophes to avoid change format such as date Format if need
        Next
   Next
Sheets("Sheet1").Range("D3").Resize(UBound(Arr) + 1, 2) = Nary
End Sub

Need ideas for formula to display groups of data in certain format.xlsm
	A	B	C	D	E
1	unique Identifier	Category		Desired Summary
2	Item #	Bucket		Summary	Item #'s
3	1	A		Bucklet A	1-3,7,17-20
4	2	A		Bucklet B	4-6,8,13
5	3	A		Bucklet C	9-11
6	4	B		Bucklet D	12,14-16
7	5	B
8	6	B
9	7	A
10	8	B
11	9	C
12	10	C
13	11	C
14	12	D
15	13	B
16	14	D
17	15	D
18	16	D
19	17	A
20	18	A
21	19	A
22	20	A
Sheet1

OK Dossfm0q - This is really slick. I'm not much of a coder, but I was kind of able to follow how you did this. Thanks a ton for taking the time on this - this does exactly what I wanted. I just need to check with the end-user and see if their ok having to execute the code every time they make a change. This was just an example. In practice, the workbook has about 30 sheets that would have to run the code individually or alter the code to go through the entire workbook, which I know isn't hard to do. I'm likely to end up using the textjoin method above and stay away from vba for this user.

Thanks for all your time and effort on this - truly appreciated.
Cheers.

 

[alansidman]

I'm guessing you didn't like the Power Pivot solution. No comments about it? Afraid to expand your horizons? Feedback to people who are trying to help is imperative in these forums. Good or Bad we want it.

 

[Peter_SSs]

I'm likely to end up using the textjoin method above and stay away from vba for this user

If you are wanting a formula solution and have TEXTJOIN, then you may also consider this which gives the format you asked for.
If you do not have the UNIQUE function then you can use the formulas in column G in column D instead.

BTW, please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using, and therefore what functions you have available. (Don’t forget to scroll down & ‘Save’)

21 02 07.xlsm
	A	B	C	D	E	F	G
1	Item #	Bucket		Summary	Item #'s		Item #'s
2	1	A		A	1-3, 7, 17-20		A
3	2	A		B	4-6, 8, 13		B
4	3	A		C	9-11		C
5	4	B		D	12, 14-16		D
6	5	B
7	6	B
8	7	A
9	8	B
10	9	C
11	10	C
12	11	C
13	12	D
14	13	B
15	14	D
16	15	D
17	16	D
18	17	A
19	18	A
20	19	A
21	20	A
22
Sheet2 (2)

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(B2:B21)
E2:E7	E2	=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," ")),",")))," ","-"),","," "))," ",", "))
G2:G7	G2	=IFERROR(INDEX($B$2:$B$21,MATCH(0,INDEX(COUNTIF($G$1:G1,$B$2:$B$21)+(B$2:B$21=""),0),0)),"")
Dynamic array formulas.

 

[Dman333]

I'm guessing you didn't like the Power Pivot solution. No comments about it? Afraid to expand your horizons? Feedback to people who are trying to help is imperative in these forums. Good or Bad we want it.

Apologies - I thought I did respond but apparently skipped over it. Thanks for your time, but the end-user isn't familiar with how pivot tables work and it would need to constantly be refreshed. There's also a lot of other things going on in the table. The example was just a simplified version of what I'm doing. Thanks again for your time. I work with Pivots all the time and love them for certain things.