Macro to add quotes, commas, transpose and select the transposed rows

[sncb]

Hi All,

I am constantly on a daily basis having to prepare data for SQL in excel so was wondering if there is an automatic way to add quotes, commas, transpose and select the transposed rows which i could simply paste into SQL.

Here's what I do:
1. I paste all my data in column B1 onwards
2. Add quotes A1 and C1
3. Add commas in D1
4. Concatenate in E1
5. Copy down the formula to all the rows of data
6. Copy column of data in E and paste values in F
7. Select all the values in F and Transpose into rows from cell G2 onwards.

Since I do this very often, is there a better way using a macro such that I can simply paste the column of data in B1 each time and run the macro so that it does the above steps without all the manual work involved?

Thanks for any assistance towards this manual effort.

 

[dreid1011]

If you can provide some sample data and the expected results, I'm sure someone can come up with something.

 

[sncb]

Thanks Dreid,

Pretty much as simple as this. Only difference is my data in column B varies each time.

 

[dreid1011]

Can the result be in a single cell or does it need to be spread out over columns? Also, which version of Excel are you using, 2010 or 365?

Also, I know your title mentions a macro, but would a formula be acceptable?

 

[sncb]

Either way, really dosent matter since Im going to anyway copy this from excel to SQL.

It will be used on another PC using 365 not this laptop where I'm trying right now which has 2010.

 

[sncb]

Can the result be in a single cell or does it need to be spread out over columns? Also, which version of Excel are you using, 2010 or 365?

Also, I know your title mentions a macro, but would a formula be acceptable?

Yes a formula would also help but ideally looking for a macro to reduce clicks but a formula should work great as well. Thanks again

 

[dreid1011]

Okay, well in the meantime, here is the formula if you are using Excel 365:

Book1
	A	B	C	D	E	F	G	H	I	J
1		Code1		'Code1',	'Code2',	'Code3',	'Code4',	'Code5',	'Code6',	'Code7',
2		Code2
3		Code3
4		Code4
5		Code5
6		Code6
7		Code7
Sheet5

Cell Formulas
Range	Formula
D1:J1	D1	=TEXTSPLIT("'"&TEXTJOIN("',@'",,B1:B7)&"',","@")
Dynamic array formulas.

I can work on a macro after lunch.

 

[sncb]

Okay, well in the meantime, here is the formula if you are using Excel 365:

Book1
	A	B	C	D	E	F	G	H	I	J
1		Code1		'Code1',	'Code2',	'Code3',	'Code4',	'Code5',	'Code6',	'Code7',
2		Code2
3		Code3
4		Code4
5		Code5
6		Code6
7		Code7
Sheet5

Cell Formulas
Range	Formula
D1:J1	D1	=TEXTSPLIT("'"&TEXTJOIN("',@'",,B1:B7)&"',","@")
Dynamic array formulas.

I can work on a macro after lunch.

Thank you v much. That worked great. Only if you have time a macro would be mighty useful.

 

[dreid1011]

Give this a try:

Private Sub Combine()
Dim lRow As Long, i As Long
Dim combo() As Variant
lRow = Range("B" & Rows.Count).End(xlUp).Row
ReDim combo(lRow - 1)
On Error Resume Next
For i = 0 To lRow - 1
    combo(i) = "''" & Range("B" & i + 1).Value & "',"
    Debug.Print combo(i)
Next i
Range("G2").Resize(, lRow).Value = combo
End Sub

 

[sncb]

Thanks Dreid. That worked perfectly well.

The output did have double quotes on the left side of each item that was transposed but I adjusted the macro accordingly.

Changed from combo(i) = "''" to combo(i) = "'"

Works great. Thank you for your time and effort and for easing my daily work.