EXCEL: VLookupwith dummy variables

by

I have data that has dates, returns, and two columns of dummy variables. I would like to know how to use a formula to only show data when the dummy variable indicates a hit.
My data looks like this:

    date      Return    A   B
    1/1/2014    0.18    0   0
    1/2/2014    0.97    0   1
    1/3/2014    0.73    0   0
    1/4/2014    0.85    1   0
    1/5/2014    0.19    1   0
    1/6/2014    0.80    0   0
    1/7/2014    0.50    0   0
    1/8/2014    0.27    0   0
    1/9/2014    0.94    0   0
    1/10/2014   0.40    0   0
    1/11/2014   0.56    0   0
    1/12/2014   0.40    1   0
    1/13/2014   0.40    1   0
    1/14/2014   0.43    1   1
    1/15/2014   0.44    0   1
    1/16/2014   0.90    0   0
    1/17/2014   0.35    0   0

And I would like it to output two tables:

    DUMMY A TABLE           
    date    Return  A   B
    1/4/2014    0.85    1   0
    1/5/2014    0.19    1   0
    1/12/2014   0.40    1   0
    1/13/2014   0.40    1   0
    1/14/2014   0.43    1   1

and

    DUMMY B TABLE           
    date    Return  A   B
    1/2/2014    0.97    0   1
    1/14/2014   0.43    1   1
    1/15/2014   0.44    0   1

I have figured how to do it using sort copy / paste, but I would like a formula to make it a more efficient template

Answer

Use 2 pivot tables in a table layout.

Set Date and Return as “Row Labels”, and Report Filter on A (on one resultant pivot) and B (on the other pivot). You don’t need any “Values”

This should result in (for Table A) – (note I used random returns):
enter image description here

The mechanics of creating and formatting one differs slightly in different versions, but you just select your data table and Insert->Pivot Table.

Attribution
Source : Link , Question Author : user3537689 , Answer Author : Madball73

Leave a Comment