Re: Extract subarray from table column based on criteria matched-structure table reference

[jaryszek]

Hi,

i want to add into named range from dynamic table where Criteria is "Yes" or "No".

So for named range = "ResultNo" i should have array : {1111,2222,3333} and for named range = "ResultYes" i should have : {4444,5555,6666,7777} in Formulas Manager from dynamic table.
How can i achive this in the simplest way?

Best,
Jacek

Screenshot_16.png

[Andy Pope]

ResultYes: =TEXTJOIN(",",TRUE,IF(t_MaxMemory[Environment]="Yes",t_MaxMemory[Offered],""))
ResultNo: =TEXTJOIN(",",TRUE,IF(t_MaxMemory[Environment]="No",t_MaxMemory[Offered],""))

Requires xl2016 in order to use the TEXTJOIN function

[jaryszek]

wow thank you.

What about array formula and excel 2010 ?

Best,
Jacek

[Andy Pope]

You would need to create a UDF (user defined function) to concatenate text.

[jaryszek]

thank you Andy.

I do not want to this.
Maybe i will use helpel columns to get specific values with condition.

Can anybody help with writing array formula for this?
Best,
Jacek

[jaryszek]

and i saw that this textjoin is not treated like array - this is text joined...
This is not what i mean here...

I want to use this in other formulas as array of values...

Best,
Jacek

[jaryszek]

In other words:
I need extract subarray from table column based on criteria matched in the same table.

Best,
Jacek

[Bosco]

Maybe…………….

1] G4,

=OFFSET(A2:A10,MATCH("No",A2:A10,0)-1,1,COUNTIF(A2:A10,"No"))

2] I4,

=OFFSET(A2:A10,MATCH("Yes",A2:A10,0)-1,1,COUNTIF(A2:A10,"Yes"))

p.s. press F9 to view the result

Regards
Bosco

[Glenn Kennedy]

Bosco - is that amendable to take account of a more realistic example where the Yes/No responses are not/cannot be sorted?

[Bosco]

Bosco - is that amendable to take account of a more realistic example where the Yes/No responses are not/cannot be sorted?

If data are unsorted (or sorted), try to use :

=INDEX($B$1:$B$10,N(IF(1,AGGREGATE(15,6,ROW($B$1:$B$10)/($A$1:$A$10="No"),ROW(INDIRECT("1:"&COUNTIF($A$1:$A$10,"No")))))))

or,

=INDEX($B$1:$B$10,N(IF(1,AGGREGATE(15,6,ROW($B$1:$B$10)/($A$1:$A$10="Yes"),ROW(INDIRECT("1:"&COUNTIF($A$1:$A$10,"Yes")))))))


Regards
Bosco

[Glenn Kennedy]

Brilliant. I had been playing with this:

=SMALL(IF(A2:$A$10="No",$B$2:$B$10),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$10,"No"))))

which returns the correct values in an array (F9), but which cannot be used in a DV list. To preserve what is left of my sanity.... why does it not work in the DV list??

[Glenn Kennedy]

I had too much faith in your solution (which I looked at, but did not evaluate!!), Bosco... it returns the same error message in the DV list as my effort.

[Glenn Kennedy]

Obviously, it's INDIRECT that's mucking it up... but I cannot get a workaround to work for me...

[jaryszek]

so this is working only when conditionas are in order?

Best,
Jacek

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[jaryszek]

Hi FDibbins,

thank you.

Please change the title for:

"Extract subarray from table column based on criteria matched"

Best,
Jacek

[FDibbins]

I explained in my post how you can change your title

[jaryszek]

Ok thank you done it.

Returning to the topic, Bosco, your formula only working when we have sorted table?

I changed order and this is working like a charm.
Can you help me with understanding this function?

Please Login or Register  to view this content.

Mainly this one:

Please Login or Register  to view this content.

Result is:

Please Login or Register  to view this content.

How this is working?

Why Glenn did you wrote that there is an error?

And one more thing - this is only working when i have table in first row. What if i will move table to assume 5th row? It would be not working...

Best,
Jacek

[Glenn Kennedy]

I am away from my PC. Bosco's formula works fine. But. I assumed that yiu wanted to use this for data validation, given your previous threads. I could not get it to work in DV. Did you get it working, or did yiu want the array for another purpose? Can't access your file at the moment.

[jaryszek]

Hi Glenn.

thanks!

Formula is working fine but this should refer to dynamic table addresses not cell addresses.
This is not working where you move your table into another place.

I want to use it in formula as array to look within.
So i want to have named range and put this array into it.

Best,
Jacek

[rorya]

@Glenn,

A DV list source must be a delimited string or a single column/row range. It cannot be an array, unfortunately.

[jaryszek]

Anybody?

This index function would be awesome if we could point into dynamic table reference row number.
Some array with index and match to refer to the exact row number...

