• 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

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

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

Last edited:
Top