+ Reply to Thread
Results 1 to 6 of 6

Extracting data based on two helper column criteria

  1. #1
    Registered User
    Join Date
    07-18-2018
    Location
    Charlotte, NC
    MS-Off Ver
    Office 2016
    Posts
    3

    Extracting data based on two helper column criteria

    Good Afternoon,

    I have a spreadsheet with two main worksheets - one titled "2018" which houses mainly a data dump of text information, and a "Dashboard" which I would like to be user-friendly. I had created a data extract to pull data from the "2018" worksheet to the "Dashboard" via numerical helper columns and IFERROR(INDEX()) formulas (see below):

    =IFERROR(INDEX('2018'!$A$2:$M$18,'2018'!$I2,COLUMNS(B$6:$B6)),"")

    This worked well but I realized that I need multiple drop downs to be able to whittle down the results, and I cannot get my formula correct. I have built two sets of helper columns C-E (for the year) and G-I (for the company).

    What I am looking for -

    To have data only pull into the Dashboard (cells B7:E7) when the resulting number is found in both H3 columns (columns E and I), and then to have the corresponding data (Month, Product, Action, Citations) from those row numbers pulled to the Dashboard.

    I have attached an example file that functions with 1 criteria (Dummy1 drop down - located in cell B5), but does not work with both that and the year drop down (located in cell B4).

    I hope that makes sense, and thank you in advance for any assistance you might be able to provide.
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Extracting data based on two helper column criteria

    You've Excel 2016 so you can try PowerQuery aka Get&Transform
    Attached Files Attached Files
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: Extracting data based on two helper column criteria

    You have explained what you have done, but not really what you want to actually do?
    Are you trying to pull all 2017 data for Jan?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    07-18-2018
    Location
    Charlotte, NC
    MS-Off Ver
    Office 2016
    Posts
    3

    Re: Extracting data based on two helper column criteria

    So I am trying to create two drop down, one for date and one for company which, when selected, will only pull over the information which matches both criteria. For instance, there are three records for Dummy5 company, 2 in 2018 and 1 in 2019. I want to be able to bring over information for Dummy5 company for all years (so all three records), and then also confine that information to just 2018 and/or just 2019. Does that help at all?

  5. #5
    Registered User
    Join Date
    07-18-2018
    Location
    Charlotte, NC
    MS-Off Ver
    Office 2016
    Posts
    3

    Re: Extracting data based on two helper column criteria

    Thank you - I just got access to Excel 2016 so I'll have to play around with the PowerQuery features. I am not familiar SO I don't know if it will be able to accomplish what I am looking for, but I appreciate your suggestion.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: Extracting data based on two helper column criteria

    1st, make sure headings in both tables match.

    Then use this ARRAY formula in
    B7=IFERROR(INDEX('2018'!$A$2:$M$16,SMALL(IF(('2018'!$F$2:$F$16=$B$5)*('2018'!$B$2:$B$16=$B$4),ROW('2018'!$A$2:$A$16)),ROWS($A$1:A1)),MATCH(Dashboard!B$6,'2018'!$A$1:$M$1,0)),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Copy across, then copy down

+ 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. VLOOKUP with multiple criteria - no helper column if possible
    By TRAPLETT in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-26-2017, 10:46 AM
  2. sumif: criteria - helper column is the first occurence
    By kcees in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-22-2017, 04:26 PM
  3. Sort by helper column even if values in helper are alike
    By NoSleepForMe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2016, 04:14 AM
  4. Extracting names from a column to another sheet, based on criteria
    By sutherlandc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-06-2014, 03:59 PM
  5. [SOLVED] SUMIFS and Date Criteria (Month Year etc...) but without helper column?
    By JungleJme in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-16-2013, 07:21 AM
  6. Extracting data from column based on specific criteria and pasting into new column
    By vanvalkenburg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2012, 10:26 PM
  7. Extracting Data Based on Criteria
    By SJT in forum Excel General
    Replies: 3
    Last Post: 06-16-2007, 04:18 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