Combine non-blank items in two ranges (unknown number of values) into a new list

[mrcool]

Need help creating a combined list from values in two ranges. All non-blank items in list 1 should be included before adding the non-blank items in list 2. The headings between and after these lists should not be included in the resulting list. The challenge I'm having is that I don't know which cells will be blank and which ones wont. The values will always be in range B2:B5 and B8:B13 but I don't know how many items will be populated and whether there will be blanks in between.

I've made a start of it but keep getting stuck. Here is my example for column E:
=IFERROR(IF(B2<>"",B2,OFFSET($B$8,0,0,COUNTA($B$8:$B$13),0)),"") and then copying the formula down column E. This gets some of the right results but not all. Since it doesn't work I haven't gotten to the next element of complexity as shown in the next image down.


To make it more complicated, for List 2 items (B8:B13), if D6 = "Yes" (Use alternate value) then the sheet is to find the corresponding non-blank value for the item in List 2 from range C8:C13. Not all cells in List 2 will have alternate values from C8:C13 but all values from Alternate Value (C8:C13) range will have a corresponding non-blank value in List 2. As an example, if cell $D$6="Yes", B8 is not "" and C8 is not "" then use value of C8. But if $D$6="Yes", B8 is not "", but C8 is blank then use value in B8. This example is shown in the image below. If there is a value in the Alternate Value range C8:C13 that does not have a corresponding value in B8:B13, then it should be ignored such as shown in C13.

Any help would be most appreciated.

 

[Eric W]

How about:

Book1
	A	B	C	D	E
1		List 1			New List
2		A			A
3		B			B
4		D			D
5					AA
6		Heading 1	Use Alternate Value?	no	BB
7		List 2	Alternate Value		CC
8		AA	AB		DD
9		BB
10
11		CC	CB
12		DD
13			EB
14		Heading 3
15
Sheet12

Cell Formulas
Range	Formula
E2:E9	E2	=IFERROR(IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$5)/($B$2:$B$5<>""),ROWS($E$2:$E2))),INDEX($B:$C,AGGREGATE(15,6,ROW($B$8:$B$13)/($B$8:$B$13<>""),ROWS($E$2:$E2)-COUNTA($B$2:$B$5)),IF(AND($D$6="Yes",INDEX($C:$C,AGGREGATE(15,6,ROW($B$8:$B$13)/($B$8:$B$13<>""),ROWS($E$2:$E2)-COUNTA($B$2:$B$5)))<>""),2,1))),"")

Also, please update your user profile to include the version of Excel you're using. It could be that there's an easier way if you have some of the newer functions.

 

[mrcool]

How about:

Book1
	A	B	C	D	E
1		List 1			New List
2		A			A
3		B			B
4		D			D
5					AA
6		Heading 1	Use Alternate Value?	no	BB
7		List 2	Alternate Value		CC
8		AA	AB		DD
9		BB
10
11		CC	CB
12		DD
13			EB
14		Heading 3
15
Sheet12

Cell Formulas
Range	Formula
E2:E9	E2	=IFERROR(IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$5)/($B$2:$B$5<>""),ROWS($E$2:$E2))),INDEX($B:$C,AGGREGATE(15,6,ROW($B$8:$B$13)/($B$8:$B$13<>""),ROWS($E$2:$E2)-COUNTA($B$2:$B$5)),IF(AND($D$6="Yes",INDEX($C:$C,AGGREGATE(15,6,ROW($B$8:$B$13)/($B$8:$B$13<>""),ROWS($E$2:$E2)-COUNTA($B$2:$B$5)))<>""),2,1))),"")

Also, please update your user profile to include the version of Excel you're using. It could be that there's an easier way if you have some of the newer functions.

Thank you very much for the help. I've updated my profile. I'm using Office 365. It would be amazing if there was a simpler way of doing this as I have several similar scenarios with some variation.

 

[mrcool]

How about:

Book1
	A	B	C	D	E
1		List 1			New List
2		A			A
3		B			B
4		D			D
5					AA
6		Heading 1	Use Alternate Value?	no	BB
7		List 2	Alternate Value		CC
8		AA	AB		DD
9		BB
10
11		CC	CB
12		DD
13			EB
14		Heading 3
15
Sheet12

