Forum Discussion
I need help with a formula
Hi,
I need help with a formula please! I have an excel sheet with multiple tabs. So on sheet 1 I have all master data and on sheet 2, I break out a section of the data. In the example below, we are looking at the master data and on sheet two, I need to pull all books that have at least one "yes" over and it needs to show the corresponding code. For example, I'm looking for all codes (TSS, RML, LOO, BDG, TRR, SMS, APP) that the books apply to.
Master Data - "Books" is Column A.
| Books | Data | Facts | TSS | RML | LOO | BDG | TRR | SMS | APP |
| Transfer | PlaceHolder | PlaceHolder | Yes | No | No | No | Yes | Yes | No |
| Regulation | PlaceHolder | PlaceHolder | No | No | No | No | No | No | No |
| Control | PlaceHolder | PlaceHolder | No | Yes | Yes | Yes | Yes | No | Yes |
| - | - | PlaceHolder | No | Yes | Yes | Yes | No | No | No |
Here is an example of what I need the formula to do on another tab of the workbook. The formula needs to be such that whenever the master information is updated, this other tab witl automatically update. So below, each Book that contains a "yes" on the master sheet populates, showing which of the codes had a "yes". (CODES are columns D-J above)
| Books | Codes |
| Transfer | TSS |
| TRR | |
| SMS | |
| Control | RML |
| LOO | |
| BDG | |
| TRR | |
| APP | |
| - | RML |
| LOO | |
| BDG |
2 Replies
Hy Rylane,
Perhaps the easiest way is to use Power Query (aka Get&Transform) - query your master table, unpivot columns with codes, filter on Yes, make bit more cosmetic and load the result into another sheet.
