Help extracting selected rows from a table

[JenniferMurphy]

Suppose I have a table like the one on the left. Is there a way I can create another table like the one on the right containing just the rows with "Yes" in the Tax column? Thanks

Donations & RMD.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Date	Account	Check#	To	Amount	Tax?		Date	Account	Check#	To	Amount	Tax?
6	01/23/21	Brokerage	1443	Checking	$100.00	No		02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes
7	02/03/21	Checking	2384	Girl Scout Cookies	$50.00	No		06/12/21	Brokerage	1444	NPR	$150.00	Yes
8	02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes		07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
9	03/22/21	Checking	2385	Johnny's Auto	$125.00	No
10	04/15/21	Checking	2386	Grandma	$200.00	No
11	06/12/21	Brokerage	1444	NPR	$150.00	Yes
12	07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
13	08/12/21	Checking	2387	Disneyland	$250.00	No
MrExcel3

 

[kevin9999]

I see you have 365 - have you considered using the Filter() function? You can put the formula into a singe cell - I6 for the desired result.

Cars.xlsb
	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Date	Account	Check#	To	Amount	Tax?		Date	Account	Check#	To	Amount	Tax?
6	23/01/21	Brokerage	1443	Checking	100	No		14/02/21	SEP-IRA	43-255-1276	Red Cross	75	Yes
7	3/02/21	Checking	2384	Girl Scout Cookies	50	No		12/06/21	Brokerage	1444	NPR	150	Yes
8	14/02/21	SEP-IRA	43-255-1276	Red Cross	75	Yes		9/07/21	SEP-IRA	67-003-9202	Molly's School	500	Yes
9	22/03/21	Checking	2385	Johnny's Auto	125	No
10	15/04/21	Checking	2386	Grandma	200	No
11	12/06/21	Brokerage	1444	NPR	150	Yes
12	9/07/21	SEP-IRA	67-003-9202	Molly's School	500	Yes
13	12/08/21	Checking	2387	Disneyland	250	No
Sheet4

Cell Formulas
Range	Formula
I6:N8	I6	=FILTER(B6:G13,G6:G13="Yes","")
Dynamic array formulas.

 

[JenniferMurphy]

I see you have 365 - have you considered using the Filter() function? You can put the formula into a singe cell - I6 for the desired result.

Wow! That a new one for me. I'll check it out. It looks like it will do the job. Thanks.

Can I refer to the table using the table name rather than B6:G13 so that it will update if I add to the table? I'm going to go test that now.

Thanks

 

[Peter_SSs]

Can I refer to the table using the table name rather than B6:G13 so that it will update if I add to the table? I'm going to go test that now.

It probably will update anyway, but makes more sense to directly tie it to the original table name/structure

=FILTER(Table1,Table1[Tax?]="Yes","")

 

[JenniferMurphy]

It probably will update anyway, but makes more sense to directly tie it to the original table name/structure

=FILTER(Table1,Table1[Tax?]="Yes","")

