+ Reply to Thread
Results 1 to 5 of 5

Need to sort multiple sources (of same-type data, millions of rows in all) to new file

  1. #1
    Registered User
    Join Date
    06-14-2020
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    Office 2016, Win10-64
    Posts
    7

    Question Need to sort multiple sources (of same-type data, millions of rows in all) to new file

    Howdy!

    I have several dozen sheets, in a handful of files, each with 3 columns of the same data - reference, name, URL - no formatting, all text - but because there are millions of rows, they're split up.

    I want to find a way to sort all of it into a new file (and due to the length of the data, new sheets) in alphabetical order.
    At this time, that's all I need to do. I've been looking at PowerQuery/Pivot, but can't seem to find a way to do this; I think they're more useful for combining similar, related data sets, rather than multiple-of-the-same-datasets.

    i.e. I want to take 25 million similar rows, scattered over multiple sheets/files, and sort all into a new document, where sheet 1 = "Aaa...-Bac...", sheet 2 = "Bad...-Cro...", etc.
    It doesn't matter where the new sheet breaks are - they can be less than the full 1048576 rows, or every sheet can be the full 1048576 rows.
    I have to do manual work on the result, but do not need to create more rows.
    The top-most few thousand, and the bottom-most few dozen-thousand, records (i.e. the first, and the last, records) will need some editing, but the purpose of this sorting, is to get them to the top, and bottom, of the 25-million row list so I can do that, without working on the first/last records on one sheet, then the next, then the next...

    I can work with VBA if I need to, but I'm wondering if there is a way to do this more automatically before I start diving in.
    I've looked at KuTools and AbleBits add-ons and don't see anything there, either.

    Any help or pointers appreciated!
    Last edited by David_R_Turner; 06-14-2020 at 06:23 PM.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Need to sort multiple sources (of same-type data, millions of rows in all) to new file

    .
    https://www.masterdataanalysis.com/m...records-excel/

    https://chandoo.org/wp/more-than-million-rows-in-excel/

  3. #3
    Registered User
    Join Date
    06-14-2020
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    Office 2016, Win10-64
    Posts
    7

    Re: Need to sort multiple sources (of same-type data, millions of rows in all) to new file

    After a lot of repeated efforts to use the masterdatanalysis guide (some necessary steps are not shown there, perhaps because I'm using multiple tabs in multiple files as source data, rather than individual CSVs to append), I was finally able to understand how the data can be pulled in from my Excel files & combined.
    I'm halfway there, so thanks...
    I've discovered that I can do more with the interim data than I expected, so I actually was able to trim down my most immediate needed results from a sorted 24million rows to just 615 rows.
    In this test, all I want from 24million rows, is the resulting 615 rows in a new sheet, to do some more manual work on.

    However, there seems to be, and Googling seems to confirm it, no way to export the resulting data directly to Excel.
    It sounds like I need to use DAX and PowerBI (neither of which I know anything about), to push my resulting data to the cloud, and then extract it back into an Excel file or CSV, etc.

    Did Microsoft really provide a tool to do large data-set manipulation, expecting that the results would never need to be exported or used outside of the immediate software??
    Or am I missing something?

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Need to sort multiple sources (of same-type data, millions of rows in all) to new file

    .
    Someone with a higher understanding of Excel will need to contribute to this thread. I am sorry that I cannot answer your question.
    I do not have any experience working with large data sets as you are involved with.

    I would encourage you however to post any results you obtain in your efforts so others may benefit from the knowledge.

  5. #5
    Registered User
    Join Date
    06-14-2020
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    Office 2016, Win10-64
    Posts
    7

    Re: Need to sort multiple sources (of same-type data, millions of rows in all) to new file

    UPDATE: I realize now that I CAN export data to a table from within PowerQuery; but the issue remains at a 1,048,576 row maximum: PQ just says that's all I can do...

    So - I did get my 615 rows out; and for some of my work that's going to be adequate.
    But I still need to find a way to export the full data set - although I could do it it smaller chunks, a full solution would be nice.
    I'm looking into DAX now, as it's a free service... but it's still external to Excel, and I have to upload the multiple gigs of source data, and then download the results...

+ 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. Macro to copy server file into local folder, and change data sources to this new file
    By rickywangca in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2019, 11:18 AM
  2. [SOLVED] Sum data from multiple sources based on rows and column headers
    By rogclark in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2019, 11:32 AM
  3. How to sort csv file first before extracting rows of data from it
    By bezbid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-09-2015, 03:46 PM
  4. Sort and hide rows as I type
    By Haden7 in forum Excel General
    Replies: 1
    Last Post: 05-03-2014, 10:41 AM
  5. Non-Consecutive Rows sort data with multiple rows
    By Keiran1983 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2010, 11:15 PM
  6. Import and sort macro from multiple sources
    By Negative in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2009, 05:00 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