Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Merge two columns together

Posted on 2011-02-18
Medium Priority
1,157 Views
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
Question by:techpr0
[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
• 9
• 6
• 2
• +1

LVL 24

Expert Comment

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
``````
0

LVL 22

Expert Comment

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

ID: 34930260

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

HTH,
0

Author Comment

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

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

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

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

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

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
``````

Then update the "2" and "4" with iFirstColumn and iSecondColumn in the v(i, #) references.
0

LVL 22

Expert Comment

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

Author Comment

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
``````
0

LVL 24

Accepted Solution

StephenJR earned 1200 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
``````
You can put it in your personal workbook and it will then be accessible all the time.
0

Author Comment

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

rspahitz earned 800 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

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

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

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

Author Closing Comment

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

Author Comment

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

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me â€¦
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month10 days, 19 hours left to enroll