Extend the dates and compare the date with other rows

[Tharabai]

Hi All,

Please!! required help !!

Step 1 : Filter not blanks column (column I) and find the difference in between the dates by inserting a column (column L) named "Difference"

Step 2 : Based on the value in the column "difference" i need to insert the rows and fill the values
eg. if the difference between the dates 01/13/2015 to 01/15/2015 is 3, then i need to insert 3 rows below this line and fill the dates with + 1 added to the dates till the end date and the ID should be the same.

01/13/2015
01/14/2015
01/15/2015

Column ID should be taken into consideration.

Let say ID is 12345 then dates are 01/13/2015 to 01/16/2015 then I need to insert below rows

12345 01/13/2015
12345 01/14/2015
12345 01/15/2015
12345 01/16/2015

Rows inserted should be highlighted

Step 3 : Concatenate ID and dates eg. 12345 01/13/2015

Step 4 : Compare for the column, if duplicated then, the row should be marked as “Overlap”

Step 5: delete the inserted rows (rows inserted to find the in between days)

Hope am clear with my requirement,

I have attached sample file for reference.

-Thara

[SamT]

Hi, Tharabai,

I think that if I knew what you were trying to do, I would know how to do it. Telling me how to do it, doesn't help me understand what you want.

So you want something marked "Overlap." What are the conditions that mean that it is overlapped?

On the output sheet, why do any rows get marked as Overlap, nothing in any so marked rows is duplicated.

OK, I sorted the columns various ways, and when I sorted by ID then by From, I saw that when an inserted row had the same ID and From as an original, either the inserted row or the orignial was marked "Overlap" but not both.

Tharabai, may I suggest that you select all the columns, then on the Data Menu, click "Sort" the first sort by "ID," second, sort by "From," then click "OK."

Study the results and maybe you can think of another way to get the results you need.

[Tharabai]

thank you for the response.

i want to know the date overlap.

the result should be that the dates which are overlapped should be marked as "overlap" for the particular ID.

For a particular ID, any row with the dates within the date range is overlap.

for eg, for the ID 84441638 the date range is 06/22/2015 to 06/26/2015. Any other rows with the dates within this date range for that ID is overlap. We also find another date range for the same ID. the above applicable for this as well...

Hope i made it clear now

[SamT]

Why didn't you sort the Source sheet like I suggested?


Anyway. Look at this attachment in which I did sort the sheet. From what I believe, you don't need to insert empty rows with dates and IDs, and then delete those rows after all is donebut you only need to have the green rows in my attachment marked OverLap.

What about the "To" cells which overlap the "From" Cells, noted in yellow in the attached.

[Tharabai]

Thank you for the response.

But the results in your file does not match with my output.

Also, i have very huge data (numbers of rows is very high with many ID's).

As the data is high, just thought of using VBA.

and i should check only for the rows with FROM and TO dates. If any rows with dates inbetween the range for the particular ID then tat needs to be marked as "Overlap".

sorry for the confusion if i have not pointed my requirement clear..

But help on this will be highly appreciated.

-Tharabai

[Tharabai]

Hi, Any update pls....