Multiple column match, and seek multiple output values from one column

English
, , , ,
#1

Please see the attached ods file with two sheets for the problem.

The problem is, when a name is selected in dropdown at B17 : ANLYS.ods.

Scenario 1:
To have all the corresponding STRIKE_PR of that particular name filled up in the column D beginning at D17 after matching OPTSTK, CE, 30-May-2019.

Scenario 2:
To have only STRIKE_PR of top three highest OPEN_INT corresponding to that particular name. filled up in the column L beginning at L17 after matching OPTSTK, CE, 30-May-2019. (OPEN_INT are in column H in yday sheet)

As you can see, I tried with INDEX and MATCH function, but it is showing only the first matched STRIKE_PR ignoring the rest. Perhaps it requires a different combination of functions.

I hope someone can help / guide me.
thanks

#2

On a first glance it looks as if you can assure that the matches run over a contiguous sequence of rows.
If so, you need to explicitly state this fact because it can simplify the solution very much.

#3

Your “Scenario 2” ignores the fact that there is no assurance about “the top three” being unambiguous.
To the extreme: 100 items having the same value of STRIKE_PR would be 100 items on rank 1 when looking for “the top three”.

#4

@Lupp, 100 items may have same STRIKE_PR but SYMBOL would be different.
That said, if you have a simpler solution please do let me / us know. The solution provided by @SM_Riga works and so I am marking this question as answered, but because of the large number of calculations involved, it is causing heavy load on CPU and freezing for a while. thank you.

#5

What I sketched may or may not be simpler. I didn’t study the already given solution, and in fact I won’t. This is obvivusly a database task, and any solution by spreadsheets will be a crook and probably not reliable enough or unsafe concerning data protection / security / safety, or…
However I sketched a kind of solution originally to demonstrate in what way the probable assurance mentioned in my first comment might simplify the task or, at least allow for a more efficient solution. I did not post that example because I didn’t get and answer concerning the assurance.
Since you asked for it explicitly I will now have to open a next-to-empty answer to be able to attach the demo.

#6

Hello, @Sai

Please also refer to this topic

What you are trying to achieve is “Filtering by formula” functionality. While it is technically possible to create such pseudo-filter, this is not the best option for such a task, cause you get just a view, but not truly filtered range with dynamic dimensions of result range - please see sample spreadsheet for Scenario 1. Calc offers native built-in tools for such tasks - Filter, Auto Filter, Advanced Filter. You can also use Pivot Table for data analysis. Maybe consider using database instead of spreadsheet, as your task is pure database query? See also an advanced topic combining spreadsheet and database, some knowledge of working with data sources, forms and database is a must for creating such spreadsheet.

—Edit 2019-05-31 ----

Another sample file for Advanced Filter + Pivot and formula for Top-3 results.

#7

thanks @SM_Riga for your suggested solution for Scenario-1. While your solution for Scenario-1 would be helpful, I am unable to reproduce the same result in my analysis spreadsheet. The sample file I uploaded is a shortened version of my analysis spreadsheet, so that it is within upload file size limit here at this website. Original raw data file contains about 50k rows and 15 columns. Can you please explain how did you do it, in very simple language, for me to replicate the same in my file.

As regards filters, yes, know them. But the challenge is, I have to do further analysis in this spreadsheet after fetching relevant data into other columns (not visible in the sample file), which may not be possible with filters.

#8

It is possible to copy filter result to another predefined position. On the large files with big amount of data such pseudo filtering is a no-go.What I can see from your description, your goal is a database task, so I advice you to consider using Base or Base+Calc. You can create a database from you source data, register it as a source for Calc spreadsheet and then use predefined queries to pass filtered data. More advanced topic (which I already mentioned in my answer) is to connect Base query string with Calc form, which will allow you to edit query on-the-go and return query result back to spreadsheet. Anyway, with some customization and probably a bit of lite coding you will also manage it all in Calc-only, As a starter, I have added another sample file which shows how Advanced filtering works combined with copying result to another destination. Change criteria in Criteria Table, apply Advanced filter and see how it works. Few lines of code and all is done by one mouse click.

#9

Anyway, if you decide to stick to filtering by formulas, I can try to explain step-by-step how sample formula works.

#10

Hi @SM_Riga, thanks for providing new ideas for analyzing sample data both with Advanced Filter + Pivot and Formula based methods.
(1) Have you observed that are the values in Pivot table not updating, based on AdvancedFilter criteria? while those based on formula are updating!
(2) These formulae are new to me. What does the criteria >0, >100, >0 mean / do ?

The reason I am not talking about Libreoffice-base is because of total ignorance about it. Although I am interested in it, if I have to do it through base, I have to start from basics of base. I get raw data file daily in csv format & just read online that csv files cannot be automatically imported into base. I also get few other files in csv format daily, which are linked to a spreadsheet & respective calculations/analysis done automatically. Now after your suggestion, even as I may learn base & may eventually migrate to base, if that serves the purpose better, for the present though I may have to get workdone in spreadsheets.

#11

Further, I think array formula was used for the Scenario-1, but as already mentioned before, I am not able to replicate it in my own spreadsheet despite repeated attempts (CSE done). Is there anything else that need to be done to get the array formula working for Scenario-1 ? Could you please share the step by step procedure for the same. thanks

#12

(1) Have you observed that are the
values in Pivot table not updating,
based on AdvancedFilter criteria?

Yes, of course. Right click on Pivot Table, then click Refresh The idea is to demonstrate different methods/approaches.

(2) These formulae are new to me. What
does the criteria >0, >100, >0 mean /
do ?

Just a sample to show that you can use comparison operators in the Filter Criteria table also.

Have you noticed that I used Named Ranges in formulas to make it easier to understand what cell range formula refers to? Do you enter =IF((INSTRUMENT=$D$11)*(OPTION_TYP=$D$13)*(EXPIRY_DT=$D$14)*(SYMBOL=$B$17);1;0) as an array formula?

#13

Yes, just seen the defined ranges. I didn’t notice this earlier. What is __Anonymous_Sheet_DB_0, __Anonymous_Sheet_DB_1? & Print Area ?
& yes, array formula was same as you mentioned.

#14

What is __Anonymous_Sheet_DB_0,
__Anonymous_Sheet_DB_1? & Print Area ?

These are not created/defined in my sample spreadsheet, so possibly the result of some of your actions. Did you manage to get the formula work in your original spreadsheet after adjusting Named Ranges references?

#15

Thanks for guiding me, I downloaded the file with your worked-out solution afresh and followed your procedure, initially to get the results in a exact copy of the sample file uploaded here (not yet in my main spreadsheet, the data / variables to consider in main spreadsheet is a little more). It worked! One small doubt. In the yday sheet there are some workings in columns I, J and K. Was it some side-calculation you did and forgot to delete or are they relevant ? I didn’t consider them in my workbook. I tried for a dropdown of the EXPIRY_DT in D14 with the various expiry dates (using data validity option). But that doesn’t seem to work even if I choose cell format as date.

#16

thank you @SM_Riga. With slight tinkering, I could get the D14 dropdown of EXPIRY_DT working.

#17

(Only in puruit of the recent comments on the question. Not claiming anything!)
announced attachment was not accepted due to file size.
See this (temporary?) link.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.