Function to Macro/VBA

[dianamruelas]

I'm running this function on just under one million lines. I have a decent processor, but it still ends up being a several-hour-long project. Is there an easier way via macro? Here I have the rows limited to 21460 for this particular data set, but I would like to remove the limit to match to the entire column, as the number of rows will vary each time I run it. Any help is greatly appreciated.

=IFERROR(INDEX($H$2:$H$21460,MATCH(MIN(IF(ISNUMBER(SEARCH(C2,$G$2:$G$21460)),IF(A2-$F$2:$F$21460>=0,A2-$F$2:$F$21460))),IF(ISNUMBER(SEARCH(C2,$G$2:$G$21460)),IF(A2-$F$2:$F$21460>=0,A2-$F$2:$F$21460)),0)),"PRE EFFECTIVE")

 

[Peter_SSs]

It may or may not be relevant to this question but I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Could you give us a small set (say 15-20 rows) of representative sample dummy data and the expected results with XL2BB?

In particular, what I am wondering about (& it may determine the best possible approach) is
- How long/complex the data is, especially in columns C and G?
- If there is repetitive data in column C?
- Where the results are to go?

Also,
- What column or columns determine how many rows in the worksheet contain data?
- Is the number of rows of data the same in columns A & C as for columns F:H?

Could be more questions later but that should allow some sort of initial investigation.

 

[dianamruelas]

Thanks for the response! I'm evaluating provider data for a physician group. Don't worry, no PHI here. You'd be surprised how often a doctor switches back and forth from a Primary care specialty to cardiology, etc. I have a running list of their status per month. This list will grow every month, but will be reset every year. I'm trying to match a date they saw a patient to their most recent status date. For example, I need a 3/28/20 service date to match to their specialty on the 3/1/20 update, as the 4/1 specialty would not yet be effective.

-The dates of service can be up to a million lines depending on the time frame I am evaluating. And the specialty list, as it grows, may be up to 50,000 lines.
-Column C is the provider identifier number, so there will be many repetitions as they may have many dates of service.
-The results will go in column D
-Column A:C is the service date and the provider identifier, so that data will go together. Column F:H is the provider status and the provider identifier, so that data will go together.

Unfortunately, I'm not the administrator of my machine, so I cannot do XL2BB. See below sample data set, followed by desired outcome. Note that if there is not a match, it will return "PRE EFFECTIVE"

Service DT	Name (ignore)	PROV IDENTIFIER	PROVIDER TYPE		EFFECTIVE DATE	PROVIDER IDENTIFIER	Provider Type
4/27/2020​		1871830299​			5/1/2020​	1598300428​	APP-Spec
4/27/2020​		1871830299​			6/1/2020​	1598300428​	APP-Spec
4/27/2020​		1871830299​			7/1/2020​	1598300428​	APP-Spec
4/30/2020​		1871830299​			8/1/2020​	1598300428​	APP-Spec
5/4/2020​		1871830299​			9/1/2020​	1598300428​	APP-Spec
5/11/2020​		1871830299​			10/1/2020​	1598300428​	APP-Spec
5/11/2020​		1871830299​			11/1/2020​	1598300428​	APP-Spec
5/11/2020​		1871830299​			12/1/2020​	1598300428​	APP-Spec
5/11/2020​		1871830299​			1/1/2021​	1598300428​	APP-Spec
5/11/2020​		1871830299​			1/1/2019​	1871830299​	APP-Spec
5/21/2020​		1871830299​			2/1/2019​	1871830299​	APP-Spec
05/19/20​		1598300428​			3/1/2019​	1871830299​	APP-Spec
05/19/20​		1598300428​			4/1/2019​	1871830299​	APP-Spec
04/22/20​		1598300428​			5/1/2019​	1871830299​	APP-Spec
05/05/20​		1598300428​			6/1/2019​	1871830299​	APP-Spec
05/05/20​		1598300428​			7/1/2019​	1871830299​	APP-Spec
05/12/20​		1598300428​			8/1/2019​	1871830299​	APP-Spec
04/27/20​		1598300428​			9/1/2019​	1871830299​	APP-Spec
03/31/20​		1598300428​			10/1/2019​	1871830299​	APP-Spec
04/28/20​		1598300428​			1/1/2020​	1871830299​	APP-Spec
04/28/20​		1598300428​			2/1/2020​	1871830299​	APP-Spec
05/15/20​		1598300428​			3/1/2020​	1871830299​	APP-Spec
05/12/20​		1598300428​			5/1/2020​	1871830299​	APP-PCP
05/12/20​		1598300428​			6/1/2020​	1871830299​	APP-PCP
05/05/20​		1598300428​			7/1/2020​	1871830299​	APP-PCP
05/27/20​		1598300428​			8/1/2020​	1871830299​	APP-PCP
					9/1/2020​	1871830299​	APP-PCP
					10/1/2020​	1871830299​	APP-PCP
					11/1/2020​	1871830299​	APP-PCP
					12/1/2020​	1871830299​	APP-PCP
					1/1/2021​	1871830299​	APP-PCP

