Macro to Merge Sequential Dates in Rows

[MSmithson]

Hello all,

Trying to write a macro that will merge sequential dates within two or more rows.

I have the columns Title, Start Date, End Date, Format, Rights1, Rights2, Rights3 and Notes. These columns can be in any column letter, which is why I’d like to use the header in row 1 to identify these columns.

If the Title matches the column below, and Rights1, Rights2, and Rights3 all match and the dates are sequential, then merge these two rows into one row with the earliest start date and the last end date in the start and end date columns. The Format and Notes columns are merged together so no data in these columns is lost in the merge.

See examples 1 and 2 in attached sheet "Sequential Dates Merge".

Notice that on the first example, the Start Date of 3/22/11 is used and the End Date is 3/30/11 is used in the merged row. This is because the End Dates of 3/25/11 and 3/26/11 are sequential. The Formats are merged together and the Notes columns are merged together keeping any formatting such as font and color.

Since I’m working with up to several thousand rows, the macro may have to start at the last row and work it’s way up, using a -1 instead of a +1 for the sequential dates.

Something like: IF (Title = Title) and (start date = start date -1) and (end date = end date -1) and (Rights1 = Rights1) and (Rights2 = Rights2) and (Rights3 = Rights3) THEN

Merge (Union) of Rows and Merge Format and Notes columns together to include any data in rows into new row.

Any help at all is most appreciated – thank you so much!

Question also posed at mrexcel: http://www.mrexcel.com/forum/showthread.php?t=537943

[Richard Buttrey]

Hi,

And what if there is a non sequential End/Start date within the same Title?

Do you
a) ignore the whole series
b) merge the rows up to the break and move to the next title
c) merge the rows up to the break and continue in the same title looking for further matches
d) something else

Regards

[MSmithson]

Thanks Richard, I should have mentioned that in my original post.

If there is a non-sequential Start/End Date within the same title, then do option c:

c) merge the rows up to the break and continue in the same title looking for further matches

If there are no sequential Start/End Dates within a title, then the macro moves on to the next title.

If there are sequential Start/End Dates within a title, but the rights do not match in any one of the three columns, then do not merge, but continue to look for further matches within the title.

Thanks for asking the question.

[Richard Buttrey]

Hi,

It's a bit rough & ready and could no doubt be smartened up a bit with a little more thought but the following seems to work as you expect.

Please Login or Register  to view this content.

Regards

[MSmithson]

Hi Richard,

Thank you so much for your help - I really appreciate the time you put into this.

One thing (and maybe I should have been more clear in my example or written it in a different way), but instead of copying and pasting all the data into a new section on the same sheet, is there any way the macro can simply adjust the existing data where it is? No need to copy or move anything. I just used the before and after in the same sheet to illustrate the difference.

Your macro seems to do all the merging and matching just fine - just wondering if there is a way we can perform these calculations on existing data and not copy or move. Does this make sense? Sorry if I had caused any confusion in my example and again, I appreciate the help.

[MSmithson]

Attached is an example: Sheet1 and Sheet1 (2) are the before and after picture of the same sheet. Title1 merges into one row because the dates are sequential and all other criteria holds true. Title2 does not because one of the rights does not match. Thanks for the help.

[Richard Buttrey]

Hi,

I'd be inclined not to try and change a range which you're currently working with. That seems to me fraught with potential problems.

However you could achieve the same result, finishing the macro with a deletion of the original data and then cutting and pasting the resultant data back in place of the original.

Regards

[MSmithson]

True, the copy, change, then delete old data could work.

Or..is there any way we could move this data onto a new worksheet? As in, open up a new tab and paste the new adjusted info there?

[Richard Buttrey]

Hi,

Yes indeed.

Just add the following code after the last 'Loop' instruction

Please Login or Register  to view this content.

Regards

[snb]

or

Please Login or Register  to view this content.

[MSmithson]

Thanks SNB, your code seems to do all the matching and merging correctly, but it also gets rid of all formatting such as fonts and colors. See attached file (your code is added). It removes all formatting from the header and notes columns. Any way to keep this? Thanks for your help.

[MSmithson]

Thanks Richard, however your code does not seem to do anything...the macro just stops after it merges the cells and copys the info into the same sheet. I also tried a few variations on the code and couldn't get it to delete the old rows - are the new cells somehow locked or something that prevents deleting the old cells? Thanks for the help.

[Richard Buttrey]

Hi,

Sorry, I'd forgotten that the macro finishes as soon as the If Test just after the Do While....is satisfied. This is where the extra lines are needed. Hence add the following 6 lines immediately after the Do While...

Please Login or Register  to view this content.

[snb]

