Transpose from One Column to Another based on a Condition

I, for the life of me, can't figure this out (and I'm sure it's fairly simple). I'm trying to transpose unique values from one sheet to another BUT based on a condition.

In the example below I'd like to pull any Record#s (columnA) that have "yes" as the data3 (columnD) into the resulting table (column F). This data is generated from the data changes regularly and I have several scenarios/results tables to build and I'd like it to be a formula and not manual. Macros cannot be enabled in my environment.

Any help would be greatly appreciated!


Source:

Record# Data1 Data2 Data3
1 a 11 Yes
2 b 12 No
3 c 13 Yes
4 d 14 No
5 e 15 Yes
6 f 16 Yes


Result:

Result Data3
1 Yes
3 Yes
5 Yes
6 Yes


transpose question.png

sample files generally better than pictures but, perhaps:

Formula:

Please Login or Register  to view this content.

Here's a sample table - hopefully I'm attaching/including it properly this time.

clear D6:D12 then if, as you say, you only want the first column from source range simply apply below to D6:

=FILTER(A6:A12,B6:B12=E3)

Thanks again for the reply but now I'm getting a #CALC error. The adjusted formula I'm using is: =FILTER(Data!$A$9$:A$22,Data!$K$9:$K$22="delayed") where A9:A22 is my record# column and K9:K22 is the criteria column.

This is referencing a different sheet within the workbook - could that be the issue?

Shouldn't be an issue referencing a different sheet, that error occurs if it finds no results for the filter, is that the case here?

That was it. The data was "delayed ...". How can I make this a little more dynamic and allow for other variations of this status code? i.e. "Delayed*"

Try:

=FILTER(Data!$A$9$:A$22,ISNUMBER(SEARCH("Delayed",Data!$K$9:$K$22)))

Perfect, thank you!

Ok, any chance you can tell me what's wrong with this formula. I've added a 2nd condition to check the date (which is in text form).

FULLTABLE = entire source table
COLLECT = date (as text) in format of "YYYY-MM-DD"
STATUS = text where we find "delayed"

{=FILTER(FULLTABLE, (COLLECT<=TEXT(TODAY()+1, "YYYY-MM-DD"))*(ISNUMBER(SEARCH({"clearance","booked","ok to ship","hold","collection attempt"},STATUS))))

What is not working? Error or wrong result? I guess error as I don't think you can pass a 2-d array to FILTER, which is what gets created when you use SEARCH with an array like that. There's probably a way around it though. I might get a chance to look tomorrow, if nobody else does first.

Hi to all!

Originally Posted by jonathan.phillips

Ok, any chance you can tell me what's wrong with this formula...

You can't use 2D arrays into the include argument of FILTER. If I get what you want, you can use this formula:

PHP Code: 

=FILTER(FULLTABLE, (COLLECT<=TEXT(TODAY()+1, "YYYY-MM-DD"))*(MMULT(--ISNUMBER(SEARCH({"clearance","booked","ok to ship","hold","collection attempt"},STATUS)), {1;1;1;1;1}))) 


Blessings!

FWIW, the use of SEARCH (as opposed to MATCH) implies that your criteria are embedded within longer strings (STATUS) however, your earlier post (K-range="delayed") implies that might not be the case...

So, if the terms are, in fact, stored as isolated strings in STATUS you could use:

Formula:

Please Login or Register  to view this content.

i.e. you simply bookend STATUS values with a delimiter (pipe in this case) then search this value against the delimited concatenation of search terms -- in this context, bookending terms removes risk of false positives.

That did it! Looks like I need to do some youtube watching on these various elements - i've never heard of MMULT.

Unfortunately, they are not static strings and there is a lot of variability in the text strings of STATUS.