CODEWITHSUNDEEP
excelif-statementexcel-formulapivot-tableworksheet-function
user3700539
user3700539

Reputation: 21

Match multiple conditions and avoid records that do not match

Source Data: SHEET 1 having three columns: ID, STATUS (Fail/Pass) and DATE.

Output should appear on Sheet 2 with records (only ID column) which are having Status as "Pass" and Date>today()-7, avoiding records that do not match.

Notes: 

1. I want to run it for all the records in SHEET 1  
   (number is dynamic, so want to run it on entire column for both Status and Date).  
2. Do not want to have any blank or false value  
   i.e. records that do not match should not show up at all on sheet 2.  
3. Avoid macros.

Upvotes: 1

Views: 41

Answers (1)

pnuts
pnuts

Reputation: 59475

You might, amongst very many other possibilities:

  1. Label a column, say D, in SHEET 1, say with df, and insert in it and copy down a formula such as: 

    =IF(C2>TODAY()-7,1,0)

then pivot sheet 1 A:D into Sheet 2 with STATUS and df for FILTERS and ID for ROWS, then select Pass for the STATUS filter and 1 for the df filter.

or

  1. Add and copy down a formula in Sheet 2 such as: 

    =IF(AND('SHEET 1'!B2="P",'SHEET 1'!C2>TODAY()-7),'SHEET 1'!A2,"")

and apply Advanced filter to it with Copy to another location and Unique records only checked.

Upvotes: 1

Related Questions