Let's say I have data that looks like this:
I want to look up this data and create a table that would look like this:
So I am looking for a formula that will scan the data and the first time it sees a "1" for each president, it will bring in that date. The second time it sees a "1" it brings in the corresponding date, etc. Let's assume that the "president" column heading is cell A1. I am looking for a formula that I can write for each of the dates for each of the presidents that will correctly pull all of this in. Can anyone help?
| President | June 20, 2021 | June 21, 2021 | June 22, 2021 | June 23, 2021 | June 24, 2021 | June 25, 2021 |
| George Washington | 1 | 1 | 1 | |||
| John Adams | 1 | 1 | ||||
| Thomas Jefferson | 1 | 1 | ||||
| James Madison | 1 | 1 | 1 | 1 | 1 | 1 |
I want to look up this data and create a table that would look like this:
| President | Date 1 | Date 2 | Date 3 | Date 4 | Date 5 | Date 6 |
| George Washington | June 20, 2021 | June 22, 2021 | June 25, 2021 | |||
| John Adams | June 21, 2021 | June 22, 2021 | ||||
| Thomas Jefferson | June 23, 2021 | June 24, 2021 | ||||
| James Madison | June 20, 2021 | June 21, 2021 | June 22, 2021 | June 23, 2021 | June 24, 2021 | June 25, 2021 |
So I am looking for a formula that will scan the data and the first time it sees a "1" for each president, it will bring in that date. The second time it sees a "1" it brings in the corresponding date, etc. Let's assume that the "president" column heading is cell A1. I am looking for a formula that I can write for each of the dates for each of the presidents that will correctly pull all of this in. Can anyone help?
