Grouping Data

[KarthickDijo]

Hi All,

I need to group few data (in a column) based on the another column. Kindly help me out how it can be done apart from VBA. Attached the sample input and output.

Thanks in advance

 

[RoryA]

Why are you using B3:B11>=A2 as the filter criteria?

 

[mohadin]

Hi
Try this option
Maybe

Sub test()
    Dim a As Variant, lr, i, x, s, k, itm
    a = Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 2)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> 0 Then
                If Not .exists(a(i, 1)) Then
                    .Add a(i, 1), a(i, 2)
                Else
                    .Item(a(i, 1)) = .Item(a(i, 1)) & " | " & a(i, 2)
                End If
            End If
        Next
      Cells(3, 4).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
       
    End With
End Sub

 

[Jimmypop]

Why are you using B3:B11>=A2 as the filter criteria?

It was a formula I found while searching which seemed to do the job... but it gave me the two "Sony" results so played around with it until got the correct result. Just not sure why.. trying to teach myself by combing different formulas... Realize that it actually means B3:B11 is bigger than or equal to A2... which might be more suited to numbers...

 

[KarthickDijo]

Hi there

Not sure if this will work for you?

Book1
	A	B	C	D	E
1	Input
2	Brand	Color		Brand	All Colours
3	Apple	Blue		Apple	Blue|Yellow
4	Apple	Yellow		Samsung	Red
5	Samsung	Red		Sony	Red|Green|Blue
6	Sony	Red		Lenovo	Orange|Black
7	Sony	Green		Dell	White
8	Sony	Blue
9	Lenovo	Orange
10	Lenovo	Black
11	Dell	White
Sheet1

Cell Formulas
Range	Formula
D3:D7	D3	=FILTER(A3:A12,(B3:B12>=A1),"NA")
E3:E7	E3	=TEXTJOIN("|",TRUE,IF(A3:A11=D3,B:B11,""))
Dynamic array formulas.

Turn the data into a table and load it into the data model using the Power Pivot tab. Then create a power pivot table and add a measure like:

=CONCATENATEX(VALUES(Table1[Color]),[Color],"|")

where Table1 is the name of the table you created at the start.

Am not getting the expected result, attached the result arrived. Is there more steps to be executed post this ??

 

[RoryA]

That's a calculated column, not a measure.