How to create a list from a list?

[Hélio]

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.

 

[Luke M]

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]

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]

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

 

[Luke M]

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]

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.

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"

 

[nivanyameha]

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]

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

=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))))

 

[Peter Bartholomew]

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.