Consolidating Consecutive Date Rows

[pawville]

Hi All,
I am working with an excel file that shows daily whether an individual is checked into a shelter. The dates are often consecutive. I am trying to merge rows to reflect a true length of stay, not daily. here is what my data looks like:
First Last SSN DOB Entry Date Exit Date
Jane Doe 111111111 1/11/1111 4/1/2009 4/1/2009
Jane Doe 111111111 1/11/1111 4/2/2009 4/2/2009
Jane Doe 111111111 1/11/1111 4/3/2009 4/3/2009
Jane Doe 111111111 1/11/1111 4/4/2009 4/4/2009
Jane Doe 111111111 1/11/1111 4/5/2009 4/5/2009
Jane Doe 111111111 1/11/1111 4/6/2009 4/6/2009
Jane Doe 111111111 1/11/1111 4/7/2009 4/7/2009
Jane Doe 111111111 1/11/1111 4/8/2009 4/8/2009
Jane Doe 111111111 1/11/1111 4/9/2009 4/9/2009
Jane Doe 111111111 1/11/1111 4/10/2009 4/10/2009
Jane Doe 111111111 1/11/1111 4/12/2009 4/12/2009
Jane Doe 111111111 1/11/1111 4/13/2009 4/13/2009
Jane Doe 111111111 1/11/1111 4/14/2009 4/14/2009
Jane Doe 111111111 1/11/1111 4/15/2009 4/15/2009
Jane Doe 111111111 1/11/1111 4/16/2009 4/19/2009
Jane Doe 111111111 1/11/1111 4/21/2009 4/22/2009

Here is what I would like it to look like:
First Last SSN DOB Entry Date Exit Date
Jane Doe 111111111 1/11/1111 4/1/2009 4/10/2009
Jane Doe 111111111 1/11/1111 4/12/2009 4/19/2009
Jane Doe 111111111 1/11/1111 4/21/2009 4/22/2009

Does anyone have any suggestions on how I can accomplish this with a macro or otherwise so I don't have to manually go through a year's worth of data? Thanks for your help and time.
Sincerely,
Fred

 

[Domenic]

The other possibility is that the data from the imported file contains special or invisible characters. If this is the case, ASAP Utilities can be used to remove them. You can easily find the utility by searching Google.

 

[pawville]

Hello Domenic,
Sorry for the delayed reply. Here is my formula (as I copied, pasted and updated the cells):

=SUMPRODUCT(--ISNUMBER(R2:S48023))=ROWS(R2:S48023)*2

The value returned was "FALSE".

So it appears that some of the values are not stored as true dates.

What else can I do. Thanks for your help and time.
Sincerely,
Fred

 

[Domenic]

1) Which date format does your system use -- m/d/yyyy or d/m/yyyy ?

2) How does the date actually appear in the cell?

By the way, I'm assuming that there are no blank cells in R2:S48023, correct?

 

[pawville]

1.) & 2.) Here are some data:
8/29/2007 8/29/2007
11/4/2009 11/4/2009
10/5/2007 11/2/2007
1/2/2008 1/22/2008
10/15/2009 10/21/2009
10/22/2009 10/22/2009
10/23/2009 10/23/2009
10/24/2009 10/24/2009
10/25/2009 10/25/2009

I used auto filter to determine that there are no blanks to filter on.

Is that helpful?

 

[Domenic]

Try adding the following line of code just prior to the 'For-Next' construct...

Range("R2:S" & LastRow).Value = Range("R2:S" & LastRow).Value

Does this help?

 

[pawville]

Hello Domenic,
Thank you again for your help. I inserted the code as instructed (I think) to reflect:

Sub test()

Dim LastRow As Long
Dim i As Long

Application.ScreenUpdating = False

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("R2:S" & LastRow).Value = Range("R2:S" & LastRow).Value

For i = LastRow To 3 Step -1
If Cells(i - 1, "F") + 1 = Cells(i, "E") Then
Cells(i, "E") = Cells(i - 1, "E")
Rows(i - 1).Delete
End If
Next i

Application.ScreenUpdating = False

End Sub

I still get the same error.

As of this morning I was able to combine the data and save it as a CSV file. In doing so I can run the macro without error. I am presently reviewing in greater detail to be sure no records are missing.

Thank you again for your help and time. As you can see, your help has saved me a great deal of time in not having to filter through 49,000 rows of data. If there is a way that I can recommend you to others, I would be glad to do so. Just let me know.
Sincerely,
Fred

 

[pawville]

Hello Domenic,
I just wanted to update you on your last line of code suggestion. I tried it again and it worked. It helps if you change the suggested column letters (in your sample code) to reflect the correct column letters in the worksheet. So your last solution is a good one.

Thank you again.
Sincerely,
Fred

 

[Domenic]

Glad to hear that you were able to resolve it. Thanks for the feedback.

Cheers!