Cell Formulas
Range	Formula
E2:E9	E2	=IFERROR(IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$5)/($B$2:$B$5<>""),ROWS($E$2:$E2))),INDEX($B:$C,AGGREGATE(15,6,ROW($B$8:$B$13)/($B$8:$B$13<>""),ROWS($E$2:$E2)-COUNTA($B$2:$B$5)),IF(AND($D$6="Yes",INDEX($C:$C,AGGREGATE(15,6,ROW($B$8:$B$13)/($B$8:$B$13<>""),ROWS($E$2:$E2)-COUNTA($B$2:$B$5)))<>""),2,1))),"")

Also, please update your user profile to include the version of Excel you're using. It could be that there's an easier way if you have some of the newer functions.

I tried your formula and it worked. Amazing. My problem came up when I tried to adapt it to my sheet. There was some success but ran into an issue when using the alternate name (J13:J23). In that instance the formula returns a number instead of the value. I'm sure this is my own error, but I have included the actual spreadsheet layout for more clarity. Again, I am using Office 365 if that makes a difference. Sorry for not providing the larger sheet before but I was hoping that I'd be able to adapt the formula for my sheet without overcomplicating my post.

Here is the formula I used, based largely on your previous help. I just changed the ranges.
=IFERROR(IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$7:$B$10)/($B$7:$B$10<>""),ROWS($B$28:$B28))),INDEX($B:$C,AGGREGATE(15,6,ROW($B$13:$B$23)/($B$13:$B$23<>""),ROWS($B$28:$B28)-COUNTA($B$7:$B$10)),IF(AND($L$12="Yes",INDEX($J:$J,AGGREGATE(15,6,ROW($B$13:$B$23)/($B$13:$B$23<>""),ROWS($B$28:$B28)-COUNTA($B$7:$B$10)))<>""),2,1))),"")

Note when L12 ="Yes" B32 and B35 returns a number instead of the value.

In addition I need to return the quantity (column C) for each item in B28:B37.

 

[Peter_SSs]

For the original layout/request you could try this.

21 05 25.xlsm
	B	C	D	E
1	List 1			New List
2	A			A
3	B			B
4	D			D
5				AA
6	Heading 1	Use Alternate Value?	no	BB
7	List 2	Alternate Value		CC
8	AA	AB		DD
9	BB
10
11	CC	CB
12	DD
13		EB
Lists

Cell Formulas
Range	Formula
E2:E8	E2	=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,B2:B5,IF(D6="yes",IF(C8:C13="",B8:B13&"",C8:C13),B8:B13))&"</c></p>","//c")
Dynamic array formulas.

.. and here it is with a "yes"

21 05 25.xlsm
	B	C	D	E
1	List 1			New List
2	A			A
3	B			B
4	D			D
5				AB
6	Heading 1	Use Alternate Value?	yes	BB
7	List 2	Alternate Value		CB
8	AA	AB		DD
9	BB			EB
10
11	CC	CB
12	DD
13		EB
Lists

Cell Formulas
Range	Formula
E2:E9	E2	=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,B2:B5,IF(D6="yes",IF(C8:C13="",B8:B13&"",C8:C13),B8:B13))&"</c></p>","//c")
Dynamic array formulas.

 

[Eric W]

This is an excellent example of why you should try to show your sheet as closely as possible. Changing formulas, especially one as long as this, can be tricky. Here's how to adapt it to your current layout:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4		Item	Quantity		New List
5
6		Users*
7		AA	75
8		BB	24
9		CC	10
10		DD	5
11		Users Total	114
12		Products								Change Names?		Yes
13		Product A	1							Not Product A
14		Product B	1
15		Product C	1
16		Product D	1							Product D New Name
17		Product E	1
18
19
20
21
22
23		Product F
24		Product Total
25
26
27		Names	Quantity
28		AA		AA
29		BB		BB
30		CC		CC
31		DD		DD
32		Not Product A		Not Product A
33		Product B		Product B
34		Product C		Product C
35		Product D New Name		Product D New Name
36		Product E		Product E
37		Product F		Product F
38
Sheet12

