match 2 criteria and return multiple rows

[tryzhas]

Hello,

I'm trying to find out a solution (macro/formula) for matching 2 criteria and return multiple rows.

I have predefined Matrix table with all data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Text1[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Text2[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Text3[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Text4[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and for separate sheet I need to select which Text belongs to respective Header.

So if I select Header1 (column B) i'll get as result all rows, where is "x" in column B
like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Text1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Text2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Text4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and similar way for "header2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Text1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Text4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

etc.

i did it once a years ago, but it wasnt a nice/elegant solution (many formulas, macros...no option for skip rows without "x"...etc) + it was really hard to update data (not mention it was hard for me, not for common user)

Actually I'm trying to create something simple, which will be easy to manage (data + formulas/macro)

any ideas here guys/ladies?

 

[mrhstn]

Assuming the table is in Sheet4 Range A2:A5 you can try this...

Book1
	A	B	C	D	E
1		Header1	Header2	Header3	Header4
2	Text1	x	x
3	Text2	x		x	x
4	Text3		x		x
5	Text4	x
Sheet4

	A	B	C
Text1	Header2
Text3

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

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

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet 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] "]C1[/TH]
[TD="align: left"]=COUNTIF(INDEX(Sheet4!$B$2:$E$5,,MATCH(B1, Sheet4!$B$1:$E$1, 0)), "x")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[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] "]A1[/TH]
[TD="align: left"]{=IF(ROW()>$C$1, "", INDEX(Sheet4!$A$2:$A$5, SMALL(IF(INDEX(Sheet4!$B$2:$E$5,,MATCH($B$1, Sheet4!$B$1:$E$1, 0))="x", ROW($A$2:$A$5)), ROWS($A$1:A1))-1))}[/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]

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td] [/td][td] Header1[/td][td] Header2[/td][td] Header3[/td][td] Header4[/td][td] Header1[/td][/tr]
[tr][td]

2​

[/td][td] Text1[/td][td] x[/td][td] x[/td][td] [/td][td] [/td][td]

3​

[/td][/tr]
[tr][td]

3​

[/td][td] Text2[/td][td] x[/td][td] [/td][td] x[/td][td] x[/td][td]List[/td][/tr]
[tr][td]

4​

[/td][td] Text3[/td][td] [/td][td] x[/td][td] [/td][td] x[/td][td]Text1[/td][/tr]
[tr][td]

5​

[/td][td] Text4[/td][td] x[/td][td] [/td][td] [/td][td][/td][td]Text2[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Text4[/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H2 just enter:

=COUNTIFS(INDEX(B2:E5,0,MATCH(H1,B1:E1,0)),"x")

In H4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($H$4:H4)>$H$2,"",INDEX($A$2:$A$5,SMALL(IF(INDEX($B$2:$E$5,0,MATCH(H$1,$B$1:$E$1,0))="x",ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($H$4:H4))))