Solved

Merge two columns together

Posted on 2011-02-18
19
1,097 Views
Last Modified: 2012-05-11
I want to automate the merge of column B with Column D if Column D is not empty and add the word sub between it. I would do this manually, but there are over 50,000 records.

Given:
A               B                C                 D                
124         Pulley          72.50        
135         Tank            37.50         A034
256         Pump           11.13         N005
654         Tube            28.89

Output:
A                B                C                D             E
124         Pulley          72.50                        Pulley
135         Tank            37.50         A034       Tank Sub A034
256         Pump           11.13         N005       Pump Sub  N005
654         Tube            28.89                        Tube
0
Comment
Question by:techpr0
  • 9
  • 6
  • 2
  • +1
19 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 34930245
If your data starts in A1:
Sub x()

Dim v, i As Long

With Range("A1").CurrentRegion
    v = .Value
    .ClearContents
End With

For i = LBound(v, 1) To UBound(v, 1)
    If v(i, 4) = "" Then
        v(i, 4) = v(i, 2)
    Else
        v(i, 4) = v(i, 2) & " Sub " & v(i, 4)
    End If
Next i

Range("A1").Resize(UBound(v, 1), 4) = v

End Sub

Open in new window

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34930251
Formula for E1:

=If(D1="", B1, B1 & " Sub " & D1)

then copy it down 50000 times (double-click the little black box in the bottom right corner, or there are other ways to do that.)
0
 
LVL 4

Expert Comment

by:SmittyPro
ID: 34930260
How about a formula approach:

=IF(ISBLANK(D2),B2,B2&" Sub "&D2)

HTH,
0
 

Author Comment

by:techpr0
ID: 34930339
Hi StephenJR,

It would actually start at A2, A1 would be the column headers. How do I implement your solution. I have only did formula inside individual cells up to this point, but your solution was something that i expected to receive. I  just don't know where i put this .

rspahitz,
Your solution worked, but I am not sure if this would import into UA correctly since it is a formula. I will have to wait until Monday morning until then I am looking for a solution similar to StephenJR

SmittyPro,
This did not work. I take it because the field is technically not blank
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34930352
thanks tech.

For Stephen's solution, you'll need to go to the VB world (Alt-F11) and open the code for a sheet or module (e.g. menu Insert | Module.)  Paste his code, put the cursor somewhere inside the code and press F5 to run it.
0
 
LVL 4

Expert Comment

by:SmittyPro
ID: 34930446
This did not work. I take it because the field is technically not blank

Then you'd just use a similar approach to the previous example: = IF(x=""...
0
 

Author Comment

by:techpr0
ID: 34931479
It worked but needs some minor adjustments
1. I need it to start at row 2 not 1
2. It  replaces column D instead of creating a new column in E
3. Is there a way to prompt it for what 2 columns to use?
0
 

Author Comment

by:techpr0
ID: 34931483
In the last post when i said it worked I was referring to this post

