Append two columns into DropDown

[BrianGGG]

Hello. I have seen a few posts that are close to what I am trying to do, but nothing that exactly works in my situation.

Here is my situation: I have two columns in a table (or could be split into two tables) that I would like to combine into a single Named Range so that I can use it as a Drop Down for List Validation in another part of my spreadsheet. Here is an example:

Codelists

*	N	O
15	Regular	Additions
16	a	y
17	b	z
18	c	*
19	*	*
20	*	*
21	#VALUE!	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula N21 =CHOOSE({1;2},TRANSPOSE(t_test[Regular]),TRANSPOSE(t_test[Additions])) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


I have two columns above, Regular and Additions. They may or may not have the same number of values (more likely not).
The REGULAR column currently has {a,b,c} and the Additions column has {y,z]

I would like to create a named range that I can use in LIST data validation so that a user would be able to enter
any of the values in either column...in the example this would be a,b,c,y,z.

I have included above something from the forums that has gotten me close, a CHOOSE/TRANSPOSE statement. This combines the arrays, but it makes a two dimensional array rather than a one-dimensional array with all values.

Any ideas how to make the single Dropdown with values from both columns?



Thanks,
BrianGGG

 

[Worf]

See if this page helps:

Merge two columns into one list in excel | Get Digital Help - Microsoft Excel resource

 

[BrianGGG]

Worf,

Thanks Very Much! This posting was the key to my problem.

I had to adapt the solution for two wrinkles: I am using Table columns rather than ranges, and I need to suppress blanks so that my dropdown is continuous.
Here's what I got to:


=IFERROR(IF(INDEX(t_Test2[Regular],ROWS(K$7:K7))<>"",INDEX(t_Test2[Regular],ROWS(K$7:K7)),1/0),
IFERROR(IF(INDEX(t_Test2[Additions], ROWS(K$7:K7)-SUMPRODUCT(N(LEN(t_Test2[Regular])>0)))<>"",INDEX(t_Test2[Additions],
ROWS(K$7:K7)-SUMPRODUCT(N(LEN(t_Test2[Regular])>0))), ""),1/0))

The idea here is: If the row in question is null, I force an error by dividing by zero. I also needed to subtract the non-null rows of the first column with the SUMPRODUCT in the second table.

Absolutely no idea why, but this formula did not require array formula syntax (Ctrl-Shift-Enter). Seems to work for my situation.


Thanks again for leading me to the solution.


BrianGGG

 

[BrianGGG]

Update: It''s much easier to append two arrays into a single one by using the new Dynamic arrays.
Below is an example of merging two arrays (A3#, and B3#):

=IF(SEQUENCE(COUNTA(A3#:B3#))<=ROWS(A3#),
        INDEX(A3#,SEQUENCE(COUNTA(A3#))),
        INDEX(B3#,SEQUENCE(COUNTA(A3#:B3#))-ROWS(A3#)))

 

[kvsrinivasamurthy]

Yo can combine two arrays in single column and use it for validation list.
Array1 Column A -> A4:A8
Array2 Column B -> B4:B10
Elements can be added to both the columns individually.
Combined list in Colmn E
Formla in E5 ten drag down till blank cell is obtained.

=IFERROR(INDEX($A$4:$B$20,IFERROR(AGGREGATE(15,6,(ROW($A$4:$A$20)-ROW($A$4)+1)/($A$4:$A$20<>""),ROWS($E$5:$E5)),AGGREGATE(15,6,(ROW($B$4:$B$20)-ROW($B$4)+1)/($B$4:$B$20<>""),ROWS($E$5:$E5)-COUNTA($A$4:$A$20))),IF(ROWS($E$5:$E5)<=COUNTA($A$4:$A$20),1,2)),"")

A	B	C	D	E	F
X	1				row4
Y	2			X	row5
Z	5			Y	row6
A	12			Z	row7
C	44			A	row8
	5			C	row9
	3			1	row10
				2	row11
				5	row12
				12	row13
				44	row14
				5	row15
				3	row16
					row17
					row18
					row19
					row20
					row21

<tbody>
</tbody>