Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: data reformat

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    data reformat

    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
    Attached Files Attached Files
    TIA
    dubdub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This will do it:
    ="('"&A2&"','"&A3&"','"&A4&"','"&A5&"')"

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.
    TIA
    dubdub

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Put Steve's solution in then use the fill handle.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    Last edited by dubdub; 2013-05-31 at 15:54.
    TIA
    dubdub

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Dub,

    Try This:

    Code:
    Public Sub test()
    col = 1  'CHANGE THE 1 TO THE COLUMN OF DATA
    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  'CHANGE THE COORDINATES TO THE CELL YOU WANT TO POPULATE
    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
    Last edited by Maudibe; 2013-06-01 at 12:28.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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  'CHANGE THE 1 TO THE COLUMN OF DATA
    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 'CHANGE THE COORDINATES TO THE CELL YOU WANT TO POPULATE
    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
    Last edited by Maudibe; 2013-06-01 at 12:34.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    How about this in B2 and then fill down:

    =IF(0=MOD(ROW()-2,4),"("&INDIRECT("A"&ROW())&","&INDIRECT("A"&1+RO W())&","&INDIRECT("A"&2+ROW())&","&INDIRECT("A"&3+ ROW())&")","")

    This will do them in groups of 4.
    Last edited by kweaver; 2013-05-31 at 18:59.

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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
    Last edited by Maudibe; 2013-05-31 at 19:10.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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())&"')", "")
    Last edited by kweaver; 2013-05-31 at 19:39. Reason: fixed an error.

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Very cool, Thanks

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •