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

 

[JenniferMurphy]

OK. After a little more fiddling around, I have several tables that seem to work and achieve most of my original objectives.

In the mini-sheet below, I have 3 derivative tables, one for tax items on the right, another identical one below, and one for gift items under that. If this is too crowded, either of these derivative tables can go onto a different sheet.

I don't know why the Gift? column in the Tax tables and the Tax? column in the Gift table contain 0s. I suppose I could eliminate both of those columns in the derivative tables, but I don't have time to test that right now.

I put a blank line between the vertical tables but had to put at least 1 character in one of the cells within the table columns or the table would eat it if expanded.

All in all, a very nice feature. Thanks for pointing it out to me.

Filter Function.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Complete List								Tax Items
4	Date	Account	Check#	For	Amount	Tax?	Gift?		Date	Account	Check#	For	Amount	Tax?	Gift?
5	01/23/21	Brokerage	1443	Checking	$100.00				02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes	0
6	02/03/21	Checking	2384	Girl Scout Cookies	$50.00				06/12/21	Brokerage	1444	NPR	$150.00	yes	0
7	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	0
8	03/22/21	Checking	2385	Johnny's Auto	$125.00				12/01/21	Checking	2388	PBS	$25.00	yEs	0
9	04/15/21	Checking	2386	Grandma	$200.00		Yes		12/02/21	SEP-IRA	73-357-6478	EMILY'S List	$150.00	Yes	0
10	06/12/21	Brokerage	1444	NPR	$150.00	yes			12/04/21	SEP-IRA	77-191-2157	Lincoln Project	$50.00	Yes	0
11	07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes
12	08/12/21	Checking	2387	Disneyland	$250.00
13	12/01/21	Checking	2388	PBS	$25.00	yEs
14	12/02/21	SEP-IRA	73-357-6478	EMILY'S List	$150.00	Yes
15	12/03/21	Checking	2389	Home Depot	$89.00
16	12/04/21	SEP-IRA	77-191-2157	Lincoln Project	$50.00	Yes
17	12/21/21	Checking	2390	Destroyed	$0.00
18				Tax Items	$950.00
19				Gift Items	$200.00
20	---
21	Tax Items
22	Date	Account	Check#	For	Amount	Tax?	Gift?
23	02/14/21	SEP-IRA	43-255-1276	Red Cross	$75.00	Yes	0
24	06/12/21	Brokerage	1444	NPR	$150.00	yes	0
25	07/09/21	SEP-IRA	67-003-9202	Molly's School	$500.00	Yes	0
26	12/01/21	Checking	2388	PBS	$25.00	yEs	0
27	12/02/21	SEP-IRA	73-357-6478	EMILY'S List	$150.00	Yes	0
28	12/04/21	SEP-IRA	77-191-2157	Lincoln Project	$50.00	Yes	0
29
30	---
31	Gift Items
32	Date	Account	Check#	For	Amount	Tax?	Gift?
33	04/15/21	Checking	2386	Grandma	$200.00	0	Yes
Sheet3

Cell Formulas
Range	Formula
J4:P4,B32:H32,B22:H22	J4	=Expenses[#Headers]
J5:P10,B23:H28	J5	=FILTER(Expenses,Expenses[Tax?]="Yes","")
F18	F18	=SUMIFS([Amount],[Tax?],"yes")
F19	F19	=SUMIFS(Expenses[Amount],Expenses[Gift?],"yes")
B33:H33	B33	=FILTER(Expenses,Expenses[Gift?]="Yes","")
Dynamic array formulas.

 

[JenniferMurphy]

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.

Thanks, I'll study that when I have more time.

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.

Good point. Probably also makes it run a bit faster.

It can be achieved with conditional formatting. See below.

Another feature for my todo list.

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.

I did look at that but missed the part about the filter function. I don't always get everything everyone says the first time. ?

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.

I tried that, but ended up putting is under the master table as I show in my post that I was working when yours came in. I could do both!

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.

Thanks. I think I have what I need right now. I am already a couple of weeks late getting taxes info together and my tax person is nagging me.