Solved: Concatenate multiple un-adjacent columns

[Kam]

Hello,
I am trying to write vba code to allow a user to select multiple columns and then the code would concatenate the selected columns of data row by row.

The columns that are selected by the user would not always be adjacent and would vary file to file:

If the user selected cols A, B and E:

A B C D E ConcatenatedValue
1 2 4 5 6 126
7 8 9 0 j 78j

I guess I am stuck in trying to determine which columns the user has selected. If a user select cols A, B and E, how can I determine that in vba code?

Any help is appreciated (or a shove in the right direction! :)) Thanks!

[f2e4]

can you post a sample sheet?

[f2e4]

but the simple concatenate code for VBA would be:


Sub Join_Values()

Dim Option1 as string, Option2 as string, Option3 as string
Dim x as long

Option1 = cells(x, 1)
Option2 = cells(x, 2)
Option3 = cells(x, 5)

Cells(x,6) = Option1 & Option2 & Option3

End Sub

This is based on your example data above. I haven't included any code that determines your row and so just labelled it 'x'.

This is about as far as I can help you without seeing your sheet

[Kam]

There really isn't any sample, but I will get something together. The key is that I want to concatenate whichever columns the user has selected. Those columns will change with every sheet and will not be adjacent.

I really need a way to figure out in vba code which columns the user has selected.

I will post a sheet in a minute.

[Kam]

OK, I have posted a worksheet. In this worksheet, the User would have had columns C, E and F selected.

I want them to then run my code, and it would concatenate those selected columns into and End Result Col (which I highlighted in red in the file).

The code needs to be generic enough that it will concatenate whatevr cols the user has selected. It will change from file to file.

[f2e4]

Quick Question:

Will the selections always be on the same row?

[Kam]

Yes, the concatenated info will stay in the same row as it is in the original.

What is going to happen in reality is that a user is going to open up a file from one of our customers and they are going to see that a name is divided into fname and lname. They will select those two columns and concatenate them.

The next file they open might have fname, mi, lname and suffix. They will select those columns and concatenate them.

