+ Reply to Thread
Results 1 to 7 of 7

Bulk Merge Text through Arrays to reduce processing time

  1. #1
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Bulk Merge Text through Arrays to reduce processing time

    Hello Seniors,

    Attached is the Sample Workbook, starting data from ROW 7 till 26407, - truncated due to 1 MB file size restrictions. Practically there are much higher ROWS 225897 to over a half Million ) in Worksheets - Sheet1

    Data starts from Column B through P
    Column C having Text Data
    Column D having DATE data
    Column E having Numeric data
    Column F having Text Data
    Column P having DATE data

    Column A is where I merge text like

    Please Login or Register  to view this content.


    Following is the macro code to fill/populate rows in column A

    Please Login or Register  to view this content.
    This takes about 4 to 8 seconds presently depending on ROWS data.

    Seek Guidance for two aspects

    1) Can I reduce the processing time further through transferring data to Arrays and Looping and move/paste data to rows in Column A, as is done presently but through formula filling.

    2) I run Timer to measure the time elapsed, but some how the outcome comes in twelve hour format, such that for 5 seconds spent, i get message - 12:00:05 AM. How Can i display this result in simple 00:00:00 format i.e. HH:MM:SS without AM PM ?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Bulk Merge Text through Arrays to reduce processing time

    Using Power Query on your example it is almost instantaneously

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Bulk Merge Text through Arrays to reduce processing time

    See if this runs faster
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: Bulk Merge Text through Arrays to reduce processing time

    Quote Originally Posted by jindon View Post
    See if this runs faster
    Please Login or Register  to view this content.
    It was a Bomb, took just 0.73 seconds on ROWS 272892+ compared to over 6 seconds earlier. Thanks a lot. I just added towards end of macro a line to deactivate range copy
    Please Login or Register  to view this content.

    Quote Originally Posted by alansidman View Post
    Using Power Query on your example it is almost instantaneously

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Thanks, I will certainly explore it[Power BI]. I thought, due to my old version of Excel, it is not useable and ignored for years.

  5. #5
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Bulk Merge Text through Arrays to reduce processing time

    Another option, Evaluate:
    Please Login or Register  to view this content.
    If things don't change they stay the same

  6. #6
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: Bulk Merge Text through Arrays to reduce processing time

    Quote Originally Posted by CheeseSandwich View Post
    Another option, Evaluate:
    Please Login or Register  to view this content.
    Certainly, I will check.

    Regards

  7. #7
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: Bulk Merge Text through Arrays to reduce processing time

    Quote Originally Posted by CheeseSandwich View Post
    Another option, Evaluate:
    Please Login or Register  to view this content.
    good enough, 1.37 seconds for rows about 223300+

+ 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. SUMIF Alternative to reduce processing time
    By seasider89 in forum Excel General
    Replies: 5
    Last Post: 08-27-2015, 05:50 AM
  2. Reduce time in processing multiple excel sheets
    By rexer231 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2014, 06:00 AM
  3. Reduce processing time for processing multiple excel sheets
    By rexer231 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2014, 09:25 AM
  4. Reduce processing time for saving excel sheets located in a folder
    By rexer231 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2014, 05:28 AM
  5. Excel - bulk processing disparate spread sheets with common data into 1 table or SS
    By rblackcat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2013, 08:41 PM
  6. Reduce processing load in Excel
    By Lance Gray in forum Excel General
    Replies: 0
    Last Post: 12-29-2005, 01:30 PM
  7. [SOLVED] Bulk processing
    By PO in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2005, 09:06 AM

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