Index Match formula pulling from a longer list with blank row spaces or unwanted data into a shorter list without row spaces

[MikeWnbExclWiz]

Hi everyone,

Long-time lurker, first-time poster. I'm trying to pull the information highlighted in green from the below columns (columns J-L) into separate consolidated columns that only return the information in green and excludes blank or un-highlighted rows. The highlighting is a conditional formatting that is based on the "Yes/No" data validations in column H. So I'd like the formula to be based on those "Yes/No" user selections.

And to add an additional level of complexity, it would be great if that new consolidated list could be effectively sorted by the data validation in column I, which is the "Controllable/Non-Controllable" selection (i.e. have "Controllable" listed first and then "Non-Controllable" next). Or it might be easier if they're displayed in two separate 3-columned lists (first 3 columns for Controllable and next 3 columns for Non-Controllable)

Also the information in columns J-L change between properties, so they won't always be in the same exact position (i.e. one property may have a lot more expense line items under "Advertising & Promotion"), so the formula needs to be dynamic.

I'm trying as a hard as humanly possible not to use macros/VBA.

I appreciate the communities help! I've been banging my head on my desk for a few days now...

Thanks,
Mike

 

[Eric W]

Welcome to the MrExcel forum!

Maybe something like this?

Book1
	H	I	J	K	L	M	N	O	P
1
2
3
4
5
6	Shared Expense (Y/N)	Controllable/Non-Controllable
7
8
9				OPERATING EXPENSE
10				Salary & Related-Residential			Number	Desc	Amount
11	Yes	Non-Controllable	1	a	1.00		8	h	8
12	Yes	Non-Controllable	2	b	2.00		9	i	9
13	No	Non-Controllable	3	c	3.00		10	j	10
14	Yes	Non-Controllable	4	d	4.00		11	k	11
15	Yes	Non-Controllable	5	e	5.00		13	m	13
16	Yes	Non-Controllable	6	f	6.00		14	n	14
17	Yes	Non-Controllable	7	g	7.00
18				Total			1	a	1
19							2	b	2
20							4	d	4
21				Advertising & Promotion			5	e	5
22	Yes	Controllable	8	h	8.00		6	f	6
23	Yes	Controllable	9	i	9.00		7	g	7
24	Yes	Controllable	10	j	10.00		15	o	15
25	Yes	Controllable	11	k	11.00		17	q	17
26	No	Controllable	12	l	12.00		18	r	18
27	Yes	Controllable	13	m	13.00		19	s	19
28	Yes	Controllable	14	n	14.00		20	t	20
29				Total			21	u	21
30							22	v	22
31
32				Utilities
33	Yes	Non-Controllable	15	o	15.00
34	No	Non-Controllable	16	p	16.00
35	Yes	Non-Controllable	17	q	17.00
36	Yes	Non-Controllable	18	r	18.00
37	Yes	Non-Controllable	19	s	19.00
38	Yes	Non-Controllable	20	t	20.00
39	Yes	Non-Controllable	21	u	21.00
40	Yes	Non-Controllable	22	v	22.00
41	Yes	Non-Controllable	23	w	23.00
42				Total
Sheet6

Cell Formulas
Range	Formula
N11:P30	N11	=LET(a,COUNTIFS(H11:H100,"Yes",I11:I100,"Controllable"),t,SORT(FILTER(H11:L100,H11:H100="Yes"),2,1),s,SEQUENCE(ROWS(t)),IFS(s<=a,INDEX(t,s,{3,4,5}),s=a+1,{"","",""},1,INDEX(t,s-1,{3,4,5})))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J11:L41	Expression	=$H11="Yes"	text	NO

I had it insert a blank row between the "Controllable" and "Non-Controllable" sections. But that can be removed, or we can build 2 tables side by side like you suggested.

 

[MikeWnbExclWiz]

Thanks for digging in, Eric. A couple of questions, as I'm having a little trouble following the formula - why have you used "t" and "s" in your formula? I'm assuming "a" is to reference the beginning/top of the array, but not quite sure where "t" and "s" come in.

Thanks in advance

 

[Eric W]

Let me walk you through it a bit. I tweaked the formula a bit to make it more understandable (hopefully!).

Book1
	H	I	J	K	L	M	N	O	P
