Return Number Based on Date

[dunmore83]

Hi,

In my 1st spreadsheet named ABC I have unique numbers (no duplicates) in Column B

In my 2nd spreadsheet named MOP I have dates in column F & numbers in Column G. In column B are the values from Column B in column B in the ABC spreadsheet (with duplicates).

In my 1st spreadsheet ABC I need a formula to go into column F that will return the 1st date found in the MOP spreadsheet in Column F based on the Lookup Value in Column B

I then need a formula to go in the ABC spreadsheet in column J that will return the next newest date which occurs after the one returned from the formula in Column F. I then need to repeat this formula by continuing to find the next newest date in column F of MOP.

Hope it makes sense!

Any helpers ??

 

[bebo021999]

Could you post image/screenshot/XL2BB to display data in the worksheet?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[My Aswer Is This]

Just a note:
You said:
"In my 1st spreadsheet named ABC"

The term "spreadsheet"
Was used prior to about 1980 to refer to what we now call a "Workbook"
Today Excel uses the terms "Workbook" and "Worksheet"
So I assume you mean Worksheet named "ABC"

Excel can have several "Worksheets" in a "Workbook"

 

[dunmore83]

Sorry for the incorrect use of the term spreadsheet. Yes please replace the term spreadsheet with Worksheet.

 

[dunmore83]

Screenshots below. Thanks

 

[bebo021999]

there are too much data to input manually to test
can you try to post mini sheet via XL2BB tool, follow link instruction in #2?

 

[Peter_SSs]

In my 1st spreadsheet ABC I need a formula to go into column F that will return the 1st date found in the MOP spreadsheet in Column F based on the Lookup Value in Column B

What do you mean by the 'first' date found?
- The one closest to the top of the worksheet column?
- The earliest date?
- The latest date?
- Something else?

The sample dates in column G of MOP are all identical (as far as I can see). Are the actual dates in that column sorted? If so, earliest dates at top of column or latest dates at top of column?

 

[dunmore83]

Thanks for your reply. The sample dates in column F of MOP are a number of different dates. It just shows 01-Feb-22 in the screenshot. I do not plan to sort column F.

I need a formula in Column F of ABC to return the earliest date for that VID (column B). Then in column J the next date found after the date in Column F for that VID.

 

[Peter_SSs]

I suggest that you update your Account details (or 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’)

Since you haven't given us any sample data that we can copy (as suggested by bebo, check out XL2BB for next time) this is my sample MOP sheet

dunmore83.xlsm
	B				F
1
2	12352				07-Feb-22
3	12347				04-Mar-22
4	12350				19-Feb-22
5	12351				18-Jan-22
6	12345				11-Feb-22
7	12346				29-Mar-22
8	12348				20-Feb-22
9	12349				22-Jan-22
10	12352				19-Feb-22
11	12353				22-Feb-22
12	12349				17-Mar-22
13	12348				21-Mar-22
14	12348				06-Mar-22
15	12346				29-Jan-22
16	12353				29-Jan-22
17	12351				15-Jan-22
18	12347				11-Feb-22
19	12349				19-Jan-22
20	12353				16-Feb-22
21	12351				14-Mar-22
22	12345				04-Feb-22
23	12347				18-Jan-22
24	12345				03-Feb-22
25	12350				07-Jan-22
26	12350				25-Mar-22
27	12346				25-Jan-22
28	12352				19-Jan-22
MOP

Then see if these do what you want.

dunmore83.xlsm
	B				F				J				N
1					Date				Date				Date
2	12345				03-Feb-22				04-Feb-22				11-Feb-22
3	12346				25-Jan-22				29-Jan-22				29-Mar-22
4	12347				18-Jan-22				11-Feb-22				04-Mar-22
5	12348				20-Feb-22				06-Mar-22				21-Mar-22
6	12349				19-Jan-22				22-Jan-22				17-Mar-22
7	12350				07-Jan-22				19-Feb-22				25-Mar-22
8	12351				15-Jan-22				18-Jan-22				14-Mar-22
9	12352				19-Jan-22				07-Feb-22				19-Feb-22
10	12353				29-Jan-22				16-Feb-22				22-Feb-22
ABC

Cell Formulas
Range	Formula
F2:F10	F2	=AGGREGATE(15,6,MOP!F$2:F$100/(MOP!B$2:B$100=B2),1)
J2:J10	J2	=AGGREGATE(15,6,MOP!F$2:F$100/(MOP!B$2:B$100=B2),2)
N2:N10	N2	=AGGREGATE(15,6,MOP!F$2:F$100/(MOP!B$2:B$100=B2),3)

 

[dunmore83]

I will try XL2BB next time.

Excellent. That works except in the 3rd column where this no 3rd date yet it returns #NUM! Can we adjust the formula so it returns a blank value if no 3rd date is found ?