Please help,
Best,
Jacek

[Glenn Kennedy]

See rorya's comment and my opinion, previously expressed on one of your other threads. You can't do it. At least not directly. If you do want to select choices for a DV list, then you need to assemble the "approved list" in another place as a contiguous set of "approved items" and use that for your DV.

It's just a "feature" of excel that has never been addressed.

Do you know how to do what I have suggested?

[jaryszek]

Glenn,

thank you very much.
I want to have named range - not DV list for it. So named range with array from dynamic table. But still if you can not do this at once - it is ok.

Please help with this helper column (the easiest way),
still i am not feeling these array formulas.

Best,
Jacek

[Glenn Kennedy]

Not sure if I follow you... What do you want to do with the array if/when you have it??

[jaryszek]

Hi,

i want to get 2 separated lists into order to assign them into named range:

Screenshot_17.png

Thank you,
Best,
Jacek

[Glenn Kennedy]

Yes... but. I asked what you wanted TO DO with the named range... You didn't answer.

A guess. With No and Yes in F1 and G1:

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW(t_MaxMemory[[Environment]:[Environment]])/(t_MaxMemory[[Environment]:[Environment]]=F$1),ROWS(F$2:F2))),"")

coped across and down. Then use this for the named range (similar for column G):

=OFFSET(Sheet1!$F$2,,,SUMPRODUCT(--(LEN(Sheet1!$F$2:$F$11)>0)))

[jaryszek]

A sorry Glenn,

and thank you. I want to use this within formula. To have vlookup within named range.

Ok,
a little explanation here:

this sum product: how this is working here? what is "--" as array?
And why values greater then 0 is only summed?

And about aggregations function.

How this is working step by step in details?:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

?

thank you,
Best,
Jacek

[Glenn Kennedy]

ROW(t_MaxMemory[[Environment]:[Environment]]) return the row number where the criterion is met.

(t_MaxMemory[[Environment]:[Environment]]=F$1) this is the criterion. where t_MaxMemory[Environment] is "No"

The doubling up of the [Environment] bit has the same function as the $ in normal formulae: locks the column.

ROWS(F$2:F2) a counter, starts at 1 and increments by 1 per row. It feeds into INDEX($b:B,AGGREGATE(15,6... which returns the values from column B that meet the yes/No criterion in ascending row number order.

-- coerces the numerical responses from the LEN bit into TRUE or FALSE.

[jaryszek]

oo thank you very much!

awesome you explained it very well.

ROW(t_MaxMemory[[Environment]:[Environment]]) return the row number where the criterion is met.

one more thing.
So to find criterion you are using "/" sign? how this is working?

Best,
Jacek

[Glenn Kennedy]

Yes. / works the same way as in LOOKUP.... The criteria begin here...

[jaryszek]

Hi Guys,

i have to refresh topic.

I moved table in another place in workbook and now i have error.

I have to locate Row number for table and use it somehow with this aggregation function.

I do not know how this aggregatio nfunction works when you have table in A1 cell.
YOu have row {1,2,3} there - how this is working ?

This function:

Please Login or Register  to view this content.

when table is moved is not working.

Please help,
Best,
Jacek

[Glenn Kennedy]

The results (F2:G11) seem fine to me. However if you are referring to the error message in K9.... then you will need to explain what you HOPED it would do. I can't tell what your intention was....

[jaryszek]

Hi Glenn,

i created new Sample.

In column M there is a formula which i created to create DV list:

Please Login or Register  to view this content.

what is expected result?

To have Data Validation list with this formula. To see within DV only

Please Login or Register  to view this content.

without creating helper columns.

I created with this formula named ranged and tried to use it within DV list but i have an error (during calculations there is an error).

Please help,
Jacek

[rorya]

In a named formula, INDIRECT needs a sheet name.

Edit: actually, that's not necessary if you are using it as a data validation list. It is necessary for a chart source. Ignore me.

[Glenn Kennedy]

Please post a sheet with the formula in place. I can't even get it to produce an error (; and , issues, I think).

[jaryszek]

Glen,

you have formula in M19 cell, sheet1. And Also as named ranged CheckingTest.

What i need i want to have array of list as i wrote,

Best,
Jacek

[Glenn Kennedy]

So. I am going in ever decreasing circles trying to figure out EXACTLY what you want. L15 (my formula) and L17 (your adaptation of it) BOTH produce and an array of ={4444;5555;7777}....

See sheet.

[jaryszek]

Hi Glenn,

thank you.

Your formula is working only as my formula - on sheet. Try to move it to DV list...(as named ranged for example).
You will get an error.

So this want i achieve here - to input this formula into DV list and make it working...

Best,
Jacek

[Glenn Kennedy]

Re-read Post 23. You can't.

[jaryszek]

Ahh it is a pity...

Best,
Jacek