1
2
3
4
5
6	Shared Expense (Y/N)	Controllable/Non-Controllable
7
8
9				OPERATING EXPENSE
10				Salary & Related-Residential			Number	Desc	Amount
11	Yes	Non-Controllable	1	a	1.00		8	h	8
12	Yes	Non-Controllable	2	b	2.00		9	i	9
13	No	Non-Controllable	3	c	3.00		10	j	10
14	Yes	Non-Controllable	4	d	4.00		11	k	11
15	Yes	Non-Controllable	5	e	5.00		13	m	13
16	Yes	Non-Controllable	6	f	6.00		14	n	14
17	Yes	Non-Controllable	7	g	7.00
18				Total			1	a	1
19							2	b	2
20							4	d	4
21				Advertising & Promotion			5	e	5
22	Yes	Controllable	8	h	8.00		6	f	6
23	Yes	Controllable	9	i	9.00		7	g	7
24	Yes	Controllable	10	j	10.00		15	o	15
25	Yes	Controllable	11	k	11.00		17	q	17
26	No	Controllable	12	l	12.00		18	r	18
27	Yes	Controllable	13	m	13.00		19	s	19
28	Yes	Controllable	14	n	14.00		20	t	20
29				Total			21	u	21
30							22	v	22
31							23	w	23
32				Utilities
33	Yes	Non-Controllable	15	o	15.00
34	No	Non-Controllable	16	p	16.00
35	Yes	Non-Controllable	17	q	17.00
36	Yes	Non-Controllable	18	r	18.00
37	Yes	Non-Controllable	19	s	19.00
38	Yes	Non-Controllable	20	t	20.00
39	Yes	Non-Controllable	21	u	21.00
40	Yes	Non-Controllable	22	v	22.00
41	Yes	Non-Controllable	23	w	23.00
42				Total
Sheet6

Cell Formulas
Range	Formula
N11:P31	N11	=LET(NumControl,COUNTIFS(H11:H100,"Yes",I11:I100,"Controllable"),table,SORT(FILTER(H11:L100,H11:H100="Yes"),2,1),tblseq,SEQUENCE(ROWS(table)+1),IFS(tblseq<=NumControl,INDEX(table,tblseq,{3,4,5}),tblseq=NumControl+1,{"","",""},1,INDEX(table,tblseq-1,{3,4,5})))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J11:L41	Expression	=$H11="Yes"	text	NO

The formula is:

=LET(NumControl,COUNTIFS(H11:H100,"Yes",I11:I100,"Controllable"),table,SORT(FILTER(H11:L100,H11:H100="Yes"),2,1),tblseq,SEQUENCE(ROWS(table)+1),IFS(tblseq<=NumControl,INDEX(table,tblseq,{3,4,5}),tblseq=NumControl+1,{"","",""},1,INDEX(table,tblseq-1,{3,4,5})))

LET is a relatively new function that lets you create intermediate results. You can calculate them, then name them, then you can reuse them later in the formula without having to recalculate them. In the original formula I name the intermediate results a, t, and s. Which work, but they're not especially clear as to what they're doing. I renamed them in the new version.

=LET(NumControl,COUNTIFS(H11:H100,"Yes",I11:I100,"Controllable"),table,SORT(FILTER(H11:L100,H11:H100="Yes"),2,1),tblseq,SEQUENCE(ROWS(table)+1),IFS(tblseq<=NumControl,INDEX(table,tblseq,{3,4,5}),tblseq=NumControl+1,{"","",""},1,INDEX(table,tblseq-1,{3,4,5})))

So in this section, the blue is a COUNTIFS, that just counts how many rows have a Yes in column H, and "Controllable" in column I. It was named a in the old version, it's named NumControl (for Number of Controllable rows) here.

=LET(NumControl,COUNTIFS(H11:H100,"Yes",I11:I100,"Controllable"),table,SORT(FILTER(H11:L100,H11:H100="Yes"),2,1),tblseq,SEQUENCE(ROWS(table)+1),IFS(tblseq<=NumControl,INDEX(table,tblseq,{3,4,5}),tblseq=NumControl+1,{"","",""},1,INDEX(table,tblseq-1,{3,4,5})))

In this section, I filter the H11:l100 section, only keeping rows that have "Yes" in column H. I then sort the table by column 2, which is where the Controllable/Non-Controllable code is, so that the Controllable rows sort to the top. This now creates an internal table, which I named table (it was t).

=LET(NumControl,COUNTIFS(H11:H100,"Yes",I11:I100,"Controllable"),table,SORT(FILTER(H11:L100,H11:H100="Yes"),2,1),tblseq,SEQUENCE(ROWS(table)+1),IFS(tblseq<=NumControl,INDEX(table,tblseq,{3,4,5}),tblseq=NumControl+1,{"","",""},1,INDEX(table,tblseq-1,{3,4,5})))

In this section, I create an array with the SEQUENCE function. This array contains the numbers 1, 2, 3, 4, etc. up to the number of rows in the table, plus 1. The extra row is for the space I added between the Controllable and Non-Controllable sections. This I named tblseq (for table sequence), it was just s.

=LET(NumControl,COUNTIFS(H11:H100,"Yes",I11:I100,"Controllable"),table,SORT(FILTER(H11:L100,H11:H100="Yes"),2,1),tblseq,SEQUENCE(ROWS(table)+1),IFS(tblseq<=NumControl,INDEX(table,tblseq,{3,4,5}),tblseq=NumControl+1,{"","",""},1,INDEX(table,tblseq-1,{3,4,5})))

