+ Reply to Thread
Results 1 to 4 of 4

INDEX MATCH MATCH multiple columns with same heading

  1. #1
    Registered User
    Join Date
    07-11-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    3

    INDEX MATCH MATCH multiple columns with same heading

    Hi,

    I am a new user and I have recently started a job in finance where I am getting the opportunity to become a power user of excel, unfortunately there aren't always people here to help me answer my questions. The error I am having today is a INDEX MATCH MATCH problem, when trying to put data into a more manipulatable form I need to run a index match match query. The problem I have is that there are repeats in the column header, so it only picks up the first instance , runs it to the bottom and then doesn't run it again. I have made up a simple spreadsheet that explains my problem, the original data is massive, hence the need for a formula. Using the example I have uploaded, I need a formula that will count all of the John columns, not just the first one.

    The Three column table is what I need to populate so it can be manipulated more easily using other functions, as you can see the total sum of the 15 column table is $335, and the sum of the cost column in the three column table is $253.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: INDEX MATCH MATCH multiple columns with same heading

    You need to create UNIQUE column headers. so JOHN PC, JOHN PSN, JOHN XB1, then you can use powerquery or pivot tables to UNpivot the data, or you can use your index match.

    Are you trying to Aggregate the data so PSN + Xb1 + PC = COST?
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    07-11-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    3
    Quote Originally Posted by mikeTRON View Post
    You need to create UNIQUE column headers. so JOHN PC, JOHN PSN, JOHN XB1, then you can use powerquery or pivot tables to UNpivot the data, or you can use your index match.

    Are you trying to Aggregate the data so PSN + Xb1 + PC = COST?
    Thanks for the comment!

    Changing the column header doesn't work given the size of the original data. The ultimate end result is to have a template where the qtrly data can be dumped into a sheet with a pivot table interrogating it so individual users can see what services they're subscribed to (and paying for) across different accounts.

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: INDEX MATCH MATCH multiple columns with same heading

    Can you not get the raw data in a format OTHER than this crosstabular report?

    How many columns of data do you have in your real dataset?


    I would likely process the data a bit to put platform into its own column, so each name stays unique. THEN use powerquery (or VBA for me) to unpivot the data completely.

    Otherwise your method COULD work but there is a potential for error if you forget to manually add a concatenation of Game Title and Name (and/or platform).
    http://chandoo.org/wp/2015/09/29/unp...h-power-query/


    In the attached I manually processed the data in three steps, first to create a column for platform, then to stack names in a column THEN a final pivot that you can do whatever you want. At the end of the day ALL you want is a STANDARDIZED DATASET where everything has a unique column and all the data dimensions (platform & name & Title) are in their own column.
    Attached Files Attached Files

+ 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. [SOLVED] Index Match across multiple Columns
    By whiZZfiZZ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2016, 01:05 PM
  2. [SOLVED] Using index,match with multiple columns
    By kriminaal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-10-2015, 01:07 PM
  3. Index Match multiple columns
    By Kdjonesmtb in forum Excel General
    Replies: 1
    Last Post: 11-29-2014, 11:45 AM
  4. Replies: 6
    Last Post: 03-17-2014, 08:10 PM
  5. [SOLVED] index match multiple columns
    By mma3824 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-08-2013, 04:02 PM
  6. [SOLVED] Index / Match across multiple columns
    By Chad B in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2013, 11:52 AM
  7. [SOLVED] Index/Match against multiple columns
    By paradox34690 in forum Excel General
    Replies: 8
    Last Post: 04-28-2012, 12:43 PM

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