Formula Help Please

[elleinndee]

Hello, I'm going crazy trying to figure out a formula. Any assistance is greatly appreciated.

On Sheet1 I have column A (Unique Identifier), column W (Authorization Date), column X (Start Date), and column Y (First Date Billed).

On Sheet 2 I have column B (Unique Identifier) and column I (Activity Date).

On Sheet 1 column Y is filled with the first Activity Date found (column I) on Sheet 2 via a VLOOKUP with the Unique Identifier (column A on Sheet 1, column B on Sheet 2).

I need a formula for column X on Sheet 1, Start Date, that will give me this:

If on Sheet 1 column W (Authorization Date) comes before column Y (First Date Billed), the Unique Identifier on both sheets is used to lookup the nearest date after Sheet 2 column I (Activity Date) which will then fill column X (Start Date) on Sheet 1.

 

[KRice]

column Y is filled with the first Activity Date found

For any given Unique Identifier in column A of Sheet 1, will there ever only be one match found in column B of Sheet 2?...or could there be multiple entries in col. B of Sheet 2 that have the same Unique Identifier? Your use of the phrase "first Activity Date found" makes me curious about this, as if there could be 2nd, 3rd, etc. matches as well.

On Sheet 2, are the Unique Identifiers in column B always listed in ascending order? You mention using a VLOOKUP function; and depending on how it is used, sort order may matter.

If on Sheet 1 column W (Authorization Date) comes before column Y (First Date Billed), the Unique Identifier on both sheets is used to lookup the nearest date after Sheet 2 column I (Activity Date) which will then fill column X (Start Date) on Sheet 1.

Could you clarify this further, please? You obtain the First Date Billed (col Y Sheet 1) by matching Unique Identifiers to obtain the "first" match in column B Sheet 2 and extract the corresponding Activity Date (col I Sheet 2)...that's the First Date Billed. Then you want a comparison...IF ([Authorization Date]<[First Date Billed], "Unique Identifiers used to lookup nearest date after Sheet 2 column I"...what does this last part mean? Suppose [Authorization Date]<[First Date Billed], we look at the corresponding Unique Identifier on that row in Sheet 1 and then what?...we find that Unique Identifier in column B of Sheet 2 and...? are there multiple matching Unique Identifiers on Sheet 2 and we obtain a different Activity Date? This is what I'm confused about.

 

[elleinndee]

For any given Unique Identifier in column A of Sheet 1, will there ever only be one match found in column B of Sheet 2?...or could there be multiple entries in col. B of Sheet 2 that have the same Unique Identifier?

There are multiple entries.

Your use of the phrase "first Activity Date found" makes me curious about this, as if there could be 2nd, 3rd, etc. matches as well.

Yes, there can be multiple Activity Dates. This column is sorted in ascending order so the first match found is the one pulled.

On Sheet 2, are the Unique Identifiers in column B always listed in ascending order? You mention using a VLOOKUP function; and depending on how it is used, sort order may matter.

No, only the activity date column on sheet 2 is listed in ascending order.

Could you clarify this further, please? You obtain the First Date Billed (col Y Sheet 1) by matching Unique Identifiers to obtain the "first" match in column B Sheet 2 and extract the corresponding Activity Date (col I Sheet 2)...that's the First Date Billed.

Correct. Column I sheet 2 is sorted in ascending order so it gives us the earliest date that way.

