Compare a date and identify which number it falls in based on date range.

[maryflower]

Hi,
I have a workbook, with two sheets - sheet 1, sheet 2.

Sheet 1 has headers - Account, Date
Sheet 2 has headers - Account, Period, Date 1, Date 2. Date 1 is start date, date 2 is end date.

What I am trying to achieve is for excel macro or formulas to update sheet 1 with period from sheet 2, based on the date lited in sheet 1.

For example: first entry in sheet 1, account 1 has date 06/22/2016. In sheet 2, this account 1 has various dates with period number attached to it. For this particular entry in sheet 1, I want to see 1 as an answer in column C because 06/22/2016 falls between the date range 12/1/2015 - 11/30/2016, which is period 1 in sheet 2. Similarly, row 65 in sheet 1 says "2" because 12/2/2016 falls in between 12/01/2016 thru 11/30/2017 in sheet 2.

Notes:
- Sheet 1 and Sheet 2 will have multiple lines with same dates. I have 20 more columns explaining what happened on that particular date.
- I will have 100's of accounts in both sheets.


Attached is a sample. Thank you for looking into this.
Your help is appreciated.

[beyond Excel]

Hello maryflower. Sheet2 has 27 rows of data of which 24 rows are repeats.
By removing those 24 you are left with only 3 useful rows. In your real workbook: Can those 24 rows be deleted?...

In Sheet1 something similar occurs: 1183 rows could be deleted.

Comments?...

[beyond Excel]

And another question: Only 'Account 1' are you analyzing?...

If not, you should upload a more real example to the Forum.

[maryflower]

Thank you for the response, and helping.
I can delete duplicates in sheet 2 but not in sheet 1 since I have different $ posted to each date. In reality, I will have more accounts. Attaching updated file with 2 accounts.

All I am trying to do is identify period in sheet 1 for each date based on date ranges in sheet 2.

[beyond Excel]

I think this can help you:

Please Login or Register  to view this content.

[maryflower]

Thank you . I will definitely try this.
I was also able to achieve this by using Lookup formula. It made my file heavy sized

Macro might be a way but I am going to try. Will revert

[maryflower]

If I have to change column reference in sheet 1, which part of the macro should I change? In reality, my account is col B (col 2), date is column F (col 6), result is column AJ (col 36)

[beyond Excel]

.
Then try this new version:

[maryflower]

This is magical! thank you so much

[beyond Excel]

This is magical! thank you so much

Jajaja... "magical"

Please, Maryflower, don't forget to click on the * below and to the left of post #8...