Finally, I display each row of the table, based on the tblseq value. If the tblseq value is less than or equal to the number of Controllable rows, it displays out columns 3, 4, and 5. (Red section.) If tblseq is 1 over the number of Controllable rows, it displays 3 spaces. (Blue section.) Otherwise, (the 1 works as a TRUE value here, so if the first 2 sections aren't triggered, the 3rd always will be), the remaining rows of the table are displayed. (Green section.)

Hope this helps!

 

[MikeWnbExclWiz]

Wow, super cool. I really appreciate it!

I have another challenge for you, if you choose to accept it...Is there a way to make it so the first row just be a heading saying "Controllable" and then have a row between the blank row and the next set of rows of information be a heading saying "Non-Controllable". And could there be a row inserted at the end of each section summing up the Controllable and Non-Controllable values and "Total" row at the bottom (after another space) that shows a total of the Controllable and Non-Controllable? I don't even know if this is possible, but figure if anyone knows or could figure it out, it would probably be you.

 

[Eric W]

Well, I always like a challenge:

Book1
	H	I	J	K	L	M	N	O	P
1
2
3
4
5
6	Shared Expense (Y/N)	Controllable/Non-Controllable
7
8
9				OPERATING EXPENSE
10				Salary & Related-Residential			Account Number	Desc	Amount
11	Yes	Non-Controllable	1	a	1.00		Controllable
12	Yes	Non-Controllable	2	b	2.00		8	h	$ 8.00
13	No	Non-Controllable	3	c	3.00		9	i	$ 9.00
14	Yes	Non-Controllable	4	d	4.00		10	j	$ 10.00
15	Yes	Non-Controllable	5	e	5.00		11	k	$ 11.00
16	Yes	Non-Controllable	6	f	6.00		13	m	$ 13.00
17	Yes	Non-Controllable	7	g	7.00		14	n	$ 14.00
18				Total			Total:		$ 65.00
19
20							Non-Controllable
21				Advertising & Promotion			1	a	$ 1.00
22	Yes	Controllable	8	h	8.00		2	b	$ 2.00
23	Yes	Controllable	9	i	9.00		4	d	$ 4.00
24	Yes	Controllable	10	j	10.00		5	e	$ 5.00
25	Yes	Controllable	11	k	11.00		6	f	$ 6.00
26	No	Controllable	12	l	12.00		7	g	$ 7.00
27	Yes	Controllable	13	m	13.00		15	o	$ 15.00
28	Yes	Controllable	14	n	14.00		17	q	$ 17.00
29				Total			18	r	$ 18.00
30							19	s	$ 19.00
31							20	t	$ 20.00
32				Utilities			21	u	$ 21.00
33	Yes	Non-Controllable	15	o	15.00		22	v	$ 22.00
34	No	Non-Controllable	16	p	16.00		23	w	$ 23.00
35	Yes	Non-Controllable	17	q	17.00		Total:		$ 180.00
36	Yes	Non-Controllable	18	r	18.00		Grand Total:		$ 245.00
37	Yes	Non-Controllable	19	s	19.00
38	Yes	Non-Controllable	20	t	20.00
39	Yes	Non-Controllable	21	u	21.00
40	Yes	Non-Controllable	22	v	22.00
41	Yes	Non-Controllable	23	w	23.00
42				Total
43
Sheet6

Cell Formulas
Range	Formula
N11:P36	N11	=LET(NumControl,COUNTIFS(H11:H100,"Yes",I11:I100,"Controllable"),SumControl,SUMIFS(L11:L100,H11:H100,"Yes",I11:I100,"Controllable"),SumNonControl,SUMIFS(L11:L100,H11:H100,"Yes",I11:I100,"Non-Controllable"),table,SORT(FILTER(H11:L100,H11:H100="Yes"),2,1),rws,ROWS(table),tblseq,SEQUENCE(rws+6),IFS(tblseq=1,{"Controllable","",""},tblseq<=NumControl+1,INDEX(table,tblseq-1,{3,4,5}),tblseq=NumControl+2,CHOOSE({1,2,3},"Total:","",SumControl),tblseq=NumControl+3,{"","",""},tblseq=NumControl+4,{"Non-Controllable","",""},tblseq<rws+5,INDEX(table,tblseq-4,{3,4,5}),tblseq<rws+6,CHOOSE({1,2,3},"Total:","",SumNonControl),1,CHOOSE({1,2,3},"Grand Total:","",SumControl+SumNonControl)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N:N	Expression	=FIND(" "&N1&" "," Controllable Grand Total: Non-Controllable ")	text	NO
J11:L41	Expression	=$H11="Yes"	text	NO

However, I have a couple suggestions for you. First, I'm limited to Excel 2021 functions. There's a lot of newer functions, like VSTACK or MAP, that might make this simpler. You might post this formula in another thread and see if anyone can simplify it.

Next, look into the Pivot Table functionality. It basically works as a mini report writer, which is what we have here. I noodled around a bit, and got something like this:

I'm rusty on these, I'm sure it can be improved. But once you get the hang of it, you can do these much quicker than a complicated formula. There are lots of tutorials on the web.

Hope this helps!