• 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

    Hui...

  • 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?

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

  • Question.xlsx
    11.9 KB · Views: 21
Hello Luke, Genious!!!! I have tried a formula like that but my biggest mistake was to use Row and not column.... Thank very much...
 
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.
 
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

  • Copy of Question.xlsx
    20.6 KB · Views: 7
Last edited:
  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.
 
I have the huge advantage of having dynamic arrays and the new functions available to me.
= FILTER( fixedRange, IFERROR(LOOKUP(fixedRange, draw),0)<>fixedRange )
 
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

  • Question_Salim.xlsx
    12.5 KB · Views: 1
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.
 
Back
Top