Forum Discussion
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
thank you for your help
| 3/4 | 3932 | ||
| 1/2 | 3039 | v | |
| 1/2 | 3007 | v | |
| 1/2 | sans | a garder | |
| 1/2 | 8938 | à garder | |
| 1/4 | 3643 | ||
| 1/4 | 3805 | ||
| 1/4 | 3017 | ||
| 1/8 | 3804 | à garder | |
| 1/8 | 3028 | à garder | |
| 1/16 | 8927 | à garder | |
| 1/10 | 3927 | ||
| 1/10 | 8935 | ||
| Grösse | Inv | VP | |
| 3/4 | sans nr alto | ||
| 3/4 | sans Bachelet? | ||
| 1/2 | 3930 | ||
| 3/4 | 3076 | ||
| 3/4 | 3908 | ||
| 3/4 | 3919 | ||
| 1/4 | 3914 | ||
| 1/4 | 3906 | ||
| 3/4 | 3910 | ||
| 1/4 | 3913 | ||
| 1/4 | 3941 | lourder | |
| 1/4 | 8938 | à garder | |
| 1/4 | 3942 | ||
| 1/10 | 3945 | ||
| 1/4 | 3921 | ||
| 1/2 | 3090 | ||
| 1/2 | 3003 | lourder | |
| 3/4 | 3934 | à garder | |
| 3/4 | 3002 | lourder | |
| 3/4 | 3902 | lourder | |
| 1/4 | 3912 | lourder | |
| 1/4 | 3802 | à garder | |
| 3/4 | 3803 | à garder | |
| 1/2 | 3900 | ||
| 3/4 | 3806 | à garder | |
| 3/4 | 3909 | lourder | |
| 3/4 | 683 | lourder | |
| 3/4 | 3926 | lourder | |
| 1/2 | 687 | lourder | |
| 3/4 | 3002 | ||
| 3/4 | 3903 | ||
| 1/2 | 3026 | lourder | |
| 3/4 | 3933 | lourder | |
| 3/4 | 3920 | lourder |
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.
5 Replies
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).
- should I rephrase ?
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.
thank you for answering
I am afraid it doesn't work on with my version, French 2010
Never mind
thank you
I will update sometimes
