Extract All Matching Values or Nth Occurence

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


Date
Post CodeShiftService NoNameDesignation
Post NameLocationShift HoursFlagDuty
01/08/201452:00 PM To 10:00 PM124Alba Softech Pvt Ltd. GurgaonGurgaon81G8
01/08/201426:00 AM To 2:00 PM2421UPDESH KUMARG/MAhuja & Sons A-50 Naraina DelhiDelhi81M8
01/08/201458:00 AM To 8:00 PM2463KRISHNA KUMAR CHAUDHARYS/GAlba Softech Pvt Ltd. GurgaonGurgaon121G12
01/08/201437:00 AM To 7:00 PM2511NEM SINGHG/MAirworth Tours And Travel Pvt Ltd, Nehru Place DelDelhi121M12
01/08/201432:00 PM To 10:00 PM2511NEM SINGHG/MAirworth Tours And Travel Pvt Ltd, Nehru Place DelDelhi82MD8
01/08/201457:00 AM To 11:00 PM2585RAJENDRA KUMARS/GAlba Softech Pvt Ltd. GurgaonGurgaon161G16
01/08/201449:00 AM To 9:00 PM2620SANJAY KUMARG/MAlba Softech (Home) D 3/11 Dlf I GurgaonGurgaon121M12
01/08/2014410:00 PM To 6:00 AM2620SANJAY KUMARG/MAlba Softech (Home) D 3/11 Dlf I GurgaonGurgaon82MD8
01/08/2014
58:00 AM To 8:00 PM2673SURENDER KUMARS/GAlba Softech Pvt Ltd. GurgaonGurgaon121G12
01/08/2014
29:00 AM To 9:00 PM2740TILAKDHARIS/GAhuja & Sons A-50 Naraina DelhiDelhi121G12

<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-IShift-IIShift-IIIShift-IShift-IIShift-IIIShift-IShift-IIShift-III
80G8
124
125
2421M8
2463G12
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>
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
DatePost CodeShiftService NoNameDesignationPost NameLocationShift HoursFlagDuty
01/08/201452:00 PM To 10:00 PM124Alba Softech Pvt Ltd. GurgaonGurgaon81G8
01/08/201426:00 AM To 2:00 PM2421UPDESH KUMARG/MAhuja & Sons A-50 Naraina DelhiDelhi81M8
01/08/201458:00 AM To 8:00 PM2463KRISHNA KUMAR CHAUDHARYS/GAlba Softech Pvt Ltd. GurgaonGurgaon121G12
01/08/201437:00 AM To 7:00 PM2511NEM SINGHG/MAirworth Tours And Travel Pvt Ltd, Nehru Place DelDelhi121M12
01/08/201432:00 PM To 10:00 PM2511NEM SINGHG/MAirworth Tours And Travel Pvt Ltd, Nehru Place DelDelhi82MD8
01/08/201457:00 AM To 11:00 PM2585RAJENDRA KUMARS/GAlba Softech Pvt Ltd. GurgaonGurgaon161G16
01/08/201449:00 AM To 9:00 PM2620SANJAY KUMARG/MAlba Softech (Home) D 3/11 Dlf I GurgaonGurgaon121M12
01/08/2014410:00 PM To 6:00 AM2620SANJAY KUMARG/MAlba Softech (Home) D 3/11 Dlf I GurgaonGurgaon82MD8
01/08/201458:00 AM To 8:00 PM2673SURENDER KUMARS/GAlba Softech Pvt Ltd. GurgaonGurgaon121G12
01/08/201429:00 AM To 9:00 PM2740TILAKDHARIS/GAhuja & Sons A-50 Naraina DelhiDelhi121G12
Post Code2
Count of DateDuty
Service NoG12M8Grand Total
242111
274011
Grand Total112
can you use this type of approach

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 

vishal0589

Board Regular
Joined
May 13, 2014
Messages
63
Hi oldbrewer


Approach cannot be changed. I have to follow the same format for reporting purpose.
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
but if you extract the data you want you can then format it how you want by moving it to a spare sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,109,536
Messages
5,529,421
Members
409,875
Latest member
Khushal Bisht
Top