afmrubayat
New Member
- Joined
- Nov 29, 2009
- Messages
- 27
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>
</tbody>
Sheet 2 will be like this
<tbody>
</tbody>
<tbody>
</tbody>
Please help ASAP.
Regards,
Rubayat
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:
BOID | BO Short Name | 28-Aug-16 | % Free Float | 29-Aug-16 | % Free Float | 31-Aug-16 | % Free Float | 1-Sep-16 | % Free Float | ||||||||
Credits | Debits | Movement | Credits | Debits | Movement | Credits | Debits | Movement | Credits | Debits | Movement | ||||||
1201950000015747 | EBL Securities Limited | 1,011,256 | 1,289,035 | (277,779) | -0.274% | 1,014,333 | 1,044,463 | (30,130) | -0.030% | 651,708 | 764,333 | (112,625) | -0.111% | 533,500 | 783,651 | (250,151) | -0.247% |
1205720053777844 | NLI Securities Limited | - | - | - | 0.000% | - | 100,000 | (100,000) | -0.099% | - | - | - | 0.000% | - | - | - | 0.000% |
1203490006793073 | Shanta Securities Limited | - | - | - | 0.000% | 250,000 | 90,000 | 160,000 | 0.158% | 113,600 | 250,000 | (136,400) | -0.135% | 56,180 | 19,000 | 37,180 | 0.037% |
1204220018014574 | International Leasing Sec. Ltd | - | - | - | 0.000% | 56,967 | 78,767 | (21,800) | -0.021% | 14,200 | 67,267 | (53,067) | -0.052% | 36,230 | 64,600 | (28,370) | -0.028% |
1204480020376044 | IIDFC Securities Limited | - | - | - | 0.000% | 700 | 49,805 | (49,105) | -0.048% | 35,000 | 25,700 | 9,300 | 0.009% | 14,000 | 14,305 | (305) | 0.000% |
1205660050208183 | Trust Bank Securities Limited | - | - | - | 0.000% | 1,000 | 40,550 | (39,550) | -0.039% | 1,000 | 1,000 | - | 0.000% | - | 100,000 | (100,000) | -0.099% |
1204490020697475 | Bank Asia Securities Limited | 20,850 | 9,920 | 10,930 | 0.011% | 30,572 | 47,146 | (16,574) | -0.016% | 5,000 | 25,572 | (20,572) | -0.020% | 6,345 | 4,035 | 2,310 | 0.002% |
1205050043090289 | A. K. Khan Securities Limited | 5,400 | 1,000 | 4,400 | 0.004% | 6,500 | 34,000 | (27,500) | -0.027% | - | 6,500 | (6,500) | -0.006% | - | 1,000 | (1,000) | -0.001% |
1204090016161981 | SJIB SECURITIES LTD. | 500 | 12,303 | (11,803) | -0.012% | - | 26,085 | (26,085) | -0.026% | 4,000 | 2,000 | 2,000 | 0.002% | 5,931 | 5,500 | 431 | 0.000% |
1201690000001163 | SAR Securities Limited | 18,300 | 15,000 | 3,300 | 0.003% | 13,050 | 28,861 | (15,811) | -0.016% | 15,300 | 5,750 | 9,550 | 0.009% | - | 10,000 | (10,000) | -0.010% |
1203680008078187 | IDLC SECURITIES LIMITED | 17,407 | 7,986 | 9,421 | 0.009% | 32,472 | 30,575 | 1,897 | 0.002% | 13,610 | 25,972 | (12,362) | -0.012% | 5,720 | 10,300 | (4,580) | -0.005% |
1604620060706290 | SEML LECTURE EQUITY MGMT FUND | - | 94,000 | (94,000) | -0.093% | - | 21,354 | (21,354) | -0.021% | - | - | - | 0.000% | - | - | - | 0.000% |
1201520000000854 | Parkway Securities Limited | 3,802 | 302 | 3,500 | 0.003% | 2,100 | 15,000 | (12,900) | -0.013% | 500 | 2,100 | (1,600) | -0.002% | 2,300 | 2,900 | (600) | -0.001% |
1205590048601013 | UCB Capital Management Limited | 29,000 | - | 29,000 | 0.029% | 1,000 | 13,000 | (12,000) | -0.012% | 1,000 | 2,000 | (1,000) | -0.001% | 1,000 | - | 1,000 | 0.001% |
1203410006442288 | Phoenix Securities Limited | 4,016 | 916 | 3,100 | 0.003% | 916 | 12,650 | (11,734) | -0.012% | 960 | 1,876 | (916) | -0.001% | 2,000 | 2,000 | - | 0.000% |
1201830000007805 | LANKABANGLA Securities, Dhaka | 2,924 | 1,590 | 1,334 | 0.001% | 100,810 | 67,249 | 33,561 | 0.033% | 26,000 | 48,310 | (22,310) | -0.022% | 31,300 | 45,651 | (14,351) | -0.014% |
<tbody>
</tbody>
Sheet 2 will be like this
Thursday, September 01, 2016 |
<tbody>
</tbody>
Sl. No. | BO ID | BO Name | No. of shares | |
1 | 1601970058396610 | ICB Securities Trading Co. | 1,423,666 | |
2 | 1201530000003501 | ICB | 1,371,366 | |
3 | 1201950000015755 | EBL Securities Limited | 660,000 | |
4 | 1201950043531229 | EBL SECURITIES LTD | 660,000 | |
5 | 1201950000015747 | EBL Securities Limited | 533,500 | |
6 | 1201820000005723 | BRAC EPL Stock Brokerage Ltd. | 308,608 | |
7 | 1601670058396616 | NTC A/C DRIEHAUS FEMF | 219,261 |
<tbody>
</tbody>
Please help ASAP.
Regards,
Rubayat