The first time I tried it, I typed "=filter(" and then dragged the cursor over the entire table. That got me this formula =FILTER(Table9[#All],Table9[Tax?]="Yes",""), which gets a #VALUE! error. Then I noticed that your formula has just "Table1" (Table9 in my sheet), so I tried that and got this:

Donations & RMD.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Date	Account	Check#	To	Amount	Tax?		44241	SEP-IRA	43-255-1276	Red Cross	75	Yes
6	01/23/21	Brokerage	1443	Checking	$100.00	No		44359	Brokerage	1444	NPR	150	Yes
7	02/03/21	Checking	2384	Girl Scout Cookies	$50.00	No		44386	SEP-IRA	67-003-9202	Molly's School	500	Yes
8	02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes
9	03/22/21	Checking	2385	Johnny's Auto	$125.00	No
10	04/15/21	Checking	2386	Grandma	$200.00	No
11	06/12/21	Brokerage	1444	NPR	$150.00	Yes
12	07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
13	08/12/21	Checking	2387	Disneyland	$250.00	No
MrExcel3

Cell Formulas
Range	Formula
I5:N7	I5	=FILTER(Table9,Table9[Tax?]="Yes","")
Dynamic array formulas.

It's missing the header row and all of the formatting. How did you get it to look the way it does when you did it?

 

[kevin9999]

The first time I tried it, I typed "=filter(" and then dragged the cursor over the entire table. That got me this formula =FILTER(Table9[#All],Table9[Tax?]="Yes",""), which gets a #VALUE! error. Then I noticed that your formula has just "Table1" (Table9 in my sheet), so I tried that and got this:

Donations & RMD.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Date	Account	Check#	To	Amount	Tax?		44241	SEP-IRA	43-255-1276	Red Cross	75	Yes
6	01/23/21	Brokerage	1443	Checking	$100.00	No		44359	Brokerage	1444	NPR	150	Yes
7	02/03/21	Checking	2384	Girl Scout Cookies	$50.00	No		44386	SEP-IRA	67-003-9202	Molly's School	500	Yes
8	02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes
9	03/22/21	Checking	2385	Johnny's Auto	$125.00	No
10	04/15/21	Checking	2386	Grandma	$200.00	No
11	06/12/21	Brokerage	1444	NPR	$150.00	Yes
12	07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
13	08/12/21	Checking	2387	Disneyland	$250.00	No
MrExcel3

Cell Formulas
Range	Formula
I5:N7	I5	=FILTER(Table9,Table9[Tax?]="Yes","")
Dynamic array formulas.

It's missing the header row and all of the formatting. How did you get it to look the way it does when you did it?

I simply copied the header row first.

 

[JenniferMurphy]

I simply copied the header row first.

Gotcha. This is the best I could come up with. I actually set the header to be equal. It now updates, too. Notice that I changed "To" to "For" in E5 and L5 changed, too. ?

And I copied the formatting of one of the uncolored rows. This mostly works. I kinda miss the alternating row colors, but I tried converting it to a table and it did not like that at all. It will dynamically expand if I add a matching row to the table, but that new row will not be formatted the same:

Donations & RMD.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Date	Account	Check#	For	Amount	Tax?		Date	Account	Check#	For	Amount	Tax?
6	01/23/21	Brokerage	1443	Checking	$100.00	No		02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes
7	02/03/21	Checking	2384	Girl Scout Cookies	$50.00	No		06/12/21	Brokerage	1444	NPR	$150.00	Yes
8	02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes		07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
9	03/22/21	Checking	2385	Johnny's Auto	$125.00	No		44531	Checking	2388	PBS	25	Yes
10	04/15/21	Checking	2386	Grandma	$200.00	No
11	06/12/21	Brokerage	1444	NPR	$150.00	Yes
12	07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
13	08/12/21	Checking	2387	Disneyland	$250.00	No
14	12/01/21	Checking	2388	PBS	$25.00	Yes
MrExcel3

Cell Formulas
Range	Formula
I5:N5	I5	=Table9[#Headers]
I6:N9	I6	=FILTER(Table9,Table9[Tax?]="Yes","")
Dynamic array formulas.

I suppose I could pre-format a bunch of the next rows...

Anyway, this is pretty good. Thanks so much! ??

 

[kevin9999]

Gotcha. This is the best I could come up with. I actually set the header to be equal. It now updates, too. Notice that I changed "To" to "For" in E5 and L5 changed, too. ?

And I copied the formatting of one of the uncolored rows. This mostly works. I kinda miss the alternating row colors, but I tried converting it to a table and it did not like that at all. It will dynamically expand if I add a matching row to the table, but that new row will not be formatted the same:

Donations & RMD.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Date	Account	Check#	For	Amount	Tax?		Date	Account	Check#	For	Amount	Tax?
6	01/23/21	Brokerage	1443	Checking	$100.00	No		02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes
7	02/03/21	Checking	2384	Girl Scout Cookies	$50.00	No		06/12/21	Brokerage	1444	NPR	$150.00	Yes
8	02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes		07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
9	03/22/21	Checking	2385	Johnny's Auto	$125.00	No		44531	Checking	2388	PBS	25	Yes
10	04/15/21	Checking	2386	Grandma	$200.00	No
11	06/12/21	Brokerage	1444	NPR	$150.00	Yes
12	07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
13	08/12/21	Checking	2387	Disneyland	$250.00	No
14	12/01/21	Checking	2388	PBS	$25.00	Yes
MrExcel3

Cell Formulas
Range	Formula
I5:N5	I5	=Table9[#Headers]
I6:N9	I6	=FILTER(Table9,Table9[Tax?]="Yes","")
Dynamic array formulas.

I suppose I could pre-format a bunch of the next rows...

Anyway, this is pretty good. Thanks so much! ??

Glad we could help, and thanks for the feedback
As a point of interest, if you formatted columns I-N with the appropriate formatting down sufficient rows to cover your likely queries, the formatting will be retained as you add rows to the table.

 

[JenniferMurphy]

Glad we could help, and thanks for the feedback
As a point of interest, if you formatted columns I-N with the appropriate formatting down sufficient rows to cover your likely queries, the formatting will be retained as you add rows to the table.

I also discovered another little M$FT goodie. I added a SUM expression under the Amount column (Col M). When I added a row to the table that needed to be added to the side table, the whole thing turned into a #SPILL! error. (sigh)

 

[Peter_SSs]

I actually set the header to be equal. It now updates, too.

I would probably stick with that for the headers and have the separate formula for the other table data. However, it is possible to do headers and table data all in one if you want. See below.

I suppose I could pre-format a bunch of the next rows...

In terms of Date/Number etc formatting, unless there is a specific reason not to, I would format the whole column. If you format sections of a column, Excel needs to remember individual formatting for the various cells in the column. If you format the whole column, it only needs to remember one for the column. This can help reduce file bloat.

I kinda miss the alternating row colors

It can be achieved with conditional formatting. See below.

I also discovered another little M$FT goodie. I added a SUM expression under the Amount column (Col M). When I added a row to the table that needed to be added to the side table, the whole thing turned into a #SPILL! error. (sigh)

I am surprised that this was "discovery" to you, along with this whole sub-table issue, since much of it was discussed in this other thread of yours.
Consider putting the sum at the top - it is more likely to be readily visible there anyway if your table gets at all big as it would be common in that situation to freeze some rows at the top of the sheet. Two options for that sum in my mini-sheet.

Anyway, here is an option that covers most of what you are wanting as I understand it.
J5:O5 I formatted blue manually (by format-painting from B5:G5). This formatting would not automatically expand to P5 if the original table gets a new column added to it in col H.

The Conditional Formatting shown needs to be applied as far down as you think you will ever need.

JenniferMurphy.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3													$725.00
4													$725.00
5	Date	Account	Check#	To	Amount	Tax?			Date	Account	Check#	To	Amount	Tax?
6	23/01/21	Brokerage	1443	Checking	$100.00	No			14/02/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes
7	3/02/21	Checking	2384	Girl Scout Cookies	$50.00	No			12/06/21	Brokerage	1444	NPR	$150.00	Yes
8	14/02/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes			9/07/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
9	22/03/21	Checking	2385	Johnny's Auto	$125.00	No
10	15/04/21	Checking	2386	Grandma	$200.00	No
11	12/06/21	Brokerage	1444	NPR	$150.00	Yes
12	9/07/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
13	12/08/21	Checking	2387	Disneyland	$250.00	No
Sheet1

Cell Formulas
Range	Formula
N3	N3	=SUMIF(Table1[Tax?],"Yes",Table1[Amount])
N4	N4	=SUM(INDEX(J5#,0,COLUMNS(J4:N4)))
J5:O8	J5	=FILTER(Table1[#All],(Table1[[#All],[Tax?]]="Yes")+(Table1[[#All],[Tax?]]=Table1[[#Headers],[Tax?]]),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J6:O13	Expression	=AND(ROWS($J$5#)>=ROW()-ROW($J$5)+1,MOD(ROW()-ROW(J$5),2))	text	NO