Implement a subset from a table using formulas on a cell criteria

[BMarin]

Hi,
I am new here and I do not quite know if I should label myself as an advanced user or a beginner.
I programmed a lot in Excel, i.e. using VBA (but this is longtime ago), however I did not use too much Excel functions, therefore I am a little bit challenged when using formulas in Excel.
What I would like to know is if there exists any function that obtains a subset of a table (here A:2,B:10), let's say like the one from the below screen shot:



, and I would like to get a subset of this table on another sheet having just the lines that contain in the first column the Criteria that will be specified in the cell C:2.
I cannot use macros, so would this be possible using formulas?

Thank you very much,
BMarin

 

[Luke M]

Hi BMarin, and welcome to the forum.
Check out my tutorial I wrote a bit ago, which does almost exactly what you illustrate.
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[BMarin]

Hi BMarin, and welcome to the forum.
Check out my tutorial I wrote a bit ago, which does almost exactly what you illustrate.
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

Thank you very much Luke,
I copied the formula you suggested in your tutorial, and I adapted it in my situation, and now it works.
The issue is that for some reason, the instance of Excel I am using is not supporting the ROWS formula (I do not quite know why), and I would need a solution w/o ROWS. I will see what I can use instead of that.
Another question would be, because the table I am using is over 40000 rows, and the application I use it into is not working so optimal with that amount of rows, would it be possible to use the table external to the application, and use a formula like SQL.REQUEST? Or something similar?

Have a great day,
BMarin

 

[Luke M]

Weird...I've never heard of that issue w/ ROWS. For curiousity, what version are you using?

With that large a table, you're right, using formulas is probably not the best route to go. I'd probably switch to using Adv.Filter then. AdvFilter works near instantaneous, and is better suited for large data. Plus, it can copy the filtered data to a new location. To make it automatic, we can use a little VB. Take a look at Debra's article and example here:
http://www.contextures.com/exceladvancedfiltervba.html

and see if this looks more doable with your data?

 

[BMarin]

Hi Luke,

It is actually happening in a product that is using Excel es communication tool so to say.
It is a product of SAP called Dashboards, and not all functions in Excel are supported, however this one they were saying in their release notes that it is supported, however...
Anyway, I need to do that thing w/o ROWS somehow, or I have to find a way of using the spreadsheet externally and obtain on the spreadsheet in Dashboards a subset of it based on a criteria.
I saw the function SQL_Request, however it works only for all other types of databases than Microsoft (Excel, Access).
Do you happen to know of such a tool, or procedure?
Thank you so much,

BMarin

 

[Luke M]

Ah, that makes a little more sense. Does your tool support ROW function? If it's just ROWS (with an "s") that causes issue, we can change formula to be like:
=IF(COUNTIF(A:A,$D$2)<ROW($E1),....)

 

[BMarin]

Yes, for the time being it is just ROWS that is throwing an error message, so your solution should work.
I will give it a shot and will let you know, OK?
Thank you so much.

 

[BMarin]

Unfortunately, I modified the formula as you said, however, for some reason that function, i.e. ROW threw also an error message of no support.
I will check with SAP why it is not supported though it said it should be.
Thank you Luke.

 

[BMarin]

Weird...I've never heard of that issue w/ ROWS. For curiousity, what version are you using?

With that large a table, you're right, using formulas is probably not the best route to go. I'd probably switch to using Adv.Filter then. AdvFilter works near instantaneous, and is better suited for large data. Plus, it can copy the filtered data to a new location. To make it automatic, we can use a little VB. Take a look at Debra's article and example here:
http://www.contextures.com/exceladvancedfiltervba.html

and see if this looks more doable with your data?

Actually, in Dashboards I cannot use VB, only functions, and not all of them are supported, so I am a little bit limited here.
I will try and see how I could use the big spreadsheet with all calculations externally, and get in Dashboard only the subset calculated with your formula.
Thank you.

 

[BMarin]

Weird...I've never heard of that issue w/ ROWS. For curiousity, what version are you using?

With that large a table, you're right, using formulas is probably not the best route to go. I'd probably switch to using Adv.Filter then. AdvFilter works near instantaneous, and is better suited for large data. Plus, it can copy the filtered data to a new location. To make it automatic, we can use a little VB. Take a look at Debra's article and example here:
http://www.contextures.com/exceladvancedfiltervba.html

and see if this looks more doable with your data?

I forgot to ask you Luke, is Adv.Filter a function in Excel or is it a VBA function?
I was looking for a filtering function, and though there is a function for filtering in Excel (Filter menu), one cannot use it dynamically, i.e. based on the content of a cell that is changing.
Thank you.

 

[Luke M]

You are exactly correct. The manual AdvFilter is under Data, usually here:


But, for your setup you were wanting something more instantaneous, so I was thinking of either using a "click a button" type macro, or an event macro, that would automatically run the Advanced Filter. You can "get" the code you would need by recording yourself manually running the filter.