Have you any ideas yourself?
For instance copying the 'fieldnames' from one sheet to another ?
I have no clue on the formatting of cells and the relation between the formatting and the content.

As you have undoubtedly noticed analysing my suggestion, it only handles the content of the cells.

[MSmithson]

Thanks for the updated code - this works great with the copying and deleting of rows.

However, after running a few tests, i noticed the macro does something strange with larger amounts of data - it converts the start date to 12:00:00 AM. (see attached file with your macro attached). Been trying to figure out if its some sort of formatting problem or extra spaces but strange to see what it does to the start dates. Thanks so much for your time on this.

[MSmithson]

Richard, it's like the start date values get reset to 12:00:00 for some reason - going through the code now, but can't seem to find the place where it converts back to a default time/date.

[Richard Buttrey]

Hi,

Try the following. I've put in a small modification.

Please Login or Register  to view this content.

[MSmithson]

Hi Richard,

Which part of the code did you change - that got rid of all rows except the ones that were merged (and it only merged two rows, not three). see attached file.

Maybe there's some way to add in the code - if no dates merge then go to next title?

Like If not sequential, then let stand?

[MSmithson]

Hi Richard, I think I see what the macro is doing - if it finds a sequential date and merges the row, the macro then somehow autofills the start date column with the merged row start date above all the way down to the next merged row.

At this point, I'm not so concerned about the cutting and pasting or moving data to new sheets as I am about the sequential dates matching/merging.

[MSmithson]

Hi SNB,

I took a look at your code again and added this line: .Cells(1).Font.ColorIndex = .Cells(UBound(sq)).Font.ColorIndex

Unfortunately it does not do what I'd like, but I've been trying to find a way to use the Font.ColorIndex command to keep the fonts consistent. If you look at Richard's code, he has a command around the middle of the code that reads:

rTempNotes.Characters(Start:=lTempStart + 1, Length:=Len(stNotes)).Font.ColorIndex = Cells(lCount, lColStart + 7).Font.ColorIndex

Wondering if there is any way to modify/incorporate this into your macro.

Please Login or Register  to view this content.

[MSmithson]

Hi SNB,

Your code is very close to working perfectly. Aside from the color/font issue (which I'm working on), the one thing i notice is that occasionally when running the macro against data with dates that are formatted differently, i get the error message: "Type Mismatch" and it highlights this row of code:

If sq(j, 1) & sq(j, 5) & sq(j, 6) & sq(j, 7) = sq(j + 1, 1) & sq(j + 1, 5) & sq(j + 1, 6) & sq(j + 1, 7) And sq(j + 1, 2) - sq(j, 3) = 1 Then

Any way to put in an error message that says if Type Mismatch found, then go to next Title? Or, possibly automatically format all dates in the Start and End Date columns with a .value command or something similar?

Please Login or Register  to view this content.

[snb]

You forgot tot psecify what you mean by 'formatted differently'

But try:

Please Login or Register  to view this content.

[MSmithson]

Thanks SNB! I meant the format of the Start and End Dates, however I found a way to format them correctly so it's not an issue anymore.

Just trying to work out the font/color formatting issue. Your latest macro seems to keep the formatting of the individual cells, not the text in the cell (maybe I should have specified or been more clear). But If any text moves (if there are sequential dates and the rows are merged), I'd like the individual text to keep the same formatting (font, color), not the formatting to be kept with specific rows, cells. Does that make sense?

I've been playing around with this bit of code trying to incorporate it:

Please Login or Register  to view this content.

Basically something that says keep font, text, color the same. I'm thinking there has to be something with a .text or .characters command line.

[snb]

My code only uses the values in cells.
You didn't specify which cells/columns have a special formatting (fontsize, fontname,interiorcolor,fontcolor) and what the variation in formatting will be.

Don't look for any solution using the array sq in my code. That's fruitless because impossible.
If formatting is so important we have to create a workaround. That's why I need more information.

[MSmithson]

I'll simplify the problem - we don't need to worry about the 3 Rights columns, those don't matter.

All that matters is the Start Date, the End Date the Format column and the Notes column.

What I'd like to do is merge 2 or more rows if the dates in the start and end dates columns are sequential (just like before). The two columns that I'd like to keep the formatting are: the Format and Notes columns. Formatting includes font color and bold/not bold. So, if the text is a bold blue font to start, the macro keeps that text bold blue when it merges into a single row.

See attached file DatesMerge for examples. Sheet1 is the before macro picture, Sheet2 is the after macro picture. Title1 merges from two rows into one row because it has one set of sequential dates. Title2 and Title3 merge from 3 rows down to 1 row each because they both have two sets of sequential start and end dates.

The formatting of the notes and format text is kept even after merging into a new row.

I appreciate your time spent on this and all the help - thank you!