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.

[TABLE="width: 907"]
<tbody>[TR]
[TD]Books
[/TD]
[TD]Data
[/TD]
[TD]Facts
[/TD]
[TD]TSS
[/TD]
[TD]RML
[/TD]
[TD]LOO
[/TD]
[TD]BDG
[/TD]
[TD]TRR
[/TD]
[TD]SMS
[/TD]
[TD]APP
[/TD]
[/TR]
[TR]
[TD]Transfer
[/TD]
[TD]PlaceHolder
[/TD]
[TD]PlaceHolder
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Regulation
[/TD]
[TD]PlaceHolder
[/TD]
[TD]PlaceHolder
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Control
[/TD]
[TD]PlaceHolder
[/TD]
[TD]PlaceHolder
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]PlaceHolder
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]


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)


[TABLE="width: 250"]
<tbody>[TR]
[TD]Books[/TD]
[TD]Codes [/TD]
[/TR]
[TR]
[TD]Transfer[/TD]
[TD]TSS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TRR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SMS[/TD]
[/TR]
[TR]
[TD]Control[/TD]
[TD]RML[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]LOO[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BDG[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TRR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]APP[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]RML[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]LOO[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BDG[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]


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
Books	Codes
Transfer	TSS
Transfer	TRR
Transfer	SMS
Control	RML
Control	LOO
Control	BDG
Control	TRR
Control	APP
-	RML
-	LOO
-	BDG

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

</tbody>

Books​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]{=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"))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=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))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.