• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create a list from a list?


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.



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

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.
    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")
    #"Expanded Custom"


Last edited:


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

Can you try my Array_Formula please


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.