Solved

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

Posted on 2014-11-24
15
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
15 Comments
 
LVL 18

Expert Comment

by:Simon
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 

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:Simon
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

696 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