Extract Multiple Rows (Automated) based on 3 Criteria

[rahul451]

Hi,

I have data and want to pull the rows to next sheet if certain criteria is met.
Criteria:
1: Name should match is 13th Column
2: Profit should be less than 5000
3: GMV should be less than 8000

Example: I want to pull all the rows where Name (Col 13) is I****a, Profit (Col 11) is less than 5000 and GMV (Col 7) less than 8000.

Data is below:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 170px"><col width="170"><col width="100"><col width="100"><col width="158"><col width="100"><col width="100"><col width="154"><col width="110"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]Artist Name[/TD]
[TD]Venue[/TD]
[TD]Company Name[/TD]
[TD]Act[/TD]
[TD]Amount without Tax[/TD]
[TD]Recieved Status[/TD]
[TD]Artist Cost without TDS[/TD]
[TD]Payment Status (Artist)[/TD]
[TD]Profit[/TD]
[TD]event confirmation[/TD]
[TD]EM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD]Shahbaz Khan[/TD]
[TD]Pride Plaza[/TD]
[TD]Pride Plaza Hotel[/TD]
[TD]Violin Player[/TD]
[TD="align: right"]5,000[/TD]
[TD]Received on 5th June,17[/TD]
[TD="align: right"]4,500[/TD]
[TD]Paid[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]20:00[/TD]
[TD]Supriya[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/3/2017[/TD]
[TD]Vaani Duo[/TD]
[TD]Nukkad Cafe[/TD]
[TD]Flying Sparrow[/TD]
[TD]Sufi Duo[/TD]
[TD="align: right"]10,000[/TD]
[TD]As per Ledger[/TD]
[TD="align: right"]8,000[/TD]
[TD]Paid on 4th May,17[/TD]
[TD="align: right"]2,000[/TD]
[TD="align: right"]20:00[/TD]
[TD]I****a[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/3/2017[/TD]
[TD]Aadil Khan[/TD]
[TD]Nukkad Cafe[/TD]
[TD]Flying Sparrow[/TD]
[TD]Sufi Solo[/TD]
[TD="align: right"]6,383[/TD]
[TD]As per Ledger[/TD]
[TD="align: right"]4,000[/TD]
[TD]Paid[/TD]
[TD="align: right"]2,383[/TD]
[TD="align: right"]20:00[/TD]
[TD]I****a[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/4/2017[/TD]
[TD]Kunal[/TD]
[TD]Pride Plaza[/TD]
[TD]Pride Plaza Hotel[/TD]
[TD]Accordion[/TD]
[TD="align: right"]5,000[/TD]
[TD]Received on 5th June,17[/TD]
[TD="align: right"]5,000[/TD]
[TD]Paid[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]Supriya[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/6/2017[/TD]
[TD]Gautam[/TD]
[TD]Pride Plaza[/TD]
[TD]Pride Plaza Hotel[/TD]
[TD]Flute/Santoor[/TD]
[TD="align: right"]5,000[/TD]
[TD]Received on 5th June,17[/TD]
[TD="align: right"]4,500[/TD]
[TD]Paid[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD]Supriya[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/7/2017[/TD]
[TD]Shahbaz Khan[/TD]
[TD]Pride Plaza[/TD]
[TD]Pride Plaza Hotel[/TD]
[TD]Violin Player[/TD]
[TD="align: right"]5,000[/TD]
[TD]Received on 5th June,17[/TD]
[TD="align: right"]4,500[/TD]
[TD]Paid[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD]Supriya[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/8/2017[/TD]
[TD][/TD]
[TD]Pride Plaza[/TD]
[TD]Pride Plaza Hotel[/TD]
[TD]Accordion[/TD]
[TD="align: right"]5,000[/TD]
[TD]Received on 5th June,17[/TD]
[TD="align: right"]5,000[/TD]
[TD]Paid[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]Supriya[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/8/2017[/TD]
[TD]Aadil Khan[/TD]
[TD]Reset[/TD]
[TD]Imperial Foods Pvt. Ltd.[/TD]
[TD]Sufi Solo[/TD]
[TD="align: right"]6,000[/TD]
[TD]6000 Received and 300 pending after 10% TDS[/TD]
[TD="align: right"]5,000[/TD]
[TD]Paid[/TD]
[TD="align: right"]1,000[/TD]
[TD][/TD]
[TD]I****a[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/10/2017[/TD]
[TD]Kunal[/TD]
[TD]Nukkad Cafe[/TD]
[TD]Flying Sparrow[/TD]
[TD]Sufi Duo[/TD]
[TD="align: right"]10,000[/TD]
[TD]As per Ledger[/TD]
[TD="align: right"]8,000[/TD]
[TD]Paid on 4th May,17[/TD]
[TD="align: right"]2,000[/TD]
[TD][/TD]
[TD]I****a[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/12/2017[/TD]
[TD]Aadil Khan[/TD]
[TD]Ardor[/TD]
[TD]Bon Vivant Lifestyle Pvt Ltd[/TD]
[TD]Sufi Solo[/TD]
[TD="align: right"]5,000[/TD]
[TD]11270 Received for 2 performances after 2%TDS[/TD]
[TD="align: right"]4,000[/TD]
[TD]Paid[/TD]
[TD="align: right"]1,000[/TD]
[TD][/TD]
[TD]Supriya[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/12/2017[/TD]
[TD]Street Jammers[/TD]
[TD]Flying Saucer[/TD]
[TD]WG Hospitality[/TD]
[TD]Sufi Band[/TD]
[TD="align: right"]35,000[/TD]
[TD]36750 Received after TDS - 3500[/TD]
[TD="align: right"]30000[/TD]
[TD][/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[TD]I****a[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/12/2017[/TD]
[TD]Sound Street Jammers[/TD]
[TD]Flying Saucer[/TD]
[TD]WG Hospitality[/TD]
[TD]Sound Vendor[/TD]
[TD="align: right"]20,000[/TD]
[TD]Received[/TD]
[TD="align: right"]18,000[/TD]
[TD]Paid[/TD]
[TD="align: right"]2,000[/TD]
[TD][/TD]
[TD]I****a[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/12/2017[/TD]
[TD]Apratim Mallya[/TD]
[TD]Masterpiece[/TD]
[TD]Invictus Hospitality Pvt. Ltd[/TD]
[TD]Sufi Solo[/TD]
[TD="align: right"]3,600[/TD]
[TD]Cash Received on 5th may,17[/TD]
[TD="align: right"]4,000[/TD]
[TD]Paid on 4th May,17[/TD]
[TD="align: right"]-400[/TD]
[TD][/TD]
[TD]I****a[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/12/2017[/TD]
[TD]Akhil R Pillai[/TD]
[TD]Nukkad Cafe[/TD]
[TD]Flying Sparrow[/TD]
[TD]Sufi Solo[/TD]
[TD="align: right"]6,383[/TD]
[TD]As per Ledger[/TD]
[TD="align: right"]4,500[/TD]
[TD]Paid on 1st May,17[/TD]
[TD="align: right"]1,883[/TD]
[TD][/TD]
[TD]I****a[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/12/2017[/TD]
[TD]Shahbaz Khan[/TD]
[TD]Pride Plaza[/TD]
[TD]Pride Plaza Hotel[/TD]
[TD]Violin Player[/TD]
[TD="align: right"]5,000[/TD]
[TD]Received on 5th June,17[/TD]
[TD="align: right"]4,500[/TD]
[TD]Paid[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD]Supriya[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/13/2017[/TD]
[TD]Gautam[/TD]
[TD]Pride Plaza[/TD]
[TD]Pride Plaza Hotel[/TD]
[TD]Flute/Santoor[/TD]
[TD="align: right"]5,000[/TD]
[TD]Received on 5th June,17[/TD]
[TD="align: right"]4,500[/TD]
[TD]Paid[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD]Supriya[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]4/14/2017[/TD]
[TD]Vaani Duo[/TD]
[TD]Uncultured[/TD]
[TD][/TD]
[TD]Sufi Duo[/TD]
[TD="align: right"]9,000[/TD]
[TD]8500 Received after adjustment of 500 advance[/TD]
[TD="align: right"]7,500[/TD]
[TD]Paid on 1st May,17[/TD]
[TD="align: right"]1,000[/TD]
[TD][/TD]
[TD]Supriya[/TD]
[/TR]
</tbody>[/TABLE]

 

[Vikas Kumar]

Hi,


Considering Row1 of Sheet2 contain header as Sheet1. Then put this formula in A2 and hit ctrl+shift+enter simultaneously,


=IFERROR(INDEX(Sheet1!$A$1:$M$18,SMALL(IF((Sheet1!$M$1:$M$18="I****a")*(Sheet1!$K$1:$K$18<5000)*(Sheet1!$G$1:$G$18<8000),ROW(Sheet1!$A$1:$A$18)),ROW(1:1)),MATCH(Sheet2!A$1,Sheet1!$A$1:$M$1,0)),"")

Now copy across and down.

Sample Image - https://ibb.co/no48AG

 

[rahul451]

Thank You !!
Could you please help me understand the logic behind this.

 

[rahul451]

There is a slight issue with this. The current formula returns a row if all the 3 criteria are met. I want the formula to check for criteria 2 or 3. Both 2 & 3 shouldn't be true together.
Example it first looks for 1st criteria i.e Name and then looks for criteria 2 or 3.

 

[Vikas Kumar]

Could you please help me understand the logic behind this.

Here at post #5 is a well explained description of formula:

https://chandoo.org/forum/threads/array-formula-with-index-if-small-and-row-functions.8754/

Please let me know if still something is unclear.

 

[Vikas Kumar]

There is a slight issue with this. The current formula returns a row if all the 3 criteria are met. I want the formula to check for criteria 2 or 3. Both 2 & 3 shouldn't be true together.
Example it first looks for 1st criteria i.e Name and then looks for criteria 2 or 3.

Please give a try to this:

=IFERROR(INDEX(Sheet1!$A$1:$M$18,SMALL(IF((Sheet1!$M$1:$M$18="I****a")*((Sheet1!$K$1:$K$18<5000)+(Sheet1!$G$1:$G$18<8000)),ROW(Sheet1!$A$1:$A$18)),ROW(1:1)),MATCH(Sheet2!A$1,Sheet1!$A$1:$M$1,0)),"")

Note: Still array processing, So use with ctrl+shift+enter.

Here a * works like AND & + does the job of OR function.