CODEWITHSUNDEEP
excelexcel-formula
tedstrich
tedstrich

Reputation: 23

Displaying all the values of the repeating id using excel

enter image description hereI have two columns on the left Column A and Column B COlumn A has the repating ids and columb b has the corresponding values in it. how do i filter the column A VALUES AND show both their corresponding values on right.

Column A Column B 1000 5 1000 ann 2000 chris 2000 56 2001 Beth 3000 Mark 3000 1

output should show: ColumnA, Column B

1000 5, ann 2000 56, beth 3000 Mark, 1

Upvotes: 0

Views: 207

Answers (3)

barryleajo
barryleajo

Reputation: 1952

An example using VBA Autofilter. Configure the position of Cols A, B, O (output), start and end rows to suit. 

Option Explicit
Sub xfrMatches()
Dim ws As Worksheet
Dim colA As Long, colB As Long, strowA, endRowA As Long
Dim colO As Long, stRowO As Long, endRowO As Long
Dim c As Long
Dim crit1 As Variant
Dim tmpStr As String
Dim cl As Range

Set ws = Sheets("Sheet1")

colA = 1
colB = 2
strowA = 2
colO = 7
stRowO = 3

ws.AutoFilterMode = False

    With ws
        endRowA = .Cells(Rows.Count, colA).End(xlUp).Row
        endRowO = .Cells(Rows.Count, colO).End(xlUp).Row
            For c = stRowO To endRowO
            ws.AutoFilterMode = False
            crit1 = .Cells(c, colO).Value
                With .Range(.Cells(strowA, colA), .Cells(endRowA, colB))
                    .AutoFilter
                    .AutoFilter Field:=1, Criteria1:=crit1
                End With
                With .AutoFilter.Range
                    tmpStr = ""
                        For Each cl In .Columns(colB).Offset(1, 0).SpecialCells(xlCellTypeVisible)
                            tmpStr = tmpStr & "," & cl.Value
                        Next cl
                    ws.AutoFilterMode = False
                End With
            .Cells(c, colO).Offset(0, 1).Value = Mid(tmpStr, 2, Len(tmpStr) - 2)
            Next c
    End With

End Sub

Upvotes: 0

Daniel
Daniel

Reputation: 1835

It depends on your exact scenario, but if your A values are in ascending order, you can do this:

First, add the following macro for concatenation:

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
Dim Cell As Range, Area As Variant
If IsMissing(Delimiter) Then Delimiter = ""
For Each Area In CellRanges
    If TypeName(Area) = "Range" Then
        For Each Cell In Area
            If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value
        Next
    Else
        ConCat = ConCat & Delimiter & Area
    End If
Next
ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function

Then, put the following formula in your results column:

=ConCat(",",INDIRECT("B"&MATCH(D3,$A$1:$A$6,0)&":B"&MATCH(D3,$A$1:$A$6,1)))

This basically finds the first and last occurrence of the search value, then concatenates all the values within that range.

Note that this is specifically for date in these cells:

Cell locations

However, it can be easily modified for wherever your data resides by simply adding the starting data row # (using the ROW() function) to both match results.

Upvotes: 0

Dubison
Dubison

Reputation: 768

Paste this to column C related row and copy for remaining cells.

=IF(A2=A1,C1&","&B2,B2)

enter image description here

Upvotes: 1

Related Questions