Cell Formulas
Range	Formula
C11	C11	=SUM(C7:C10)
D28:D37	D28	=FILTER(IF((ROW(B7:B23)>12)*(L12="yes")*(J7:J23<>""),J7:J23,B7:B23),(B7:B23<>"")*ISERROR(MATCH(ROW(B7:B23),{11,12},0)))
B28:B38	B28	=IFERROR(IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$7:$B$10)/($B$7:$B$10<>""),ROWS($B$28:$B28))),INDEX($B:$J,AGGREGATE(15,6,ROW($B$13:$B$23)/($B$13:$B$23<>""),ROWS($B$28:$B28)-COUNTA($B$7:$B$10)),IF(AND($L$12="Yes",INDEX($J:$J,AGGREGATE(15,6,ROW($B$13:$B$23)/($B$13:$B$23<>""),ROWS($B$28:$B28)-COUNTA($B$7:$B$10)))<>""),9,1))),"")
Dynamic array formulas.

Here are the parts you missed while converting it:

=IFERROR(IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$7:$B$10)/($B$7:$B$10<>""),ROWS($B$28:$B28))),INDEX($B:$J,AGGREGATE(15,6,ROW($B$13:$B$23)/($B$13:$B$23<>""),ROWS($B$28:$B28)-COUNTA($B$7:$B$10)),IF(AND($L$12="Yes",INDEX($J:$J,AGGREGATE(15,6,ROW($B$13:$B$23)/($B$13:$B$23<>""),ROWS($B$28:$B28)-COUNTA($B$7:$B$10)))<>""),9,1))),"")

Those needed to refer to the column with the new names in it. However, the formula in D28 is an Excel 365 equivalent which is much shorter and hopefully easier to understand. It should be adaptable as long as the 2 ranges are in the same column.


And just as an FYI, consider downloading and using the XL2BB add-in (see the link in my signature or the reply box). It's easy to download, install, and use, and it makes it much easier for the helpers here to work with your data - we don't have to retype everything.

 

[Peter_SSs]

the formula in D28 is an Excel 365 equivalent which is much shorter

.. but not as robust Eric?
If any rows are inserted or deleted above row 12, erroneous values will be returned. I'm sure it could be strengthened in that regard though.

The adaptation of my previous suggestion to this layout is:

21 05 25.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4		Item	Quantity		New List
5
6		Users*
7		AA	75
8		BB	24							xxxxx
9		CC	10
10		DD	5
11		Users Total	114
12		Products								Change Names?		yes
13		Product A	1							Not Product A
14		Product B	1
15		Product C	1
16		Product D	1							Product D New Name
17		Product E	1
18
19
20
21
22
23		Product F
24		Product Total
25
26
27		Names	Quantity
28		AA
29		BB
30		CC
31		DD
32		Not Product A
33		Product B
34		Product C
35		Product D New Name
36		Product E
37		Product F
38
Lists (2)

Cell Formulas
Range	Formula
B28:B37	B28	=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,B7:B10,IF(L12="yes",IF(J13:J23="",B13:B23&"",J13:J23),B13:B23))&"</c></p>","//c")
Dynamic array formulas.

 

[Eric W]

.. but not as robust Eric?

True enough Peter, anytime you hard code row numbers there's a risk. The following improves on that:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4		Item	Quantity		New List
5
6		Users*
7		AA	75
8		BB	24
9			10
10		DD	5
11		Users Total	114
12		Products								Change Names?		Yes
13		Product A	1							Not Product A
14		Product B	1
15		Product C	1
16		Product D	1							Product D New Name
17		Product E	1
18
19
20
21
22
23		Product F
24		Product Total
25
26
27		Names	Quantity
28		AA
29		BB
30		DD
31		Not Product A
32		Product B
33		Product C
34		Product D New Name
35		Product E
36		Product F
37
Sheet12

Cell Formulas
Range	Formula
C11	C11	=SUM(C7:C10)
B28:B36	B28	=FILTER(IF((ROW(B7:B23)>ROW(B12))*(L12="yes")*(J7:J23<>""),J7:J23,B7:B23),(B7:B23<>"")*ISERROR(MATCH(B7:B23,{"Users Total","Products"},0)))
Dynamic array formulas.

There is still some risk if rows are added in between the ranges, with potentially other headings. I may ruminate on that a bit more.

 

[mrcool]

Thank you both. Amazing. Both do work. Apologies for making it more difficult than it had to be by not posting using XL2BB. Very much appreciate you taking the time to help me in spite of that.

 

[Eric W]

Glad we could help!