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

#### mrcool

##### New Member
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.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Eric W

##### MrExcel MVP

Book1
ABCDE
1List 1New List
2AA
3BB
4DD
5AA
7List 2Alternate ValueCC
8AAABDD
9BB
10
11CCCB
12DD
13EB
15
Sheet12
Cell Formulas
RangeFormula
E2:E9E2=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

##### New Member

Book1
ABCDE
1List 1New List
2AA
3BB
4DD
5AA
7List 2Alternate ValueCC
8AAABDD
9BB
10
11CCCB
12DD
13EB
15
Sheet12
Cell Formulas
RangeFormula
E2:E9E2=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

##### New Member

Book1
ABCDE
1List 1New List
2AA
3BB
4DD
5AA
7List 2Alternate ValueCC
8AAABDD
9BB
10
11CCCB
12DD
13EB
15
Sheet12
Cell Formulas
RangeFormula
E2:E9E2=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

##### MrExcel MVP, Moderator

For the original layout/request you could try this.

21 05 25.xlsm
BCDE
1List 1New List
2AA
3BB
4DD
5AA
7List 2Alternate ValueCC
8AAABDD
9BB
10
11CCCB
12DD
13EB
Lists
Cell Formulas
RangeFormula
E2:E8E2=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
BCDE
1List 1New List
2AA
3BB
4DD
5AB
7List 2Alternate ValueCB
8AAABDD
9BBEB
10
11CCCB
12DD
13EB
Lists
Cell Formulas
RangeFormula
E2:E9E2=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

##### MrExcel MVP
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
ABCDEFGHIJKL
1
2
3
4ItemQuantityNew List
5
6Users*
7AA75
8BB24
9CC10
10DD5
11Users Total114
12ProductsChange Names?Yes
13Product A1Not Product A
14Product B1
15Product C1
16Product D1Product D New Name
17Product E1
18
19
20
21
22
23Product F
24Product Total
25
26
27NamesQuantity
28AAAA
29BBBB
30CCCC
31DDDD
32Not Product ANot Product A
33Product BProduct B
34Product CProduct C
35Product D New NameProduct D New Name
36Product EProduct E
37Product FProduct F
38
Sheet12
Cell Formulas
RangeFormula
C11C11=SUM(C7:C10)
D28:D37D28=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:B38B28=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

##### MrExcel MVP, Moderator

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
ABCDEFGHIJKL
1
2
3
4ItemQuantityNew List
5
6Users*
7AA75
8BB24xxxxx
9CC10
10DD5
11Users Total114
12ProductsChange Names?yes
13Product A1Not Product A
14Product B1
15Product C1
16Product D1Product D New Name
17Product E1
18
19
20
21
22
23Product F
24Product Total
25
26
27NamesQuantity
28AA
29BB
30CC
31DD
32Not Product A
33Product B
34Product C
35Product D New Name
36Product E
37Product F
38
Lists (2)
Cell Formulas
RangeFormula
B28:B37B28=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

##### MrExcel MVP
.. but not as robust Eric?
True enough Peter, anytime you hard code row numbers there's a risk. The following improves on that:

Book1
ABCDEFGHIJKL
1
2
3
4ItemQuantityNew List
5
6Users*
7AA75
8BB24
910
10DD5
11Users Total114
12ProductsChange Names?Yes
13Product A1Not Product A
14Product B1
15Product C1
16Product D1Product D New Name
17Product E1
18
19
20
21
22
23Product F
24Product Total
25
26
27NamesQuantity
28AA
29BB
30DD
31Not Product A
32Product B
33Product C
34Product D New Name
35Product E
36Product F
37
Sheet12
Cell Formulas
RangeFormula
C11C11=SUM(C7:C10)
B28:B36B28=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

##### New Member
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.

Replies
8
Views
195
Replies
12
Views
352
Replies
13
Views
495
Replies
6
Views
152
Replies
2
Views
406

1,147,482
Messages
5,741,409
Members
423,658
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back