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

I need to merge Column B into one row based on the Column A value. Once the Column A value change, the new set of those rows will be merged. Below is an example:

Column A                  Column B
RMK_KEY                       RMK_TEXT      
207                        LEASE MAY BE EXTENDED ADDITIONAL 2 YEARS @ $100/PER
207                        NET ACRE ($31.21)

208                      PER NET MINERAL ACRES ($2,120)

The results should look like this:

207                        LEASE MAY BE EXTENDED ADDITIONAL 2 YEARS @ $100/PER NET ACRE ($31.21)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
mvill12Author Commented:
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?
mvill12Author Commented:
Also some numbers have 5 rows others have 2 or 3. It varies for each column A value.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Let's GoCommented:
The attached file uses a user-defined function ConcatenateIf which is slightly adjusted from an excellent UDF posted by HansV MVP at

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
        ConcatenateRange As Range, Optional Separator As String = ",") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Trim(Condition) Then
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIf = strResult
    Exit Function
    ConcatenateIf = CVErr(xlErrValue)
End Function

Open in new window

I have used the same spreadsheet data as SimonAdept's spreadsheet, but have retyped column A as numeric values (without any white space, which I assume was accidentally put in there when copying from EE).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
                    kk(.Item(k(i, 1)), 2) = kk(.Item(k(i, 1)), 2) & k(i, 2)
                End If
            End If
    End With
    If n Then
        Range("a1").Resize(n, 2) = kk
    End If
End Sub

Open in new window

mvill12Author Commented:
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!
Let's GoCommented:

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

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, " ")

Open in new window

This means:
check if any cells in the range $a2 to $a50 have the value XYZ
if so, concatenate the matching cells in column B, with a space (" ") in between (so that words remain separated).
mvill12Author Commented:
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?
Let's GoCommented:
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

Open 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.
Pratik MakwanaData AnalystCommented:
try this.....

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
Let's GoCommented:
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.
Pratik MakwanaData AnalystCommented:
yes its SQL query....
mvill12Author Commented:
The expert really helped me out and explain the resolution. Overall, best experience I had with the website compared to others out there.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.