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]

Assuming that Column A to Column F contains the data, and Row 1 contains the headers, try...

Sub test()

Dim LastRow As Long
Dim i As Long

Application.ScreenUpdating = False

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

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

 

[pawville]

Thank you Domenic,
I will try this tomorrow. Your assumption regarding row headers and columns was correct. My Data has many more columns so can I replace the "E" and the "F" in your code to reflect the appropriate columns that contain the dates?

Thanks again.
Sincerely,
Fred

 

[Domenic]

Thank you Domenic,
I will try this tomorrow.

You're very welcome!

...can I replace the "E" and the "F" in your code to reflect the appropriate columns that contain the dates?

Yes you can...

 

[pawville]

Thank you again Domenic!
At quick glance it appears to have worked perfectly. I will look at it in more detail just to be sure. This process will be repetitive on a monthly basis where I will have to add new data to previously consolidated data. I have combined two files and run the macro and have gotten an error:

Run-time Error '13':
Type Mismatch

I take this to mean that somewhere in my separate spreadsheets the data type for a field does not match (for example in one spreadsheet SSN is stored as text and in the other it is stored as a number). Would you agree?

If I change the data type to be the same (text and text) in my combined spreadsheet and run the macro, will that eliminate the error?

Thank you again.
Sincerely,
Fred

 

[Domenic]

The error likely occurs because one or more dates from your second file are not true date values. Two questions...

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

2) What does the following formula return?

=ISNUMBER(E100)

...where E100 represents a cell that contains a supposed date from your second file.

 

[pawville]

For the two columns that the code is checking, they are all stored as numbers (the value for the formula =isnumber(e100) is "TRUE".

I do have some other date columns which returned a "FALSE" value. All of them appear to be null or blank.

Does that help?

Thanks again.

 

[Domenic]

I do have some other date columns which returned a "FALSE" value.

Are those dates located in the target colums (Entry Date and Exit Date columns?

All of them appear to be null or blank.

Do you mean a formula blank/null string ("") ?

 

[pawville]

Hi Domenic,

Those dates are not located in the target columns. One is Date of Birth and the other is Status Date.

The cells are blank. As far as I can tell they have no value in them. I receive the data as a .dbf and open the file with excel. So I believe initially everything is formatted as text.

Let me know if I can provide additional information. Thank you again.
Sincerely,
Fred

 

[Domenic]

Just to be sure, let's try one more thing. Assuming that Column E and Column F contain the entry and exist dates, and that the data starts in Row 1, try the following formula (adjust the range, accordingly)...

=SUMPRODUCT(--ISNUMBER(E2:F100))=ROWS(E2:F100)*2

If it returns FALSE, then there's one or more dates in the range that's not a true date value and, hence, the error. What does it return?