• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract unique values using Index/Small/Row/IF from multiple sheets using Indirect

V

Valentyn Tofel

New Member
Hello.

Having a problem to Extract unique values using Index/Small/Row/IF for multiple sheets using Indirect.

In one of forums I found a formula which can extract data from 1 specific sheet, while, I need to have a possibility to extract data:
1. From different sheets after modifying A2 Cell (Drop Down)
2. Columns locations are different in every sheet
3. 2 of criteria are dynamic (see cells C9 and C11 in "Unique List" sheet)

Additional requirements:
1. Unique Location ID should be sorted as ascending.
2. Would be good to have both options of using "array formula" and without it.
3. Function should be able to be adjustable to increase or decrease criteria number.
4. Blanks in Cells A3, A5 and A7 should mean that these columns data should be excluded from conditions list (all data included in the summary list).

Current function used in Cell A14 is:
{=INDEX(Build!$A$1:$A$352,SMALL(IF(((Build!$C$1:$C$352=$A$3)*(Build!$D$1:$D$352=$A$5)*(Build!$E$1:$E$352=$A$7)*(Build!$F$1:$F$352>$A$9)*(Build!$G$1:$G$352=$A$11)),ROW(Build!$A$1:$A$352)),ROW()-13),1)}

Thank you for your support in advance!
 

Attachments

  • Chandoo.xlsb
    32.1 KB · Views: 12
Why not use Excel Table with slicers OR if you really want to use Array formulas, use Table's Structured References after you've made your data set into a Table?
A lot of folks here are happy creating complex formulas to extract data - I prefer to spend time developing good data structure so that one can simplify the extraction process. I guess it's different strokes for different folks!
 
Hello David.
That is the thing, I cannot convert my excel data into Excel Tables. Referenced data is erased and re-uploaded into the file from many other files every day. I use this file for consolidation purposes, first of all. Second task for the file is to generate necessary summaries. Formula requested will become part of other complex set of formulas in one of the summary sheets.
Thanks and
BR
Valentyn
 
Valentyn Tofel said:
Hello David.
That is the thing, I cannot convert my excel data into Excel Tables. Referenced data is erased and re-uploaded into the file from many other files every day. I use this file for consolidation purposes, first of all. Second task for the file is to generate necessary summaries. Formula requested will become part of other complex set of formulas in one of the summary sheets.
Thanks and
BR
Valentyn
Click to expand...
How are you bringing in the daily data? Are you using Power Query or Power Pivot?
 
Hello David.

I am taking data from multiple CSV files with help of VBA:
Step 1: clear all data with VBA code in consolidated tables (Build, Lease, etc. up to 10 more additional sheets).
Step 2: Paste data with VBA code from multiple CSV files.
Step 3: All data sheets (Build, Lease, etc. up to 10 more additional sheets) are modified with additional VBA code which adds columns with new content.

As a result before summary tables are recalculated structure of every data sheet is changed. For this reason I need Indirect Function in above requested formula.

Thanks and
BR
Valentyn
 
You must log in or register to reply here.
Back
Top