Mar 01 2021 01:47 AM
Hi,
I have a table and I am trying to get ALL values (duplicated or not) from a column in a different sheet based on criteria. The criteria are the highlighted columns in the first image below.
The raw date looks like the other image.
I also have attached the file in case it helps.
Many thanks before hand!
Mar 01 2021 02:00 AM
That could be
=FILTER(INDEX(Table1, SEQUENCE(ROWS(Table1)), IF($E$3="Time", {1,2}, {1,3})), COUNTIFS($C$3,Table1[CaseName]))
Mar 01 2021 02:17 AM
Thank you very much! That works as desired.
Just one little detail...
How should I tweak the formula in order to only show the values and not repeat the case name to the left of each number?
Thanks again beforehand!
Mar 01 2021 02:37 AM
Also, what if I had many more columns in he raw data and not just two?
Like the image below.
Mar 01 2021 02:57 AM
If you have Office 365 there is a FILTER function which I think will do what you want.
If you don't have 365 then see this youtube video which shows how to do this for older versions.
https://www.youtube.com/watch?v=fDB1Ktyhp3Y
Hope this helps!
regards,
Peter
Mar 01 2021 03:08 AM
If you have list of columns to select like this
formula could be
=FILTER(
INDEX(Table1,
SEQUENCE(ROWS(Table1)),
XMATCH( TRANSPOSE($E$3:INDEX($E$3:$E$10,COUNTA($E$3:$E$10))),
Table1[#Headers])),
COUNTIFS($C$3,Table1[CaseName])
)
Range E3:E10 is taken with some gap
Mar 01 2021 04:52 AM - edited Mar 01 2021 04:52 AM
Mar 01 2021 05:52 AM
@Jorglo , you are welcome, glad it helped.