Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

concatenate 2 columns down 100 rows

data looks like this
Col1    Col2
2345 Sore throat
1123Q Bad cough
etc down 100 rows
Would like result to look like this (in one cell)
2345-Sore throat; 1123Q-Bad Cough; etc

Thanks in advance for your prompt response!

Schuyler
0
schuyler
Asked:
schuyler
  • 4
  • 3
  • 2
  • +3
1 Solution
 
Patrick MatthewsCommented:
schuyler,

You would need VBA code for that.

For example:

Function Concat(Col1 As Range, Col2 As Range, Optional Delim1 As String = "-", Optional Delim2 As String = "; ")

    Dim Counter As Range

    For Counter = 1 To Col1.Cells.Count
        Concat = Concat & Delim2 & Col1.Cells(Counter) & Delim1 & Col2.Cells(Counter)
    Next

    Concat = Mid(Concat, Len(Delim2) + 1)

End Function

Open in new window



Assuming your data are in, say, A2:B100...

=Concat(A2:A100,B2:B100)

Patrick
0
 
royhsiaoCommented:
Hi Schuyler,

You could try the following code as well.

=A2& "-" &B2
Combine.JPG
0
 
gyettonCommented:
assume data in A1 & B1 in C1 put =A1 & "-" & B1
use enter to finish editing.
move the mouse to the bottom right hand corner of the cell the cursor will change to a narrow cross.
Either drag down to row 100, or if you have data that stops at row 100 just double click, the formula will autofill down until it comes to a break in the data.
You could of course use both techniques, use the double click then goto the bottom cell (by double clicking anywhere else on the bottom edge) and then just drag down the last few rows :)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
schuylerAuthor Commented:
Thanks...but I know how to do that, what I need to do is have all the data in one cell, I need to transfer it to another sheet as a list of data

Patrick,
in the excel file, in a cell I put =Concat(A19:B176,B2:C176) but nothing is happening (yes I put the function in a module)
Thanks
0
 
Patrick MatthewsCommented:
schuyler,

Please post your file.

Patrick
0
 
philip m o'brienCommented:
Schuyler, you could also look at this previous question and see if the same solution works for you,
regards
0
 
jppintoCommented:
Here's an example of the code. I've attached a sample sheet for you to test.

jppinto
Sub ConcatenateText()
    
    Dim lastRow As Long
    Dim i As Long
    Dim p As Long
    Dim conValue As String
      
    lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To lastRow
        conValue = conValue & Sheets("Sheet1").Cells(i, 1).Value & "-" & Sheets("Sheet1").Cells(i, 2).Value & ";"
    Next
    Sheets("Sheet1").Cells(1, 6).Value = conValue
    
End Sub

Open in new window

ConcatenateText-VBA.xlsm
0
 
schuylerAuthor Commented:
Patrick,
I can't send the file but if you look at what example that royhsiao posted above, that's what the file looks like, even column C...if we could use that column using an ";" btwn each set of values that would work as well.

thanks
0
 
jppintoCommented:
Did you tryed my code?
0
 
schuylerAuthor Commented:
jppinto
Where do I put the call?  I can step thru your code jppinto but where is it on the spreadsheet...sorry its been a while since I needed to code in excel....appreciate everyone's help
0
 
jppintoCommented:
Alt+F11 takes you to the VBA editor. You can place your code into your file and the click on the Run button (or press F5) to run your code on your sheet.

jppinto
Capture.JPG
0
 
schuylerAuthor Commented:
thanks so much!

patrick I know your code was similar but i couldn't get it to work and jppinto's did...appreciate your efforts!!

Schuyler
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now