+ Reply to Thread
Results 1 to 9 of 9

convert and compile data to new string

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    convert and compile data to new string

    I've attached a workbook with three tabs.

    The original data (shown on one tab) has three columns: Date, Value, and Day of Week

    I need to convert this data into a new format, where every date is converted to a style which is not available in the format settings (even the custom settings). For every single date in the original data, I need to divide it into 24 individual timestamps in the format shown in the Desired Outcome Tab.

    The only way I could create this desired outcome, was to treat the date like text, with a space at the beginning so that Excel wouldn't think that its a formula. Then concatate that date with a series of 24 times, as shown in the desired outcome.

    I've got 5 years of data to convert into this new scheme.

    Any ideas on how to do it with formulas? (ultimately, the revised data will be output to a csv file)

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: convert and compile data to new string

    In worksheet "desired Outcome", let's use column GHI as test area, G=TIME H=Value I=weekday

    G2=TEXT(INDEX('Original Data'!$A$2:$A$19,ROW(A24)/24),"yyyy-mm-dd")&"T"&TEXT(MOD(ROW(A24),24),"00")
    H2=INDEX('Original Data'!B$2:B$19,ROW(B24)/24) then copy to I2

    Select G2:I2, then copy down , all content in G:I should be as same as A:C but it doesn't use helper column

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: convert and compile data to new string

    In A2

    =TEXT(INDEX('Original Data'!$A$2:$A$1000,(ROWS($1:1)-1)/24+1),"yyyy-mm-dd") &"T" &TEXT(MOD(ROWS($1:1)-1,24),"00")

    in B2

    =INDEX('Original Data'!$B$2:$B$1000,(ROWS($1:1)-1)/24+1)

    in C2
    =INDEX('Original Data'!$C$2:$C$1000,(ROWS($1:1)-1)/24+1)


    I

  4. #4
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,171

    Re: convert and compile data to new string

    paste this code into a module then run: aMakeDesired
    (change the name if need)

    Please Login or Register  to view this content.
    Last edited by ranman256; 12-08-2017 at 03:46 PM.

  5. #5
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: convert and compile data to new string

    Thanks everyone! Since I had about 1,000 rows of days which would turn into 24,000 rows I used ranman's solution, but I see some others on this thread who've helped me before, and appreciate your help.

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: convert and compile data to new string

    The only thing you might want to concern yourself with is the speed of that code.

    Unnecessarily activating sheets and cells can slow down the code.

    I don't know how long the code would take to run on 24,000 rows but I can tell you it would be faster without all the unnecessary activating and selecting.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: convert and compile data to new string

    Modified VBA (In-memory array processing)

    Please Login or Register  to view this content.
    Last edited by JohnTopley; 12-09-2017 at 05:45 PM. Reason: Changed description to in-memory rather than in-core

  8. #8
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: convert and compile data to new string

    Thanks John and skywriter. On running the macro which ranman created, it took a few minutes to generate what turns out to be almost 50,000 rows of data, which I'm now using. But I'm interested in John's macro. What does in-core array processing do? Something to do with CPU cores?

    In any case, I appreciate your input, as always. I'm now about to post a new question which you might help with.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: convert and compile data to new string

    The macro will process the data faster because it reads the data into an array in memory rather than reading and processing each row (record) from the spreadsheet.
    Last edited by JohnTopley; 12-09-2017 at 05:44 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 06-29-2016, 01:27 PM
  2. Need to Convert 5 Row String of Data to 6 Rows in Excel 2007
    By alcharbonneau in forum Excel General
    Replies: 0
    Last Post: 07-26-2012, 10:01 AM
  3. [SOLVED] How to convert numeric data in an excel column into a string of comma values.
    By desgordon in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-04-2012, 12:10 PM
  4. Error using Clng function in vba to convert string to long data type
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-05-2011, 06:12 AM
  5. Replies: 11
    Last Post: 02-22-2011, 07:07 PM
  6. convert excel column data to comma seperated string
    By faraz in forum Excel General
    Replies: 4
    Last Post: 04-28-2010, 03:40 AM
  7. [SOLVED] How do I convert numeric data to string format (without VBA)?
    By LissaC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2006, 03:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1