Hello,
Example:
A B C D
1 Mark David Elise Ted
2 X P X X
what I want:
A formula to give me the names of the people(row 1) with a "X" in row 2
Can't seem to figure it out, tried Lookup functions...
Thanks a lot,
Johan
Hello,
Example:
A B C D
1 Mark David Elise Ted
2 X P X X
what I want:
A formula to give me the names of the people(row 1) with a "X" in row 2
Can't seem to figure it out, tried Lookup functions...
Thanks a lot,
Johan
do not know what you really want but try
=if(A2="X",A1&" ","")+if(B2="X",B1&" ","")+if(C2="X",C1&"
","")+if(D2="X",D1&" ","")+
"johanvdv" wrote:
> Hello,
>
> Example:
>
> A B C D
> 1 Mark David Elise Ted
> 2 X P X X
>
>
> what I want:
> A formula to give me the names of the people(row 1) with a "X" in row 2
>
> Can't seem to figure it out, tried Lookup functions...
>
> Thanks a lot,
> Johan
>
>
it works if I put a CONCATENATE around it but:
- I can only concatenate 5 strings
- I have A LOT of columns, it would become a VERY large formula
Is't there a shorter, better way ?
Hi Johan,
You don't mention in what form you need the names (in a string, in a column,
etc.) but I would transpose my data into another sheet, then you can use a
simple autofilter to see rows with X in column B.
Post if this solution meets your needs and if you want additional help on
transposing and filtering!
Regards,
Stefi
„johanvdv” ezt *rta:
> it works if I put a CONCATENATE around it but:
>
> - I can only concatenate 5 strings
> - I have A LOT of columns, it would become a VERY large formula
>
> Is't there a shorter, better way ?
>
>
another way to do it
use a helper row
(3?)
in A3 enter
=if(A2="X",A1&" ","")
copy across your data
somewhere else
=Concatinate(3:3)
"johanvdv" wrote:
> it works if I put a CONCATENATE around it but:
>
> - I can only concatenate 5 strings
> - I have A LOT of columns, it would become a VERY large formula
>
> Is't there a shorter, better way ?
>
>
How many columns do you have?
It will probably require a VBA solution;
Jim
"johanvdv" <[email protected]> wrote in message
news:[email protected]...
> it works if I put a CONCATENATE around it but:
>
> - I can only concatenate 5 strings
> - I have A LOT of columns, it would become a VERY large formula
>
> Is't there a shorter, better way ?
>
bj wrote...
>do not know what you really want but try
>=if(A2="X",A1&" ","")+if(B2="X",B1&" ","")
>+if(C2="X",C1&" ","")+if(D2="X",D1&" ","")+
....
Always best to test. Using + as operator with guaranteed nonnumeric
string operands will always produce #VALUE! errors. Did you mean to put
& between all the IF calls?
Hi,
Try,
In A3 and copied down,
=IF(ROW(A1)-ROW($A$1)+1>COUNTIF($A$2:$D$2,"x"),"",INDEX($A$1:$D$1,SMALL(IF($A$2:$D$2="x",COLUMN($A$2:$D$2)),ROW(A1)-ROW($A$1)+1)))
Or if you want the results in one cell,
=SUBSTITUTE(aCONCAT(IF(A2:D2="x",A1:D1),","),",False","")
Confirm with Ctrl+Shift+Enter.
ACONCAT is a UDF.
HTHPlease Login or Register to view this content.
Kris
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks