Merge rows on Column B based on change of data on Column A

Here's a quick formula-based method. It add3 columns to the right. Filter for non-blanks on either of the final two columns to get a merged list in the format you wanted.

Obviously there are lots of code-based ways of doing this type of thing, but for quick one-offs I find formulas faster and easier where it is acceptable to filter the results and paste the results columns to a new sheet etc.
EE-28568870.xlsx

Thank you for your help. The problem is I have 46,000 rows of this type of data. What do you suggest for a file that large?

Also some numbers have 5 rows others have 2 or 3. It varies for each column A value.

Sub kTest()
    
    Dim k, i As Long, kk(), n As Long
    
    k = Range("a1:b" & Range("a" & Rows.Count).End(3).Row).Value2
    ReDim kk(1 To UBound(k, 1), 1 To 2)
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(k, 1)
            If Len(k(i, 1)) Then
                If Not .exists(k(i, 1)) Then
                    n = n + 1
                    kk(n, 1) = k(i, 1): kk(n, 2) = k(i, 2)
                    .Item(k(i, 1)) = n
                Else
                    kk(.Item(k(i, 1)), 2) = kk(.Item(k(i, 1)), 2) & k(i, 2)
                End If
            End If
        Next
    End With
    If n Then
        Worksheets.Add
        Range("a1").Resize(n, 2) = kk
    End If
            
End Sub

Select allOpen in new window

Kris

krishnakrkc - I am getting an error on the line kk(.Item(k(i, 1)), 2) = kk(.Item(k(i, 1)), 2) & k(i, 2). What value should I change? Thanks!

mvill12,

I am responding to your private message here for the benefit of other EE users. I will also send you a revised spreadsheet with your confidential data.

You asked privately for more information on how to use the macro (i.e. the user-defined function).  

1) You need to open the Visual Basic editor (click on the button on the Excel ribbon, Developer tab).  If the Developer tab is not visible, see https://support.office.com/en-au/article/Show-the-Developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45

2) Insert a module (Insert, Module) then copy the user defined function into that module (see screenshot).  You can then close the visual basic editor.

3) Save the file as a .xlsm (macro-enabled) file instead of a .xlsx file.

4).  You can then use the function quoted in my response in your spreadsheet.  

For example, you could type "XYZ" in cell b60 and the following in cell $c60

 =concatenateif($a2:$a50, b60, $b2:$b50, " ")

Select allOpen in new window

This means:

Hi Let's Go - I ran everything like you instructed and I am getting an error when using the formula as an ambiguous name detected: ConcatenateIf.

I basically copied the IDs (column A) and placed them on row 75236 and the value of the formula is #name? - for all values. Your thoughts?

The #NAME? error occurs when Microsoft Excel doesn't recognise text in a formula, so the first thing to do is make sure you have not mistyped it.

Given that the RMK_KEY is a number, I also suggest changing line 11 of the UDF code to HansV's original

If CriteriaRange.Cells(i).Value = Condition Then

Select allOpen in new window

.

I have sent you a file with your private data separately.

What's the error ?

Thank you for your help. The problem is I have 46,000 rows of this type of data. What do you suggest for a file that large?

You're probably committed to another route, but 46,000 rows or 460,000 rows do not make the 3 columns of formulae I suggested out of the question, and avoid the need to add a module to the workbook and save is as .xlsm (which can have some minor implications when opening or sharing the file via email etc). It can be just a case of copying the 3 new cells in row 2, then typing ctrl+End to get to the bottom of the spreadsheet, select the 3 columns, type shift+up, then ctrl+D to fill the formulae down.

Also some numbers have 5 rows others have 2 or 3. It varies for each column A value.

The number of rows to be concatenated shouldn't matter, though the total length of the text string can't exceed 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

try this.....

select RMK_KEY, RMK_TEXT
from (
  select RMK_KEY,
  STUFF ((Select ' '+RMK_TEXT From TableName p1 Where p2.RMK_KEY=P1.RMK_KEY
            For XML PATH('')),1,1,'') as RMK_TEXT
  , row_number() over (partition by nameid order by RMK_KEY) r
  from TableName p2
) firstRow
where firstRow.r = 1

Hi, Pratik

Is that SQL code (not in my current skill set)?  The OP will need guidance on how to use that for his Excel spreadsheet.

yes its SQL query....

The expert really helped me out and explain the resolution. Overall, best experience I had with the website compared to others out there.