For Stephen's solution, you'll need to go to the VB world (Alt-F11) and open the code for a sheet or module (e.g. menu Insert | Module.)  Paste his code, put the cursor somewhere inside the code and press F5 to run it.

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34931613
Great!  so you got into the VB area and added the code.
To prompt for the columns, there are several way.
One easy way would be to use the InputBox (but you'll have to use 1 for each prompt or do some extra work...the other way, a UserForm is more complicated to set up.)

 
Dim iFirstColumn As Integer
Dim iSecondColumn As Integer
Dim iDestinationColumn As Integer

iDestinationColumn = 5

iFirstColumn = Val(InputBox("First column to pick?", "Pick", 2))
If iFirstColumn = 0 Then
   Exit Sub
End If

iSecondColumn = Val(InputBox("Second column to pick?", "Pick", 4))
If iSecondColumn = 0 Then
   Exit Sub
End If

Open in new window


Then update the "2" and "4" with iFirstColumn and iSecondColumn in the v(i, #) references.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 22

Expert Comment

by:rspahitz
ID: 34931614
sorry...add the above inserted at line 4
0
 

Author Comment

by:techpr0
ID: 34933696
I edited the code based on your last post with 3 modificaitons needed.

1. It is still updating column D instead of column E. I guess it is because i am not using iDestinationColumn. Where do i put this.

2. I need it to skip row 1 since this is the header fields.

3. Do i have to go through the process of adding the Vb code to each spreadsheet or is there some way I can add this and recall it everytime I am given an new spreadsheet?
Sub x()

Dim v, i As Long

Dim iFirstColumn As Integer
Dim iSecondColumn As Integer
Dim iDestinationColumn As Integer

iDestinationColumn = 5

iFirstColumn = Val(InputBox("First column to pick?", "Pick", 2))
If iFirstColumn = 0 Then
   Exit Sub
End If

iSecondColumn = Val(InputBox("Second column to pick?", "Pick", 4))
If iSecondColumn = 0 Then
   Exit Sub
End If 


With Range("A1").CurrentRegion
    v = .Value
    .ClearContents
End With

For i = LBound(v, 1) To UBound(v, 1)
    If v(i, iSecondColumn) = "" Then
        v(i, iSecondColumn) = v(i, iFirstColumn)
    Else
        v(i, iSecondColumn) = v(i, iFirstColumn) & " Sub " & v(i, iSecondColumn)
    End If
Next i

Range("A1").Resize(UBound(v, 1), 4)= v

End Sub

Open in new window

0
 
LVL 24

Accepted Solution

by:
StephenJR earned 300 total points
ID: 34934160
Sticking with the mongrel code(!), try this. If it doesn't work post a sample workbook.
Sub x()

Dim v, i As Long

Dim iFirstColumn As Integer
Dim iSecondColumn As Integer
Dim iDestinationColumn As Integer

iDestinationColumn = 5

iFirstColumn = Val(InputBox("First column to pick?", "Pick", 2))
If iFirstColumn = 0 Then
   Exit Sub
End If

iSecondColumn = Val(InputBox("Second column to pick?", "Pick", 4))
If iSecondColumn = 0 Then
   Exit Sub
End If


With Range("A1").CurrentRegion
    v = .Resize(, .Columns.Count + 1).Value
    .ClearContents
End With

For i = LBound(v, 1) + 1 To UBound(v, 1)
    If v(i, iSecondColumn) = "" Then
        v(i, iDestinationColumn) = v(i, iFirstColumn)
    Else
        v(i, iDestinationColumn) = v(i, iFirstColumn) & " Sub " & v(i, iSecondColumn)
    End If
Next i

Range("A1").Resize(UBound(v, 1), UBound(v, 2)) = v

End Sub

Open in new window

You can put it in your personal workbook and it will then be accessible all the time.
0
 

Author Comment

by:techpr0
ID: 34935295
The last code StephenJR posted worked perfectly.

WHat do you mean by put in a personal workbook to make it accessible at all time. Is there some steps you can let me know to use? i would basically be getting spreadsheets on a daily basis from clients and running this code.
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 200 total points
ID: 34935378
Excel has a Personal Workbook that is similar to Word's Normal.DOT document template.
By storing macros there, they will be available with any new or existing workbook that you open from that machine.

To make this available in Excel 2007 / 2010, you'll need to record a macro there first.  Go to the View tab, Macros box, Macros dropdown arrow, Record Macro...
In the "Store macro in" dropdown, select Personal Macro Workbook then click the OK button.
Next go back to the same area and pick Stop Recording.

You should now see the PERSONAL.XLSB VBAProject in VB (Alt-F11, Ctrl-R)
Place the macro into a module in that project and it should be available everywhere.
0
 

Author Comment

by:techpr0
ID: 34935529
I just tried but I did something wrong. This is the steps I took
1. View-->Macros-->Record Macro
2. I placed the following in the Record Window
Macro Name --> Test
3. Shortcut Key -->Ctrl + l
0
 

Author Comment

by:techpr0
ID: 34935535
I just tried but I did something wrong. This is the steps I took
1. View-->Macros-->Record Macro
2. I placed the following in the Record Window
            Macro Name --> Test
            Shortcut Key -->Ctrl + l
            Store Macro in--> Personal Macro Workbook
3. Select OK
4. View-->Macros-->Stop Macro
5. Alt F11
6. Insert--> Module
7. Paste code in New Module
8. Save
9. Run Macro
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34935614
Looks good.  From Excel, if you do Alt-F8, do you see the procedure that you just pasted?
0
 

Author Closing Comment

by:techpr0
ID: 34935704
Thanks. THe exact solution I was looking for. Thanks for being so patient.
0
 

Author Comment

by:techpr0
ID: 34946019
Hey All,

I just added another question that needs a similar solution.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26837074.html
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Outlook Free & Paid Tools
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

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