Kumar Amitouj jain
New Member
- Joined
- May 13, 2017
- Messages
- 1
Hi
I am a very basic user of excel, I am trying to extract data based on 3 conditions, tried to understand and did what ever was shown but still unable to get a solution any help would be highly appreciated.
Columns P,Q,R are search criterion 's' is total meeting that criterion
need to extract all data from left hand side meeting the search criterion ideally on sheet 2, but can't even make it work on same sheet.
Problem 1: it pulls up data but it is not the right one its always the one in row 2
PROBLEM2 : AND IF I try tocopy it in other cell it comes up with#num error
Dont know if i can attach a sample file but below is copy paste of what i am doing
this is the source data:
<colgroup><col><col span="2"><col><col><col><col span="4"><col><col><col></colgroup><tbody>
</tbody>
this is the search criterio:
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
This is the formula i tried using:
<colgroup><col width="113"></colgroup><tbody>
</tbody>
I always get this result only:
<colgroup><col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:5851;width:120pt" width="160"> <col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:3328;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <col style="width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="width:47pt" width="63" span="2"> <col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> </colgroup><tbody>
</tbody>
please help
I am a very basic user of excel, I am trying to extract data based on 3 conditions, tried to understand and did what ever was shown but still unable to get a solution any help would be highly appreciated.
Columns P,Q,R are search criterion 's' is total meeting that criterion
need to extract all data from left hand side meeting the search criterion ideally on sheet 2, but can't even make it work on same sheet.
Problem 1: it pulls up data but it is not the right one its always the one in row 2
PROBLEM2 : AND IF I try tocopy it in other cell it comes up with#num error
Dont know if i can attach a sample file but below is copy paste of what i am doing
this is the source data:
execution_Date | Frequency | Month | Ordering Customer | Start Date | End Date | Debiting_Ac | Beneficiary Name | Ben Sort | Ben Account | Amount | Ordering Ref | Ben Ref |
1 | ANNUALLY | January | abcd | 01/11/2013 | 01/11/2016 | 123456 | aaaaa | 123456 | 789456 | ANNUAL INT OF 60340766030002 | INTEREST | |
1 | MONTHLY | January | xyz | 03/05/2006 | UNTIL FURTHER NOTICE | 789456 | bbbbb | 456578 | 456123 | 1525 | DAVID K S TSE | FOR RENT |
1 | ANNUALLY | January | llllll | 03/11/2014 | UNTIL FURTHER NOTICE | 456789 | xxxxxx | 968978 | 147852 | 10 | SPONSAPOLL A/C | PZS14-217 |
<colgroup><col><col span="2"><col><col><col><col span="4"><col><col><col></colgroup><tbody>
</tbody>
this is the search criterio:
execution_Date | Month | Frequency | Count # that Pass all Hurdles |
1 | April | Monthly | 1 |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
This is the formula i tried using:
=IF(ROWS(P$5:P5)<=$S$2,INDEX(INDIRECT(P$4),SMALL(IF(execution_Date=$P$2,IF(Frequency=$Q$2,IF(Month=Q2,ROW(Frequency)-ROW($C$4)+1))),ROWS(P$4:P4))),"") |
<colgroup><col width="113"></colgroup><tbody>
</tbody>
I always get this result only:
execution_Date | Frequency | Ordering_Customer | Start_Date | End_Date | Debiting_Ac | Beneficiary_Name | Ben_Sort | Ben_Account | Amount | Ordering_Ref | Ben_Ref |
1 | MONTHLY | xyz | 38840 | UNTIL FURTHER NOTICE | 789456 | bbbbb | 456578 | 456123 | 1525 | DAVID K S TSE | FOR RENT |
<colgroup><col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:5851;width:120pt" width="160"> <col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:3328;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <col style="width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="width:47pt" width="63" span="2"> <col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> </colgroup><tbody>
</tbody>
please help