Solved

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

Posted on 2014-11-24
15
69 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: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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

20 Experts available now in Live!

Get 1:1 Help Now