Give new List including duplicates ignoring zeros

    Hi good people,


    If I have numbers going down in column A, some are duplicates and some are 0's, I would like a formula that will give this exact same list in another column, but filter out the 0's, shifting everything up to give a list including duplicates. Please can someone help with this?..Thank you all kindly..

    Re: Give new List including duplicates ignoring zeros


    See if this is what you want...
    [Table="width:, class:grid"]

    [tr][td][/td][td]

    A

    [/td][td]

    B

    [/td][td]

    C

    [/td][/tr]


    [tr][td]

    1

    [/td][td]

    1

    [/td][td][/td][td]

    1

    [/td][/tr]


    [tr][td]

    2

    [/td][td]

    2

    [/td][td][/td][td]

    2

    [/td][/tr]


    [tr][td]

    3

    [/td][td]

    3

    [/td][td][/td][td]

    3

    [/td][/tr]


    [tr][td]

    4

    [/td][td]

    4

    [/td][td][/td][td]

    4

    [/td][/tr]


    [tr][td]

    5

    [/td][td]

    0

    [/td][td][/td][td]

    1

    [/td][/tr]


    [tr][td]

    6

    [/td][td]

    1

    [/td][td][/td][td]

    2

    [/td][/tr]


    [tr][td]

    7

    [/td][td]

    2

    [/td][td][/td][td]

    3

    [/td][/tr]


    [tr][td]

    8

    [/td][td]

    3

    [/td][td][/td][td]

    4

    [/td][/tr]


    [tr][td]

    9

    [/td][td]

    4

    [/td][td][/td][td][/td][/tr]


    [tr][td]

    10

    [/td][td]

    0

    [/td][td][/td][td][/td][/tr]


    [/table]


    C1=IFERROR(INDEX(A:A,SMALL(IF($A$1:$A$10<>0,ROW($A$1:$A$10)),ROWS($A$1:A1))),"")
    ARRAY entered, using CTRL SGIFT ENTER, not just enter
    then copy down as needed

    Regards


    Ford

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login