Can you help me think this out? How would you approach it?

[GregG44149]

Not sure of the best way to approach this. I have a fairly large spreadsheet that contains information below.

I want to create a function (or maybe a different approach?) that does the following:

1) Will look at column F and find the right Client Code (each client code is 3 digits, separated by semi-colon). Variable in length and number of clients. Client numbers are unique.
2) Then looks at column C and finds the correct state
3) Finally, looks at column E to confirm that expiration date is less than today.

I'm stuck because I can't break out client code in F to individual lines (spreadsheet would quickly become exponentially large with 50 states). Otherwise I would probably approach it using an array function.

A	B	C	D	E	F
AGENT CODE	NAME1	LIC STATE	LIC DATE	LIC EXP DATE	CLIENT
HNBL	BROKER1	LA	12/17/2013​	03/31/2024​	AAA;BBB;
HNBL	BROKER1	OH	01/01/2022​	12/31/2024​	AAA;BBB;
HNBL	BROKER1	TX	04/01/2022​	12/31/2027​	AAA;BBB;
KENR	BROKER2	GA	03/17/2020​	12/31/2024​	CCC;
JNGX	BROKER3	AL	07/09/2015​	07/31/2019​	FFF;ZZZ;UUU;
HBBD	BROKER4	CO	03/11/2019​	03/31/2024​	DDD;FFF;LLL;MMM;

Thank you in advance!
Greg

 

[offthelip]

I would do that by using a dictionary. Also if you initialise the dictionary by calling the initialise sub when the worksheet is activated the function will work as a worksheet function or you can call it from a vba sub:

Public dic As Object
Sub initialise()
   Set dic = CreateObject("Scripting.dictionary")

lastcol = 6 ' column F
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, lastcol))
For i = 3 To lastrow
 cc = Split(inarr(i, 6), ";")
   For j = 0 To UBound(cc)
     dicv = inarr(i, 3) & cc(j) ' this concatenates the state and the client code
     dic(dicv) = inarr(i, 5)   ' load the dictionary with the date
    Next j
Next i
MsgBox "Dictionary initialised"
End Sub

Sub testdic()
State = "TX"
client = "AAA"
tttt = finddate(State, client)
 MsgBox tttt
End Sub

Function finddate(State As Variant, client As Variant)
 Dim tt As String
 tt = State & client
 ttc = dic(tt)
 finddate = ttc

End Function

 

[Peter_SSs]

Is this what you mean? If not please give examples of the results that you expect and explain why those results.

GregG44149.xlsm
	C		E	F	G	H	I	J
1	LIC STATE		LIC EXP DATE	CLIENT		Client	State	Expired
2	LA		31/03/2024	AAA;BBB;		ZZZ	AL	TRUE
3	OH		31/12/2024	AAA;BBB;		AAA	TX	FALSE
4	TX		31/12/2027	AAA;BBB;		FFF	AL	TRUE
5	GA		31/12/2024	CCC;		MMM	TX	FALSE
6	AL		31/07/2019	FFF;ZZZ;UUU;
7	CO		31/03/2024	DDD;FFF;LLL;MMM;
8
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=COUNTIFS(F:F,"*"&H2&"*",C:C,I2,E:E,"<"&TODAY())>0

 

[GregG44149]

I would do that by using a dictionary. Also if you initialise the dictionary by calling the initialise sub when the worksheet is activated the function will work as a worksheet function or you can call it from a vba sub:

Public dic As Object
Sub initialise()
   Set dic = CreateObject("Scripting.dictionary")

lastcol = 6 ' column F
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, lastcol))
For i = 3 To lastrow
 cc = Split(inarr(i, 6), ";")
   For j = 0 To UBound(cc)
     dicv = inarr(i, 3) & cc(j) ' this concatenates the state and the client code
     dic(dicv) = inarr(i, 5)   ' load the dictionary with the date
    Next j
Next i
MsgBox "Dictionary initialised"
End Sub

Sub testdic()
State = "TX"
client = "AAA"
tttt = finddate(State, client)
 MsgBox tttt
End Sub

Function finddate(State As Variant, client As Variant)
 Dim tt As String
 tt = State & client
 ttc = dic(tt)
 finddate = ttc

End Function

Thank you! Wasn't aware of this approach AND this will assist me on another issue I'm having. Thanks again!

 

[GregG44149]

Is this what you mean? If not please give examples of the results that you expect and explain why those results.

GregG44149.xlsm
	C		E	F	G	H	I	J
1	LIC STATE		LIC EXP DATE	CLIENT		Client	State	Expired
2	LA		31/03/2024	AAA;BBB;		ZZZ	AL	TRUE
3	OH		31/12/2024	AAA;BBB;		AAA	TX	FALSE
4	TX		31/12/2027	AAA;BBB;		FFF	AL	TRUE
5	GA		31/12/2024	CCC;		MMM	TX	FALSE
6	AL		31/07/2019	FFF;ZZZ;UUU;
7	CO		31/03/2024	DDD;FFF;LLL;MMM;
8
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=COUNTIFS(F:F,"*"&H2&"*",C:C,I2,E:E,"<"&TODAY())>0

I learn something new.... everyday. Bingo, this is the simplest approach for me!

 

[Peter_SSs]

Bingo, this is the simplest approach for me!

Cheers. Glad to help. Thanks for the follow-up.