Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

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                       OPTION TO EXTEND PRIMARY TERM FOR AN ADDITIONAL 2 YEARS       
208                      FROM THE EXPIRATION OF THE ORIGINAL PRIMARY TERM @ $600
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)
0
mvill12
Asked:
mvill12
  • 5
  • 4
  • 2
  • +2
1 Solution
 
SimonCommented:
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
0
 
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?
0
 
mvill12Author Commented:
Also some numbers have 5 rows others have 2 or 3. It varies for each column A value.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 https://answers.microsoft.com/en-us/office/forum/office_2010-excel/concatenate-values-that-meet-certain-conditions/b8e0f294-de85-4b30-bba3-c79d9bc4982f.

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
ErrHandler:
    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).
EE-28568870-1.xlsm
0
 
krishnakrkcCommented:
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

Open in new window


Kris
0
 
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!
0
 
Let's GoCommented:
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, " ")

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).
0
 
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?
0
 
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.
0
 
krishnakrkcCommented:
What's the error ?
0
 
SimonCommented:
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.
0
 
Pratik MakwanaData AnalystCommented:
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
0
 
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.
0
 
Pratik MakwanaData AnalystCommented:
yes its SQL query....
0
 
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now