• 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.

Populate conditional data (preferably without Macros)

skarnik01

Member
Hello,

I am looking to populate data with criteria into another sheet.
Conditions can be in AND / OR format

Attaching sample file with conditions.

I tried using Index, Match - but failed miserably. Please help. Possibly Row? Small functions?

Thanks in advance
 

Attachments

  • DataPopulation.xlsx
    9.5 KB · Views: 5
Is it chance that there is only one match? With Office 365
= FILTER( Source, (Source[Country]=Country)*(Source[Thing]=Thing)*(Source[Value]<MaxValue) )
would be able to provide multiple matches. A single match would involve XLOOKUP.

With older versions then it is more like
= IFERROR( INDEX( Source,
SMALL( IF( (Source[Country]=Country)*(Source[Thing]=Thing)*(Source[Value]<MaxValue), k ), k ),
{1,2,3,4,5} ), "" )

where the row index 'k' is defined to be
= ROW(Source) - ROW(Source[#Headers])

It is quite possible to revert the formulas to avoid tables and named formulas but that is not something I would be willing to do.
 
Is it chance that there is only one match? With Office 365
= FILTER( Source, (Source[Country]=Country)*(Source[Thing]=Thing)*(Source[Value]<MaxValue) )
would be able to provide multiple matches. A single match would involve XLOOKUP.

With older versions then it is more like
= IFERROR( INDEX( Source,
SMALL( IF( (Source[Country]=Country)*(Source[Thing]=Thing)*(Source[Value]<MaxValue), k ), k ),
{1,2,3,4,5} ), "" )

where the row index 'k' is defined to be
= ROW(Source) - ROW(Source[#Headers])

It is quite possible to revert the formulas to avoid tables and named formulas but that is not something I would be willing to do.
Hello,

Thank you for your reply. I am using MS Office Excel 13.

I did try the following formula - please guide
=IFERROR( INDEX( 'Primary Data'!A1:E16,
SMALL( IF( ('Primary Data'!B:B=IND)*('Primary Data'!D:D=CR)*('Primary Data'!E:E<30), ROW('Primary Data'!A1:E16) - ROW('Primary Data'!A1),
ROW('Primary Data'!A1:E16) - ROW('Primary Data'!A1)),
{1,2,3,4,5} ),0), "" )
I am missing something here. I tried entering into the cell, got empty values.
 

Attachments

  • DataPopulation - Copy.xlsx
    11.2 KB · Views: 2
I do not think I am the right person to help you since our ways of using Excel are so different (for example the use of entire column references meant that I had 1048576 Boolean values to check during the formula evaluation). The first problem I encountered was the lack of names to hold the values such as "IND" and "CR", then the index generated was zero-based rather than 1. CSE entered,
= IFERROR( INDEX( 'Primary Data'!A2:E16, SMALL( IF( ('Primary Data'!B2:B16="IND")*('Primary Data'!D2:D16="CR")*('Primary Data'!E2:E16<30), ROW('Primary Data'!A2:E16) - ROW('Primary Data'!A1:E1)), ROW('Primary Data'!A2:E16) - ROW('Primary Data'!A1:E1)), {1,2,3,4,5} ), "" )
does work but its not for me.
 
Primarily it will be OR criteria.
Unfortunately, whilst I can do that with the new dynamic array functions, I can't do it for Xl2013.
If they were all And criteria, you could also use
=IFERROR(INDEX('Primary Data'!A$2:A$16,AGGREGATE(15,6,(ROW('Primary Data'!$B$2:$B$16)-ROW('Primary Data'!$B$2)+1)/(('Primary Data'!$B$2:$B$16="IND")*('Primary Data'!$D$2:$D$16="CR")*('Primary Data'!$E$2:$E$16<50)),ROWS(B$11:B11))),"")
 
I do not think I am the right person to help you since our ways of using Excel are so different (for example the use of entire column references meant that I had 1048576 Boolean values to check during the formula evaluation). The first problem I encountered was the lack of names to hold the values such as "IND" and "CR", then the index generated was zero-based rather than 1. CSE entered,
= IFERROR( INDEX( 'Primary Data'!A2:E16, SMALL( IF( ('Primary Data'!B2:B16="IND")*('Primary Data'!D2:D16="CR")*('Primary Data'!E2:E16<30), ROW('Primary Data'!A2:E16) - ROW('Primary Data'!A1:E1)), ROW('Primary Data'!A2:E16) - ROW('Primary Data'!A1:E1)), {1,2,3,4,5} ), "" )
does work but its not for me.
Thank you - it works. One last question though (basic one) -
How do I have it work for multiple records (I am weaker in usage of Rows). Attaching the updated excel with additional records. For my actual file, I may require populating around 50 records.
Also, how will I ensure that if there are 30 eligible records, then rightly 30 lines of eligible rows are populated? Thanks again
 

Attachments

  • DataPopulation - Copy2.xlsx
    10.6 KB · Views: 4
In B12 filled down and across
=IFERROR(INDEX('Primary Data'!A$2:A$16,AGGREGATE(15,6,(ROW('Primary Data'!$B$2:$B$16)-ROW('Primary Data'!$B$2)+1)/(('Primary Data'!$B$2:$B$16=$E$7)*('Primary Data'!$D$2:$D$16=$E$8)*('Primary Data'!$E$2:$E$16<$E$9)),ROWS(B$11:B11))),"")
 
In B12 filled down and across
=IFERROR(INDEX('Primary Data'!A$2:A$16,AGGREGATE(15,6,(ROW('Primary Data'!$B$2:$B$16)-ROW('Primary Data'!$B$2)+1)/(('Primary Data'!$B$2:$B$16=$E$7)*('Primary Data'!$D$2:$D$16=$E$8)*('Primary Data'!$E$2:$E$16<$E$9)),ROWS(B$11:B11))),"")
It works - thanks a lot !
 
Back
Top