asked on

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

StephenJR

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

rspahitz

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.)

SmittyPro

How about a formula approach:

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

HTH,

techpr0

ASKER

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

rspahitz

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.

SmittyPro

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=""...

techpr0

ASKER

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?

techpr0

ASKER

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.

rspahitz

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.

rspahitz

sorry...add the above inserted at line 4

techpr0

ASKER

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

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

techpr0

ASKER

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.

SOLUTION

rspahitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

techpr0

ASKER

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

techpr0

ASKER

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

rspahitz

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

techpr0

ASKER

Thanks. THe exact solution I was looking for. Thanks for being so patient.

techpr0

ASKER

Hey All,

I just added another question that needs a similar solution.

https://www.experts-exchange.com/questions/26837074/Add-new-column-based-on-precentage-of-another-column.html