Formula Question

[ExcelGon]

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

<tbody>
</tbody>

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 with 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

<tbody>
</tbody><colgroup><col><col></colgroup>

Sorry If I'm not explaining this well. I hope someone can help!

Thanks!

 

[James006]

Hello,

In sheet 2 cell B2 you can test following array formula

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$D$2:$J$2,"Yes"),INDEX(Data!$D$1:$J$1,SMALL(IF(Data!$D$2:$J$2="Yes",COLUMN(Data!$D$2:$J$2)-COLUMN(Data!$D$2)+1),ROWS(B$2:B2))),"")

Hope this will help

 

[ExcelGon]

Hello,

In sheet 2 cell B2 you can test following array formula

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$D$2:$J$2,"Yes"),INDEX(Data!$D$1:$J$1,SMALL(IF(Data!$D$2:$J$2="Yes",COLUMN(Data!$D$2:$J$2)-COLUMN(Data!$D$2)+1),ROWS(B$2:B2))),"")

Hope this will help

Hello,

This didn't work. Not sure what happened.

 

[Eric W]

Welcome to the forum!

With your Master Data tab defined as you show above, try this on your Books tab:

	A	B
1	Books	Codes
2	Transfer	TSS
3	Transfer	TRR
4	Transfer	SMS
5	Control	RML
6	Control	LOO
7	Control	BDG
8	Control	TRR
9	Control	APP
10	-	RML
11	-	LOO
12	-	BDG
13

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Books​

Array Formulas Cell Formula A2 {=IF(ROWS($A$2:$A2)>COUNTIF('Master Data'!$D$2:$J$10,"Yes"),"",INDEX('Master Data'!$A$2:$A$10,MATCH(ROWS($A$2:$A2)-1,COUNTIF(OFFSET('Master Data'!$D$1:$J$1,0,0,ROW('Master Data'!$A$2:$A$10)-ROW('Master Data'!$A$2)+1),"Yes"))))} B2 {=IF(A2="","",INDEX('Master Data'!$D$1:$J$1,SMALL(IF(OFFSET('Master Data'!$D$2:$J$2,MATCH(A2,'Master Data'!$A$2:$A$10,0)-1,0)="Yes",COLUMN('Master Data'!$D$1:$J$1)-COLUMN('Master Data'!$D$1)+1),COUNTIF($A$2:$A2,$A2))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Both the formulas are array formulas, make sure you use Control+Shift+Enter when entering them in the formula bar. Change the ranges as needed. Then copy the formulas and drag down as far as needed.

Note that the book name is listed on every row. If you want to only show the book name once, you can use Conditional Formatting. Select column A, click Conditional Formatting > New Rule > Use a formula > and enter:

=a1=offset(a1,-1,0)

Then click Format > Number > Custom > and in the Type: box, put ;;;

Click OK. Let us know how this works for you.