# How to create a list from a list?

#### Hélio

##### New Member
Hello team,

I have this scenario: 25 numbers in a fix range // 15 Drawn numbers // 10 numbers not drawn resting.... I need to move the 10 non drawn numbers in a row with no empty cells... Those numbers will be different in every draw... I need a formula to understand that all numbers not set at the drawn position must be moved to another position composing a new range.... Pls see attached.

#### Attachments

• 11.9 KB Views: 21

#### Luke M

##### Excel Ninja
Put this array formula in AC6
=IFERROR(INDEX(4:4,SMALL(IF(COUNTIF(\$C\$7:\$Q\$7,\$C\$4:\$AA\$4)=0,COLUMN(\$C\$4:\$AA\$4)),COLUMNS(\$A\$1:A\$1))),"")

NOTE: Confirm as an array using Ctrl+Shift+Enter, not just Enter. Copy cell to the right as desired.

#### Hélio

##### New Member
Hello Luke, Genious!!!! I have tried a formula like that but my biggest mistake was to use Row and not column.... Thank very much...

#### Hélio

##### New Member
Luke, one more question... In your formula last items COLUMNS(\$A\$1:A\$1) is referring to what?

#### Luke M

##### Excel Ninja
It acts as a counter. The COLUMNS function returns a number indicating how many columns are in the given range. So, at first, this evaluates to 1. When you copy one cell to the right, the range becomes \$A1:B1, which then evaluates to 2, then 3, etc. This is so that the SMALL function is being told to return the 1st answer that matches, then the 2nd, then 3rd.

So, the actual cells being reference don't matter as much as the size of the cells in the reference.

#### GraH - Guido

##### Well-Known Member
Alternative without CSE
[AC6]=AGGREGATE(15,6,\$C\$4:\$AA\$4/(LOOKUP(\$C\$4:\$AA\$4,\$C\$7:\$Q\$7)<>\$C\$4:\$AA\$4),COLUMN(A\$1)) and drag across.

EDIT: Nope! That's wrong...

EDIT2: making up form my silliness, had to make a power query solution. After having named both ranges.
Code:
``````let
Source = Excel.CurrentWorkbook(),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Record.ToList([Content]{0})),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
#"Added Custom1" = Table.AddColumn(#"Transposed Table", "Not Drawn", each List.Difference([Column2],[Column1])),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Not Drawn"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns1", "Not Drawn")
in
#"Expanded Custom"``````

#### Attachments

• 20.6 KB Views: 7
Last edited:

#### nivanyameha

##### New Member
1. Select the cell where you want the first (main) drop down list.
2. Go to Data –> Data Validation. ...
3. In the data validation dialog box, within the settings tab, select List.
4. In Source field, specify the range that contains the items that are to be shown in the first drop down list.
5. Click OK.

#### Peter Bartholomew

##### Well-Known Member
I have the huge advantage of having dynamic arrays and the new functions available to me.
= FILTER( fixedRange, IFERROR(LOOKUP(fixedRange, draw),0)<>fixedRange )

#### salim hasan

##### Member
Can you try my Array_Formula please
Code:
``=IF(COLUMNS(\$A\$1:A1)>COUNTA(\$C\$4:\$AA\$4)-COUNTA(\$C\$7:\$Q\$7),"",INDEX(\$C\$4:\$AA\$4,SMALL(IF(COUNTIF(\$C\$7:\$Q\$7,\$C\$4:\$AA\$4)=0,COLUMN(\$C\$4:\$AA\$4)-COLUMN(\$C\$4)+1),COLUMNS(\$A\$1:A1))))``

#### Attachments

• 12.5 KB Views: 1

#### Peter Bartholomew

##### Well-Known Member
Since FILTER does not work on standard Excel installations, the array formula
= SMALL( NotDrawn, {1,2,3,4,5,6,7,8,9,10} )
will compress the list of values not drawn, where 'NotDrawn' refers to
= IF( ISERROR(MATCH( fixedRange, draw, 0 )), fixedRange )
This time I have switched from an approximated match to a more familiar exact match.