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

mrcool

New Member
Joined
Jun 29, 2010
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.

Capture.PNG

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.

Capture 1.PNG


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
Joined
Aug 18, 2015
Messages
11,655
How about:

Book1
ABCDE
1List 1New List
2AA
3BB
4DD
5AA
6Heading 1Use Alternate Value?noBB
7List 2Alternate ValueCC
8AAABDD
9BB 
10
11CCCB
12DD
13EB
14Heading 3
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
Joined
Jun 29, 2010
Messages
8
Office Version
  1. 365
Platform
  1. Windows
How about:

Book1
ABCDE
1List 1New List
2AA
3BB
4DD
5AA
6Heading 1Use Alternate Value?noBB
7List 2Alternate ValueCC
8AAABDD
9BB 
10
11CCCB
12DD
13EB
14Heading 3
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
Joined
Jun 29, 2010
Messages
8
Office Version
  1. 365
Platform
  1. Windows
How about:

Book1
ABCDE
1List 1New List
2AA
3BB
4DD
5AA
6Heading 1Use Alternate Value?noBB
7List 2Alternate ValueCC
8AAABDD
9BB 
10
11CCCB
12DD
13EB
14Heading 3
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.

excel question.PNG
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,698
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

For the original layout/request you could try this.

21 05 25.xlsm
BCDE
1List 1New List
2AA
3BB
4DD
5AA
6Heading 1Use Alternate Value?noBB
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
6Heading 1Use Alternate Value?yesBB
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
Joined
Aug 18, 2015
Messages
11,655
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
Joined
May 28, 2005
Messages
51,698
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 
Solution

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,655
.. 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
Joined
Jun 29, 2010
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top