0

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

5
  • Unfortunately, no as it is not scaleable. In the workbooks I am working with, I have 6-9 dummy variables that I have to run fairly often with different data. I am currently copy/pasting, but if I could code it, I could save a ton of time. Commented Apr 15, 2014 at 21:50
  • Each time I run the report, the number of indicators in each columns change. Would a macro be able to only pull the ones that are equal to 1 when that number of indicators changes each time? Commented Apr 15, 2014 at 21:56
  • Have you tried pivot tables?
    – Madball73
    Commented Apr 16, 2014 at 12:11
  • What do you mean? Pivot it 90 degrees? Commented Apr 16, 2014 at 15:10
  • I'll take that as a no :) There is an excel function called pivot tables (use Excel help to learn how to use). It allows you to create filterable tables over other tables (and accumulate data, but this is not one of your needs). I'll add as answer.
    – Madball73
    Commented Apr 16, 2014 at 16:04

1 Answer 1

1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .