Forum Discussion

martineDo's avatar
martineDo
Copper Contributor
Sep 24, 2024
Solved

make a list of objects having a text as value

I would like to be able to select all objects à garder and on one other column all the lourder I always get a #valeur or #nom  is it possible that excel doesn't like text ? I am using office 2010 ...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 24, 2024

    martineDo 

    Similar to PeterBartholomew1 solution, but without helper column and Ctrl+Shift+Enter is not required.

    In G3 formula is

    =IFERROR( INDEX(B:B, AGGREGATE(15,6, ROW($E$3:$E$51)/($E$3:$E$51=$J$2), ROW()-ROW($J$2)) ), "")

    and drag it to the right and after that G3:J3 down till empty cells appear.

    Not sure what do you mean under objects and what are #valeur or #nom.

     

    Above formula is for English version, if you open attached file formula in it will be in your locale if it's different.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 24, 2024

martineDo 

Sadly, you are using the wrong version of Excel, 365 would make your task much easier.

= FILTER(objects, state="à garder")

To build equivalent functionality in legacy Excel, you could use a number sequence 1-49 in a helper range to get the numbers that correspond to the text and then SMALL to pack the numbers into ascending order without gaps.  INDEX would then return a filtered list.

= IFERROR(INDEX(objects, SMALL(IF(state="à garder", seq), seq)), "")

You very  likely will have the commit the formula as an array using CSE (Ctrl/Shift/Enter).