afmrubayat
New Member
- Joined
- Nov 29, 2009
- Messages
- 27
Hi Good Day!
How to use HLookup by date and combined use of large to find top 5 or N number with related data to another sheet.
or any other solution for following:
I want to retrieve Following Data from Sheet 1 to Sheet 2 based on date to be lookup, with top 20 with respective data.
When another date inserted in cell A1 of sheet 2 date will collect form Sheet 1 and Sheet 2 will be updated.
Sheet 1 will be like this with huge raw data:
<tbody>
[TD="colspan: 3"] 26-Aug-16 [/TD]
[TD="colspan: 3"] 27-Aug-16 [/TD]
[TD="colspan: 3"] 28-Aug-16 [/TD]
[TD="colspan: 3"] 29-Aug-16 [/TD]
[TD="colspan: 3"] 30-Aug-16 [/TD]
[TD="colspan: 3"] 31-Aug-16 [/TD]
</tbody>
Sheet 2 will be like this
Based Sheet 1 data cell C1 date to be lookup, with top 3 with respective data in A2:E5
<tbody>
</tbody>
Thanks in advance for your kind support.
Regards,
Rubayat
How to use HLookup by date and combined use of large to find top 5 or N number with related data to another sheet.
or any other solution for following:
I want to retrieve Following Data from Sheet 1 to Sheet 2 based on date to be lookup, with top 20 with respective data.
When another date inserted in cell A1 of sheet 2 date will collect form Sheet 1 and Sheet 2 will be updated.
Sheet 1 will be like this with huge raw data:
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |
1 | BOID | BO Short Name | ||||||||||||||||||
2 | Credits | Debits | Movement | Credits | Debits | Movement | Credits | Debits | Movement | Credits | Debits | Movement | Credits | Debits | Movement | Credits | Debits | Movement | ||
3 | 1201950000015747 | EBL Securities Limited | 1804 | 1800 | 649 | 100 | 0 | 100 | 200 | 2200 | 200 | 1800 | 1000 | 1800 | 500 | 0 | 500 | 1000 | 2100 | 1000 |
4 | 1205720053777844 | NLI Securities Limited | 0 | 4 | 0 | 2000 | 0 | 2000 | 200 | 500 | 200 | 1000 | 0 | 1000 | 56650 | 72650 | 2000 | 2100 | 0 | 2100 |
5 | 1203490006793073 | Shanta Securities Limited | 1000 | 0 | 1000 | 1600 | 19325 | 17575 | 1000 | 0 | 1000 | 2220 | 100 | 2220 | 1000 | 0 | 1000 | 100 | 100 | 100 |
6 | 1204220018014574 | International Leasing Sec. Ltd | 800 | 0 | 800 | 50 | 0 | 50 | 5000 | 0 | 5000 | 300 | 0 | 300 | 2000 | 0 | 5000 | 200 | 0 | 100 |
7 | 1204480020376044 | IIDFC Securities Limited | 100 | 0 | 100 | 500 | 0 | 300 | 5000 | 0 | 5000 | 16000 | 63800 | 16000 | 0 | 2050 | 10925 | 2000 | 1540 | 3040 |
8 | 1205660050208183 | Trust Bank Securities Limited | 0 | 100 | 100 | 0 | 7000 | 0 | 0 | 20000 | 0 | 17018 | 0 | 17018 | 2300 | 2900 | 600 | 1000 | 0 | 500 |
9 | 1204490020697475 | Bank Asia Securities Limited | 0 | 398059 | 2000 | 0 | 3975 | 0 | 3000 | 0 | 3000 | 0 | 3000 | 0 | 2000 | 0 | 2000 | 10925 | 0 | 10925 |
10 | 1205050043090289 | A. K. Khan Securities Limited | 3802 | 302 | 18500 | 7000 | 0 | 3000 | 1100 | 0 | 1100 | 5400 | 11675 | 5400 | 200 | 100 | 100 | 600 | 756 | 500 |
<tbody>
[TD="colspan: 3"] 26-Aug-16 [/TD]
[TD="colspan: 3"] 27-Aug-16 [/TD]
[TD="colspan: 3"] 28-Aug-16 [/TD]
[TD="colspan: 3"] 29-Aug-16 [/TD]
[TD="colspan: 3"] 30-Aug-16 [/TD]
[TD="colspan: 3"] 31-Aug-16 [/TD]
</tbody>
Sheet 2 will be like this
Based Sheet 1 data cell C1 date to be lookup, with top 3 with respective data in A2:E5
A | B | C | D | E | F | G | H | I | |
1 | 31-Aug-16 | ||||||||
2 | Sl. No. | BO ID | BO Name | No. of shares | |||||
3 | 1 | 1204490020697470 | Bank Asia Securities Limited | 10925 | |||||
4 | 2 | 1205720053777840 | NLI Securities Limited | 2100 | |||||
5 | 3 | 1204480020376040 | IIDFC Securities Limited | 2000 |
<tbody>
</tbody>
Thanks in advance for your kind support.
Regards,
Rubayat