Merge two columns together

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
techpr0Asked:
Who is Participating?
 
StephenJRCommented:
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
 
StephenJRCommented:
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
 
rspahitzCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SmittyProCommented:
How about a formula approach:

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

HTH,
0
 
techpr0Author Commented:
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
 
rspahitzCommented:
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
 
SmittyProCommented:
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
 
techpr0Author Commented:
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
 
techpr0Author Commented:
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
 
rspahitzCommented:
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
 
rspahitzCommented:
sorry...add the above inserted at line 4
0
 
techpr0Author Commented:
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
 
techpr0Author Commented:
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
 
rspahitzCommented:
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
 
techpr0Author Commented:
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
 
techpr0Author Commented:
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
 
rspahitzCommented:
Looks good.  From Excel, if you do Alt-F8, do you see the procedure that you just pasted?
0
 
techpr0Author Commented:
Thanks. THe exact solution I was looking for. Thanks for being so patient.
0
 
techpr0Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.