+ Reply to Thread
Results 1 to 11 of 11

Extracting data based on two variables

  1. #1
    Registered User
    Join Date
    02-21-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Post Extracting data based on two variables

    Hello all

    I am analysing data (see the attached worksheet) that essentially contains two pieces of information:

    Sheet 1: shows whether certain reports are relevant for certain companies. A ‘tick’ means that the report is relevant. A ‘dash’ means that the report is not relevant.

    Sheet 2: shows the last filing date for each company.

    I want to sort the data by the following:

    If the report is applicable to the company (represented by a ‘tick’ in Sheet 1), and the date of the report is before the filing date of the company, then I want to extract the data.

    I am looking for a solution whereby I can sort by the company and obtain the reports that fit these criteria. For instance, like how you would sort data by using data validation or a pivot table.

    Can this be done?

    Ideally I would like a solution that would output the results in a clean way (rather than having to copy and paste down formulas like you would with an index/match array function).

    Any help would be greatly appreciated.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Extracting data based on two variables

    Data validation will not sort and (admittedly not being very knowledgeable on Pivot Tables) I doubt if that is a solution.

    You either use the INDEX/MATCH approach or VBA.

    And how do you want the output to look: Report IDs across a row for a given company?

    So for Company 1, there are 7 reports with Dates before the Last Filing date.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Extracting data based on two variables

    See attached:

    In Sheet2:

    C2

    =IFERROR(INDEX(Sheet1!$B$3:$B$100,SMALL(IF((INDEX(Sheet1!$C$3:$Q$100,,MATCH($A2,Sheet1!$C$2:$Q$2,0))=1)*(Sheet1!$A$3:$A$100 < Sheet2!$B2),ROW(Sheet1!$B$3:$B$100)-ROW($B$3)+1,""),COLUMNS($C:C))),"")

    Enter with Ctrl+Shift+Enter

    Copy across and down.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-21-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Re: Extracting data based on two variables

    Thanks for your response John.

    I am ideally looking to sort the data in a new worksheet (leaving these two worksheets as 'data' tabs).

    On this new worksheet I would like to sort by Company, and I would like the output to be every report that is relevant for that company (a 'tick' on Sheet 1) and that was also issued before the last filing date (hence also sorting by date on Sheet 2).

    It seems logical in my head - if it's a tick in Sheet 1 and if date of report< last filing date, then return the report(s).

    Obviously it's much more difficult to execute in reality! Hope this makes sense.

    Thanks in advance.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Extracting data based on two variables

    Does the Sheet2 not give you that? It IS every relevant report for a company.

    The maximum work required is to Sort by company name.

    By default the reports are in date sequence - Latest to earliest - (not shown) as the data in your Sheet1 is currently in descending date order.

    If you want a separate sheet, copy Sheet2 columns A and B to Sheet3, sort by company, and then put the formula into column C of Sheet3.
    Last edited by JohnTopley; 02-21-2017 at 01:53 PM.

  6. #6
    Registered User
    Join Date
    02-21-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Re: Extracting data based on two variables

    Ok yes John I see what you mean now, thank you.

    I have one more step to complete (hope you can help me with this one too!)

    Is there a way to also sort the data that contains a tick in Sheet 1 and where the date of the report>last filing date? I would also want these reports to be automatically formatted differently if possible (in a different colour or something).

    Would you know how to do that?

    Apologies for being a complete Excel rookie!

    Thanks in advance.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Extracting data based on two variables

    You cannot sort the data but using Conditional Formatting you can highlight reports where the date of the report>last filing date,

    CF formula ..

    AND($A3>INDEX(Sheet2!$B$2:$B$50,MATCH(C$2,Sheet2!$A$2:$A$50,0)),C3=1)
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-21-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Re: Extracting data based on two variables

    Brilliant John thanks a lot. I've gone through it and understand it now.

    I'm not sure whether this question is suitable for this thread or whether I should stick it in the macro thread, but I'll ask it anyway:

    Is there a way to add a button that removes all report(s) in Sheet 1 if the report has 'dashes' for all the companies and the date of the report is > 1 year from todays date?

    Again, thanks in advance. You have been of such great help!

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Extracting data based on two variables

    Try this is column R of Sheet1


    =IF(AND(COUNTIF(C4:Q4,1)=0,A4>EDATE(TODAY(),12)),"Y","")

    should get "Y" is all 0 and A > today()+12 months

  10. #10
    Registered User
    Join Date
    02-21-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Re: Extracting data based on two variables

    Thanks John

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Extracting data based on two variables

    Sorry ... was in bit of a rush with previous reply: do you want to remove those flagged as"Y"? Alternatively you can filter on the blanks.,

+ 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. Extracting Data based on Folder
    By cadetSean in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2015, 12:19 PM
  2. [SOLVED] Extracting Data Based on Values, using <= or >=
    By Raabi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2014, 04:18 AM
  3. Extracting data based on cell value
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-10-2014, 08:51 AM
  4. [SOLVED] Extracting Data Based on Yes or No answer
    By johnkelly11 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2013, 11:17 AM
  5. Extracting Data Based on Yes or No Condition
    By leowyatt in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-19-2013, 09:02 AM
  6. Extracting Data from ERP Database based on 2 variables in Spreadsheet...cont'd
    By NBVC in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 01-11-2010, 01:09 PM
  7. Extracting data based on dates
    By jodapo in forum Excel General
    Replies: 4
    Last Post: 08-07-2009, 09:10 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