data reformat

Topic

New Reply

hi all,

i have the data sample as per the attachment, what i need is a way to convert the data in column a, to the format in column b. assume excel2010

Tia
dubdub

Reply | Quote

Replies

This will do it:
=”(‘”&A2&”‘,'”&A3&”‘,'”&A4&”‘,'”&A5&”‘)”

Steve

Reply | Quote

thanks Steve for the prompt reply, but i have like 300+ elements in column a, is there a faster way to do it instead of formula.

Reply | Quote

One way is to do each one, adding in the elements of all the above. Copy it down the column and the formula at the end is the one you want to copy and paste-special…

Steve

Reply | Quote

Put Steve’s solution in then use the fill handle.

Reply | Quote

excuse my ignorance, but i cant not see how the fill handle going to work with 300+ elements without entering their names in the formula.
i do not claim i am right but may be i am missing something….

TIA

Reply | Quote

Dub,

Try This:

Code:

Public Sub test()
col = 1  [COLOR=”#008000″]’CHANGE THE 1 TO THE COLUMN OF DATA[/COLOR]
LastRow = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row
Data = “(”
For i = 2 To LastRow – 1
Data = Data & “‘” & Cells(i, col).Value & “‘,”
Next i
Data = Data & “‘” & Cells(i, col).Value & “‘)”
Cells(2, 2).Value = Data  [COLOR=”#008000″]’CHANGE THE COORDINATES TO THE CELL YOU WANT TO POPULATE[/COLOR]
End Sub

(‘wert-12′,’rtyu-1′,’rtyu-1111′,’dfrt-123’)

The code will automatically find the last row of data in column 1. It will take all the values in column A and build a single string with the values inside single quotes, separated by commas, and wrapped in parentheses as you displayed in your sample. The code will accommodate added values in column A by rerunning the code. Place it in a standard module.

HTH,
Ted

Reply | Quote

first let me say sorry for the late reply, and thanks to you all, Maudibe, you are a good mind reader, and absolutely right that is my aim, and my apology to all of you because my request was not clear enough.

dubdub

Reply | Quote

If you are looking for a formula to enter into a cell, here is some code that needs to be run only once that will build Steve’s formula into the cell B2 and append it up to the last row. The entered formula will function as if you directly entered it into the formula bar yourself, however saving the time for 300 references. I believe there is a limit to the size of a formula that the formula bar will accept. This may have been a limitation for older versions but, correct me if I am wrong, 256 characters comes to mind.

Code:

Public Sub WriteFormula()
col = 1  [COLOR=”#008000″]’CHANGE THE 1 TO THE COLUMN OF DATA[/COLOR]
LastRow = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row
calc = “=” & Chr(34) & “(‘” & Chr(34) & “&”
For i = 2 To LastRow – 1
calc = calc & “A” & i & “&” & Chr(34) & “‘,'” & Chr(34) & “&”
Next i
calc = calc & “A” & i & “&” & Chr(34) & “‘)” & Chr(34)
Cells(2, 2).Formula = calc [COLOR=”#008000″]’CHANGE THE COORDINATES TO THE CELL YOU WANT TO POPULATE[/COLOR]
End Sub

With 300 rows, you will easily exceed the formula size limit if one does exist, in which case, you can break the column into segments or run the code from my previous post.

HTH,
Maud

Reply | Quote

How about this in B2 and then fill down:

=IF(0=MOD(ROW()-2,4),”(“&INDIRECT(“A”&ROW())&”,”&INDIRECT(“A”&1+ROW())&”,”&INDIRECT(“A”&2+ROW())&”,”&INDIRECT(“A”&3+ROW())&”)”,””)

This will do them in groups of 4.

Reply | Quote

Kweaver,

There is an unneeded space that is causing an error in “R OW”. Very clever. Are you able to work in the apostophes and append as you fill down?

(‘wert-12′,’rtyu-1′,’rtyu-1111′,’dfrt-123’)

Would very much like to tuck this one away in my bag if tricks!

Maud

Reply | Quote

That space is because of the wrap-around in the reply.

The following should handle the apostrophes.

=IF(0=MOD(ROW()-2,4),”(‘”&INDIRECT(“A”&ROW())&”‘,'”&INDIRECT(“A”&1+ROW())&”‘,'”&INDIRECT(“A”&2+ROW())&”‘,'”&INDIRECT(“A”&3+ ROW())&”‘)”,””)

If dubdub wanted a rolling group of 4 as long as there was a group of 4, this ought to do it:

=IF(“”INDIRECT(“A”&(ROW()+3)),”(‘”&INDIRECT(“A”&ROW())&”‘,'”&INDIRECT(“A”&1+ROW())&”‘,'”&INDIRECT(“A”&2+ROW())&”‘,'”&INDIRECT(“A”&3+ROW())&”‘)”,””)

Reply | Quote

Very cool, Thanks

Reply | Quote

I hear it was pretty hot and humid in PA today. Grew up in Willow Grove and went to college in PA.
Now I’m in Paradise. GRIN

Reply | Quote

Lived in Willow Grove for many years. Can still walk to it if I get an early start. You might have been my neighbor and/or college mate. California..lucky you!

Reply | Quote

Hmm. Went to Upper Moreland H.S. Went to Bloomsburg under grad and Bucknell grad school. Small world that you lived there, too. Used to work summers at Willow Grove Park — that should date me big time!! Just in case you didn’t know…went from WGP to the largest bowling alley on 1 floor to the mall. Trivia history.

Reply | Quote

Rode many times on the White alps, the Thunderbolt, and the moon ride. Did you prefer the red side or the blue side in the bowling alley? Midnight bowling with the blue pin. They never had tournaments there because it was built over the lake and the lanes were warped. I hung out at Shaw’s Speed shop and Horn and Hardarts’. Remember Murphy’s 5 & 10 before it became Marshall’s. Went to Arch Bishop Wood but knew many from UMHS. Temple U for me. I think we are both antiques.

Reply | Quote

We should probably stop writing all this person reflection stuff in the Excel thread…but, 116 Thunderbolt lanes is a great memory.
Hankin, Hankin, Hankin and Shankin — all of us youngsters used to love saying that partnership name.

Reply | Quote

True!

I think what dubdub wants is to take an entire column and transpose it horizontally into one cell. From his post, I gather that he is not looking to copy the formula down in every cell in column B, he just doesn’t want to to manually enter 300 cell references into one formula. He may be looking for a formula to do this with a range. Dub, apologies if I am mistaking the intent of your post.

Reply | Quote

Will await his reply.

Reply | Quote

Dub,

The first code will do just that. It will take all the cell values from col A, build the string with the puntuation, then place the string in cell B2. My second code will build Steve’s formula, which works well, from all the cell values in col A, place the formula in cell B2, and then the completed formula will build the string.

Kweaver presented an ingenius approach, one that I would not have thought of doing, to build the sample string. His works perfectly also. If they can work a range into the formula, I would use their solution first.

Good luck,
Maud

Reply | Quote

This has been an interesting and fun problem. Maud’s solution in VB is the best way to accomplish the result you want, dub.
It quickly places all the cells together, properly formatted, in ONE cell. I’ve attached a sample workbook with that solution as well as my two versions, and even a strange third version that ultimately does what you want without VB, but you have to adjust the LAST row and then copy it to the cell you want (the last row has the ultimate solution). I had to create the first cell and then the second, then fill down and fix the last cell. (ugly)

This was fun to think about.

Reply | Quote