Solved

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

Posted on 2014-11-24
15
67 Views
Last Modified: 2014-12-08
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
Comment
Question by:mvill12
  • 5
  • 4
  • 2
  • +2
15 Comments
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40463522
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
 

Author Comment

by:mvill12
ID: 40463557
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
 

Author Comment

by:mvill12
ID: 40463560
Also some numbers have 5 rows others have 2 or 3. It varies for each column A value.
0
 
LVL 6

Accepted Solution

by:
Let's Go earned 500 total points
ID: 40463628
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
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 40463701
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
 

Author Comment

by:mvill12
ID: 40463716
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
 
LVL 6

Expert Comment

by:Let's Go
ID: 40463769
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:mvill12
ID: 40463789
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
 
LVL 6

Expert Comment

by:Let's Go
ID: 40463815
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
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 40463824
What's the error ?
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40463907
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
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40463993
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
 
LVL 6

Expert Comment

by:Let's Go
ID: 40465828
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
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40468419
yes its SQL query....
0
 

Author Closing Comment

by:mvill12
ID: 40486830
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now