The users will have to do this with other columns in the file (acct#, exp dates, etc). Each file will look different and have the info in different columns. I am trying to automate this for them.
HTH

[f2e4]

OK so I have it very nearly finished. Not sure if it is what you want though as it asks you pick a range rather than you select a range then run code

Do you want a space in between the concatenated values or no space or something else?

[f2e4]

This is the program with no spaces between the concatenated values.

The program will ask the following when run:

What range you want to select (no limit on how many cells you can select i think)
What cell you want the output to be in

Please see attached file.

[Kam]

I am trying it, but I don't think I am selecting the cells properly. How am I supposed to select the data in B and the data in F??

Do I just highlight B2:B23 and what do I put in between when I select F2:F23?

Thanks!

[f2e4]

I am trying it, but I don't think I am selecting the cells properly. How am I supposed to select the data in B and the data in F??

Do I just highlight B2:B23 and what do I put in between when I select F2:F23?

Thanks!

At present the code only does one row at a time



Example:

Hold CTRL, press B4, then C4, then F4

Output = H4


I will look into modifying the code to do a whole range

[Kam]

At present the code only does one row at a time



Example:

Hold CTRL, press B4, then C4, then F4

Output = H4


I will look into modifying the code to do a whole range

Ahhh! Thanks for clarifying!

Our files that we recieve could be over 2000 records long.

Do you know if there is any way for vba code to determine which cols the user has selected? If we can determine the columns selected, I think I can get the concatenate to work.

Thanks for working on this!

[Kam]

I think I am going to have to do this a round about way...

The user will select the columns they want concatenated.

When the code is run, I will change the font color of those selected columns. Then I will cycle through each row and concatenate the colored font cells.

Then I will change the font color back to black.

I know this is NOT the best way to do this...but it may work for now.

I think I will post in a New Thread about how to determine in vba which columns the user has selected.

f2e4: If you get your code to work with ranges, I'd love to see it. I am going to (hopefully) have some time to play around with it tomorrow and see if I can't get it to work on all the rows.

Thanks!

[mikerickson]

Perhaps something like this:
Sub test()
Dim uiColumnsForInput As Range
Dim uiResultColumn As Range
Dim workingStr As String
Dim oneCol As Range

Rem user Selects some columns, currently selected cols are the default.
workingStr = "Use the mouse to select some columns." & vbCr & "Type a comma between each column."
On Error Resume Next
Set uiColumnsForInput = Application.InputBox(prompt:=workingStr, Default:=Selection.EntireColumn.Address, Type:=8)
On Error GoTo 0
If uiColumnsForInput Is Nothing Then Exit Sub: Rem Cancel pressed.
Set uiColumnsForInput = uiColumnsForInput.EntireColumn
workingStr = vbNullString

Rem user selects the location of the concatenated result
Do
workingStr = "Select a column to put the concatenated result."
On Error Resume Next
Set uiResultColumn = Application.InputBox(workingStr, Type:=8)
On Error GoTo 0
If uiColumnsForInput Is Nothing Then Exit Sub: Rem Cancel pressed.
Set uiResultColumn = uiResultColumn.EntireColumn.Columns(1)
If Application.Intersect(uiColumnsForInput, uiResultColumn) Is Nothing Then
workingStr = vbNullString
Set uiResultColumn = uiResultColumn.EntireColumn
Else
workingStr = "The result may not be in " & uiColumnsForInput.Address
End If
Loop Until workingStr = vbNullString

Rem concatenation formula placed in result column
For Each oneCol In uiColumnsForInput.Columns
workingStr = workingStr & "&RC" & oneCol.Column
Next oneCol

With uiResultColumn
With Application.Intersect(.Cells, .Parent.UsedRange.EntireRow)
.FormulaR1C1 = "=" & Mid(workingStr, 2)
.Value = .Value
End With
End With
End Sub
Note the default columns for concatenation, Selection.EntireColumn

[Kam]

Excellent!!!! This is what I need!! Thanks so much.

[esckay]

I was looking through the site and saw this solution that you have posted
This solution also solves a problem for me except a few very small changes.

I would like to have a separator between the concatenated columns something like ---
And always have the result in column A:A
But being not very bright at this I can?t see what changes I should make to your code to make this happen. Your help would be very much appreciated.

TIA

[mikerickson]

I added a couple of optional arguments to the sub.

The Delimiter can be any length. If a cell in the input column is blank, the resulting string will have two consecutive delimiters. (default = vbNullstring)
If the FixedResultColumnNumber<1 or ActiveSheet.Columns.Count<FixedResultColumnNumber then the user selects the result column.

For the OP use, both arguments could be omitted.
Sub ConcatenateColumns(Optional Delimiter As String, Optional FixedResultColumnNumber As Long)
Dim uiColumnsForInput As Range
Dim uiResultColumn As Range
Dim workingStr As String
Dim oneCol As Range

Delimiter = "&" & Chr(34) & Delimiter & Chr(34)
If FixedResultColumnNumber < 0 Or FixedResultColumnNumber > 256 Then FixedResultColumnNumber = 0

Rem user Selects some columns, currently selected cols are the default.
workingStr = vbNullString
Do
Set uiColumnsForInput = Nothing
workingStr = "Use the mouse to select some columns." & vbCr & "Type a comma between each column." & vbCr & workingStr
On Error Resume Next
Set uiColumnsForInput = Application.InputBox(prompt:=workingStr, Default:=Selection.EntireColumn.Address, Type:=8)
On Error GoTo 0
If uiColumnsForInput Is Nothing Then Exit Sub: Rem Cancel pressed.

With uiColumnsForInput
Set uiColumnsForInput = .EntireColumn
If FixedResultColumnNumber <= 0 Then
workingStr = vbNullString
ElseIf Application.Intersect(.Cells, .Parent.Columns(FixedResultColumnNumber)) Is Nothing Then
workingStr = vbNullString
Else
workingStr = "You may not concatenate from " & .Parent.Columns(FixedResultColumnNumber).Address & "."
End If
End With
Loop Until workingStr = vbNullString

Rem set the result column
workingStr = vbNullString
Do
Set uiResultColumn = Nothing
workingStr = "Select a column to put the concatenated result." & vbCr & workingStr
If 0 < FixedResultColumnNumber Then
Rem fixed result column
Set uiResultColumn = uiColumnsForInput.Parent.Columns(FixedResultColumnNumber)
Else
Rem user selects the location of the concatenated result
On Error Resume Next
Set uiResultColumn = Application.InputBox(workingStr, Type:=8)
On Error GoTo 0
If uiResultColumn Is Nothing Then Exit Sub: Rem Cancel pressed
End If

Set uiResultColumn = uiResultColumn.EntireColumn.Columns(1)
If Application.Intersect(uiColumnsForInput, uiResultColumn) Is Nothing Then
workingStr = vbNullString
Set uiResultColumn = uiResultColumn.EntireColumn
Else
workingStr = "The result may not be in " & uiColumnsForInput.Address & "."
End If
Loop Until workingStr = vbNullString

Rem concatenation formula placed In result column
For Each oneCol In uiColumnsForInput.Columns
workingStr = workingStr & Delimiter & "&RC" & oneCol.Column
Next oneCol

With uiResultColumn
With Application.Intersect(.Cells, .Parent.UsedRange.EntireRow)
.FormulaR1C1 = "=" & Mid(workingStr, Len(Delimiter) + 2)
.Value = .Value
End With
End With
End Sub

[esckay]

I have had a bit of a play but i get the same result

or i am just using it wrong.
maybe im just not able to concentrate enough to see what i'm supposed to do.
i have a range i select the range and then run the macro but it still asks me for the destination and does not put in a delimiter