vishal0589
Board Regular
- Joined
- May 13, 2014
- Messages
- 63
Hi Friends,
I am working on large dataset having daily entry around 3K rows and around 70K (Monthly) . I have setup a Bi-weekly format to analyse the same with 3 criteria.
I have used the Array function to retrieve maximum 3 matching text values for selected criteria.
Suppose
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
Criteria:
<colgroup><col><col><col><col span="9"></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col span="8"></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>
I am working on large dataset having daily entry around 3K rows and around 70K (Monthly) . I have setup a Bi-weekly format to analyse the same with 3 criteria.
I have used the Array function to retrieve maximum 3 matching text values for selected criteria.
Suppose
| Date | Post Code | Shift | Service No | Name | Designation | Post Name | Location | Shift Hours | Flag | Duty |
| 01/08/2014 | 5 | 2:00 PM To 10:00 PM | 124 | Alba Softech Pvt Ltd. Gurgaon | Gurgaon | 8 | 1 | G8 | ||
| 01/08/2014 | 2 | 6:00 AM To 2:00 PM | 2421 | UPDESH KUMAR | G/M | Ahuja & Sons A-50 Naraina Delhi | Delhi | 8 | 1 | M8 |
| 01/08/2014 | 5 | 8:00 AM To 8:00 PM | 2463 | KRISHNA KUMAR CHAUDHARY | S/G | Alba Softech Pvt Ltd. Gurgaon | Gurgaon | 12 | 1 | G12 |
| 01/08/2014 | 3 | 7:00 AM To 7:00 PM | 2511 | NEM SINGH | G/M | Airworth Tours And Travel Pvt Ltd, Nehru Place Del | Delhi | 12 | 1 | M12 |
| 01/08/2014 | 3 | 2:00 PM To 10:00 PM | 2511 | NEM SINGH | G/M | Airworth Tours And Travel Pvt Ltd, Nehru Place Del | Delhi | 8 | 2 | MD8 |
| 01/08/2014 | 5 | 7:00 AM To 11:00 PM | 2585 | RAJENDRA KUMAR | S/G | Alba Softech Pvt Ltd. Gurgaon | Gurgaon | 16 | 1 | G16 |
| 01/08/2014 | 4 | 9:00 AM To 9:00 PM | 2620 | SANJAY KUMAR | G/M | Alba Softech (Home) D 3/11 Dlf I Gurgaon | Gurgaon | 12 | 1 | M12 |
| 01/08/2014 | 4 | 10:00 PM To 6:00 AM | 2620 | SANJAY KUMAR | G/M | Alba Softech (Home) D 3/11 Dlf I Gurgaon | Gurgaon | 8 | 2 | MD8 |
| 01/08/2014 | 5 | 8:00 AM To 8:00 PM | 2673 | SURENDER KUMAR | S/G | Alba Softech Pvt Ltd. Gurgaon | Gurgaon | 12 | 1 | G12 |
| 01/08/2014 | 2 | 9:00 AM To 9:00 PM | 2740 | TILAKDHARI | S/G | Ahuja & Sons A-50 Naraina Delhi | Delhi | 12 | 1 | G12 |
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
Criteria:
| Period Start Date : | Friday, 01 August, 2014 | Enter Post Code: | 2 |
<colgroup><col><col><col><col span="9"></colgroup><tbody>
</tbody>
| Service No. | Fri | Sat | Sun | |||||||
| 1-Aug-14 | 2-Aug-14 | 3-Aug-14 | ||||||||
| Shift-I | Shift-II | Shift-III | Shift-I | Shift-II | Shift-III | Shift-I | Shift-II | Shift-III | ||
| 80 | G8 | |||||||||
| 124 | ||||||||||
| 125 | ||||||||||
| 2421 | M8 | |||||||||
| 2463 | G12 | |||||||||
| 2511 | ||||||||||
| 2585 | ||||||||||
| 2620 | ||||||||||
| 2673 | ||||||||||
| 2740 | G12 |
<colgroup><col><col><col><col span="8"></colgroup><tbody>
</tbody>
| Now My Problem is that, if I use Array formula, then It took 15 Minutes to complete and my sheet got hanged for while. If I used following VBA Formula: (Found on Ozgrid) Function FindNth(Table As Range, Val1 As Variant, Val1Occrnce As Integer, _ Val2 As Variant, Val2Col As Integer, Val3 As Variant, Val3Col As Integer, ResultCol As Integer) ''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid Business Applications 'www.ozgrid.com 'Finds the N'th value in the first Column of a table that has a stated _ value on the same row in another Column. ''''''''''''''''''''''''''''''''''''''' Dim i As Integer Dim iCount As Integer Dim rCol As Range For i = 1 To Table.Rows.Count If Table.Cells(i, 4) = Val1 And _ Table.Cells(i, Val2Col) = Val2 And _ Table.Cells(i, Val3Col) = Val3 Then iCount = iCount + 1 End If If iCount = Val1Occrnce Then FindNth = Table.Cells(i, ResultCol) Exit For End If Next i End Function It also takes 10 to 15 Mins. Please Advise what can I do for fast calculation. Thanks in Advance |
<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>
