+ Reply to Thread
Results 1 to 4 of 4

rearrange database like dataset into table for large dataset better option INDEX MATCH

  1. #1
    Registered User
    Join Date
    10-11-2008
    Location
    Minnesota
    Posts
    5

    rearrange database like dataset into table for large dataset better option INDEX MATCH

    Hi: I have an excel workbook that takes a set of data which has three columns (user ID, Question ID, and Answer). I need to translate that dataset into a table so that I can perform some calculations and averaging on the answers. I have attached a file that shows what my raw data looks like and what I want the resulting table to look like. This dataset only contains 10 users and their responses and it works fine for small datasets but I have a dataset with 6000 sets of responses that I want to do the same analysis on and it it is VERY slow. I am currently using INDEX MATCH functions to rearrange the data. I was previously using SUMPRODUCT, which worked but from what I read is slower than INDEX MATCH. Does anyone know of yet a faster way to do this? I know this type of stuff should really be done in a database, but I don't know databases very well. My goal is to be able to process the data in less than 1 hour; right now with 6000 users, it takes more than a day. Any help is appreciated. I do know how to write macros but can't figure out quite how to write a macro to do this quickly.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    179

    Re: rearrange database like dataset into table for large dataset better option INDEX MATCH

    Hi ryanbirk,

    Have you tried using a Pivot table to achieve this? Select your dummydata table, go on Insert > Pivot Table. Put question_id in your column labels and user_id in your row labels with your sum of answers in the values section. Provided your answers are always numeric, this will work.

    Hope this helps.
    Last edited by Brumbot; 10-20-2017 at 12:42 AM.
    Did I help? Click *- add to my rep.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: rearrange database like dataset into table for large dataset better option INDEX MATCH

    The attached has 'DummyData' sorted by user_id and question_id both ascending. There is also a lookup table in H:J.

    Copy and paste all the user_ids to column H. Remove duplicates. Make certain they are still sorted ascending (important). Then this formula returns the lower and upper boundary rows of each user_id in the source data. In I2 filled down and across column J.

    Although technically an array formula it does not have to be committed with Ctl + Shift + Enter. A simple Enter suffices. Since the matches are approximate I would not anticipate this taking too long to calculate even the 12,000 anticipated returns. I could be wrong. It only has to calculate these once and takes a load off of the final formula. So please let me know.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Then in C8:BC17 this non-array formula. It also takes advantage of the speed of approximate matches. In fact my timer indicates it to be about 18-20x faster than the array formula in the original upload.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 10-20-2017 at 12:22 AM.
    Dave

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: rearrange database like dataset into table for large dataset better option INDEX MATCH

    welcome to the forum, ryanbirk. i would go with the PivotTable option and i have attached an eg of how it looks like in PT worksheet. i have also added another worksheet named SUMIFS. that would also work pretty quickly as it's not an array formula.

    by the way, it's been a while you've joined & you have probably upgraded your excel version since your file is an xlsx. could you update your profile of your MS Off Ver. it would be clearer & help members give you newer & more efficient formulas.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

+ 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. VBA code to replace INDEX/MATCH in large dataset
    By iantix in forum Excel Formulas & Functions
    Replies: 33
    Last Post: 07-25-2017, 11:23 PM
  2. Effectively rearrange large dataset
    By DanielZig in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2017, 08:03 AM
  3. Replies: 3
    Last Post: 01-23-2014, 10:13 AM
  4. [SOLVED] Vertical chart dataset needs to be converted into pivotable dataset
    By aspak84 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-17-2013, 06:06 PM
  5. Copying a dataset multiple times - and selecting titles for this dataset
    By alocke in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-07-2012, 10:15 PM
  6. graphing daily dataset with weekly dataset
    By [email protected] in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-27-2009, 09:39 AM
  7. [SOLVED] Converting A Quarterly Dataset to Weekly Dataset
    By Dan Thompson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2005, 05:30 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