Identify row values based on unique data

**krishnapunekar** · 07-01-2010, 04:29 AM

Can you please let me know, how to fetch data in all the columns of a row based on the unique values?

For example, assume below is the data in Column A, and Column B

Column A Column B
ID1 Test1
ID2 Test2
ID1 Test3
ID3 Test4
ID1 Test7
ID3 Test2

I need the output as below
ID1 Test1, Test3, Test7
ID2 Test2
ID3 Test4, Test2

**pike** · 07-01-2010, 05:24 AM

Hi krishnapunekar
Welcome to the forum
A quick site search finds this old classic

Sub tests()
    Dim a, i As Long, b(), n As Long, maxCol As Long, w()
    a = Range("a1").CurrentRegion.Resize(, 2).Value
    ReDim b(1 To UBound(a, 1), 1 To Columns.Count)
    With CreateObject("scripting.dictionary")
        .comparemode = vbTextCompare
        For i = 1 To UBound(a, 1)
            If Not .Exists(a(i, 1)) Then
                n = n + 1
                .Add a(i, 1), Array(n, 1)
                b(n, 1) = a(i, 1)
            End If
            w = .item(a(i, 1))
            w(1) = w(1) + 1
            b(w(0), w(1)) = a(i, 2)
            .item(a(i, 1)) = w
            maxCol = Application.Max(maxCol, w(1))
        Next
    Range("a1").CurrentRegion.ClearContents
    End With
  Range("A1").Resize(n, maxCol).Value = b
End Sub

**siva972377** · 07-01-2010, 06:35 AM

A B C D E F

1 ID1 Test1 ID1 Test1 Test3 Test7
2 ID2 Test2 ID2 Test2
3 ID1 Test3 ID3 Test4 Test2 Test4
4 ID3 Test4 ID4 Test1 Test2 0
5 ID1 Test7 ID5
6 ID3 Test2
7 ID3 Test4
8 ID4 Test1
9 ID4 Test2
10 ID4

Try the following array formula (ctrl+shift+enter) in D1 and copy the formula

{=IF(COUNTIF($A$1:$A$16,$C1)<COLUMNS($D:D),"",INDEX($A:$B,SMALL(IF($A$1:$A$16=$C1,ROW($A$1:$A$16),165537),COLUMNS(D:$D)),2))}

**krishnapunekar** · 07-01-2010, 07:05 AM

Iam finding it difficult to get output from this query.
Can you please let me know, what is 165537 in your query and what is the expected output?

**krishnapunekar** · 07-01-2010, 06:39 AM

Thanks Pike

I am really sorry, I missed to mention that, we need to achieve this without writing code and will have to using only combination of excel functions.
Because, we will have to later integrate this excel output to another third party tool, which does not support code, and supports only excel functions.
So, can you please give some clue on that.

As of now, I am able to find a lengthy way, I will share once I am completely done.

**pike** · 07-01-2010, 06:41 AM

Did you try siva972377 function?

**pike** · 07-01-2010, 07:32 AM

look at these to formula one by siva972377

**krishnapunekar** · 07-01-2010, 12:01 PM

Thanks a lot, I am trying to replicate what is there in excel, but I am facing issue in cell F13 (Please check attached file Example.xls).
It is displaying #NUM! error
I am unable to figure out what is the cause of this error
Can you please help me.