Service DT	Name (ignore)	PROVIDER IDENTIFIER	PROVIDER TYPE		EFFECTIVE DATE	PROVIDER IDENTIFIER	Provider Type
4/27/2020​		1871830299​	APP-Spec		5/1/2020​	1598300428​	APP-Spec
4/27/2020​		1871830299​	APP-Spec		6/1/2020​	1598300428​	APP-Spec
4/27/2020​		1871830299​	APP-Spec		7/1/2020​	1598300428​	APP-Spec
4/30/2020​		1871830299​	APP-Spec		8/1/2020​	1598300428​	APP-Spec
5/4/2020​		1871830299​	APP-PCP		9/1/2020​	1598300428​	APP-Spec
5/11/2020​		1871830299​	APP-PCP		10/1/2020​	1598300428​	APP-Spec
5/11/2020​		1871830299​	APP-PCP		11/1/2020​	1598300428​	APP-Spec
5/11/2020​		1871830299​	APP-PCP		12/1/2020​	1598300428​	APP-Spec
5/11/2020​		1871830299​	APP-PCP		1/1/2021​	1598300428​	APP-Spec
5/11/2020​		1871830299​	APP-PCP		1/1/2019​	1871830299​	APP-Spec
5/21/2020​		1871830299​	APP-PCP		2/1/2019​	1871830299​	APP-Spec
05/19/20​		1598300428​	APP-Spec		3/1/2019​	1871830299​	APP-Spec
05/19/20​		1598300428​	APP-Spec		4/1/2019​	1871830299​	APP-Spec
04/22/20​		1598300428​	PRE EFFECTIVE		5/1/2019​	1871830299​	APP-Spec
05/05/20​		1598300428​	APP-Spec		6/1/2019​	1871830299​	APP-Spec
05/05/20​		1598300428​	APP-Spec		7/1/2019​	1871830299​	APP-Spec
05/12/20​		1598300428​	APP-Spec		8/1/2019​	1871830299​	APP-Spec
04/27/20​		1598300428​	PRE EFFECTIVE		9/1/2019​	1871830299​	APP-Spec
03/31/20​		1598300428​	PRE EFFECTIVE		10/1/2019​	1871830299​	APP-Spec
04/28/20​		1598300428​	PRE EFFECTIVE		1/1/2020​	1871830299​	APP-Spec
04/28/20​		1598300428​	PRE EFFECTIVE		2/1/2020​	1871830299​	APP-Spec
05/15/20​		1598300428​	APP-Spec		3/1/2020​	1871830299​	APP-Spec
05/12/20​		1598300428​	APP-Spec		5/1/2020​	1871830299​	APP-PCP
05/12/20​		1598300428​	APP-Spec		6/1/2020​	1871830299​	APP-PCP
05/05/20​		1598300428​	APP-Spec		7/1/2020​	1871830299​	APP-PCP
05/27/20​		1598300428​	APP-Spec		8/1/2020​	1871830299​	APP-PCP
					9/1/2020​	1871830299​	APP-PCP
					10/1/2020​	1871830299​	APP-PCP
					11/1/2020​	1871830299​	APP-PCP
					12/1/2020​	1871830299​	APP-PCP
					1/1/2021​	1871830299​	APP-PCP

 

[Peter_SSs]

Thanks for the additional information.
While I consider a macro, can you see how this alternative formula compares?

IF your Excel 365 has the LET function, try this, copied down

=LET(fltr,FILTER(H$2:H$21460,(G$2:G$21460=C2)*(F$2:F$21460<=A2),"PRE EFFECTIVE"),INDEX(fltr,ROWS(fltr)))

If you do not have the LET function, try

=INDEX(FILTER(H$2:H$21460,(G$2:G$21460=C2)*(F$2:F$21460<=A2),"PRE EFFECTIVE"),ROWS(FILTER(H$2:H$21460,(G$2:G$21460=C2)*(F$2:F$21460<=A2),"PRE EFFECTIVE")))

 

[dianamruelas]

Thank you! I do not have the LET function, but the index formula works great. It does appear to be more efficient, but I would most likely still have to "set it and leave it" while it processes the bigger files. Is it just a fairy tale that a macro would speed this up?

Also, I initially tried H:H, G:G, etc. but it drastically slowed the processing. Is there a way to remove the row limit? In this case, it's limited to row 21460. I'd like to have a standard universal function. I have to train others to use this so the less steps the better.

thanks again so much!

 

[Peter_SSs]

A possible macro approach but I have not tested on large data for speed.
I have made the following assumptions

• Columns F:H are sorted by Provider Identifier then Effective Date (ascending) as appears to be the case with your sample data. (If this is not the case, could it be sorted that way?)
• Columns A:C are not sorted as above. (If they could be then it may be possible to improve the speed of the macro)

Test with the sample data above first and if it appears to do what you want, gradually increase the size of the data until you can assess the speed.

Sub Provider_Type_v1()
  Dim d As Object
  Dim a As Variant, b As Variant, c As Variant, Rws As Variant
  Dim LastRow As Long, i As Long, j As Long
  Dim s As String
  
  LastRow = Range("F" & Rows.Count).End(xlUp).Row + 1
  a = Range("F1:H" & LastRow).Value2
  Set d = CreateObject("Scripting.Dictionary")
  For i = 2 To UBound(a) - 1
    If a(i, 2) <> a(i - 1, 2) Then d(a(i, 2)) = i
    If a(i, 2) <> a(i + 1, 2) Then d(a(i, 2)) = d(a(i, 2)) & " " & i
  Next i
  
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
  c = Range("A2:C" & LastRow).Value2
  ReDim b(1 To UBound(c), 1 To 1)
  For i = 1 To UBound(c)
    b(i, 1) = "PRE EFFECTIVE"
    If d.exists(c(i, 3)) Then
      Rws = Split(d(c(i, 3)))
      For j = Rws(1) To Rws(0) Step -1
        If a(j, 1) <= c(i, 1) Then
          b(i, 1) = a(j, 3)
          Exit For
        End If
      Next j
    End If
  Next i
  Range("D2").Resize(UBound(b)).Value = b
End Sub