Then you want a comparison...IF ([Authorization Date]<[First Date Billed], "Unique Identifiers used to lookup nearest date after Sheet 2 column I"...what does this last part mean? Suppose [Authorization Date]<[First Date Billed], we look at the corresponding Unique Identifier on that row in Sheet 1 and then what?...we find that Unique Identifier in column B of Sheet 2 and...? are there multiple matching Unique Identifiers on Sheet 2 and we obtain a different Activity Date?

Yes. We want to find the nearest activity date date to the authorization date. So if the authorization date is 7/1/2022 and the first date billed is 6/1/2022, we want to find the activity date that is greater than or equal to the authorization date, so 7/3/2022 for example.

This is what I'm confused about.

Thank you for responding! Sorry for the difficult wording. I would attach but the sheet has confidential client information.

 

[KRice]

Are the Authorization Dates "given"...that is, you already know them? So would it make for sense to simply use both the Unique Identifier from col A/Sht 1 and Authorization Date from col W/Sht 1 and find the next larger Activity Date associated with that Unique Identifier on Sheet 2? Would that work in all cases?

 

[elleinndee]

Are the Authorization Dates "given"...that is, you already know them? So would it make for sense to simply use both the Unique Identifier from col A/Sht 1 and Authorization Date from col W/Sht 1 and find the next larger Activity Date associated with that Unique Identifier on Sheet 2? Would that work in all cases?

Yes all the authorization dates are given. Yes what you are saying would work and sounds so much simpler. My brain just can’t work out how to do that though?

 

[KRice]

Try this:

Book2
	A																						W	X	Y
1	Unique Identifier																						Authorization Date	Start Date	First Date Billed
2	uid1																						1/10/2022	1/15/2022	1/7/2022
3	uid2																						1/20/2022	1/23/2022	1/23/2022
4	uid3																						1/15/2022	no date found	no date found
5	uid4																						3/3/2022	4/17/2022	1/8/2022
6	uid5																						2/5/2022	2/8/2022	2/8/2022
7	uid6																						4/13/2022	no date found	no date found
8	uid7																						4/20/2022	no date found	no date found
9	uid8																						6/17/2022	7/11/2022	7/11/2022
Sheet1

Cell Formulas
Range	Formula
X2:X9	X2	=IFERROR(INDEX(SORT(FILTER(Sheet2!$I$2:$I$10,(Sheet2!$B$2:$B$10=$A2)*(Sheet2!$I$2:$I$10>$W2))),1),"no date found")
Y2:Y9	Y2	=IFERROR(INDEX(SORT(FILTER(Sheet2!$I$2:$I$10,(Sheet2!$B$2:$B$10=$A2))),1),"no date found")

For this notional data set:

Book2
	B							I
1	Unique Identifier							Activity Date
2	uid1							1/7/2022
3	uid4							1/8/2022
4	uid1							1/15/2022
5	uid2							1/23/2022
6	uid5							2/8/2022
7	uid4							2/13/2022
8	uid4							4/17/2022
9	uid1							5/15/2022
10	uid8							7/11/2022
Sheet2

Sorting of your table is not required as the formula handles that.

 

[elleinndee]

Try this:

Book2
	A																						W	X	Y
1	Unique Identifier																						Authorization Date	Start Date	First Date Billed
2	uid1																						1/10/2022	1/15/2022	1/7/2022
3	uid2																						1/20/2022	1/23/2022	1/23/2022
4	uid3																						1/15/2022	no date found	no date found
5	uid4																						3/3/2022	4/17/2022	1/8/2022
6	uid5																						2/5/2022	2/8/2022	2/8/2022
7	uid6																						4/13/2022	no date found	no date found
8	uid7																						4/20/2022	no date found	no date found
9	uid8																						6/17/2022	7/11/2022	7/11/2022
Sheet1

Cell Formulas
Range	Formula
X2:X9	X2	=IFERROR(INDEX(SORT(FILTER(Sheet2!$I$2:$I$10,(Sheet2!$B$2:$B$10=$A2)*(Sheet2!$I$2:$I$10>$W2))),1),"no date found")
Y2:Y9	Y2	=IFERROR(INDEX(SORT(FILTER(Sheet2!$I$2:$I$10,(Sheet2!$B$2:$B$10=$A2))),1),"no date found")

For this notional data set:

Book2
	B							I
1	Unique Identifier							Activity Date
2	uid1							1/7/2022
3	uid4							1/8/2022
4	uid1							1/15/2022
5	uid2							1/23/2022
6	uid5							2/8/2022
7	uid4							2/13/2022
8	uid4							4/17/2022
9	uid1							5/15/2022
10	uid8							7/11/2022
Sheet2

Sorting of your table is not required as the formula handles that.

Yes!!! Thank you so much! You've saved the day!

 

[KRice]

You're welcome...I'm happy to help.