Merging-Splitting-Multiple-Rows
Dear Experts,
I am stuck with merging and splitting rows.
1. If Column J has a value of days of particular month (suppose attached file has first non-blank cell value in Column E, which is start date is 01-10-2016, VBA must look in column J the number of days for October which is 31), if it finds the matching days for particular month, then it must merge all the rows corresponding to that particular Function as per their activity and Project to one row and changing Start Date to the first day of the particular month (in this case, its 01-10-2016), start time to be 00:00:00, End Date should be the last day of the particular month (in this case, its 31-10-2016), End time should be 23:59:00. For eg, Function 241014 with Project 152 and Activity CHIRE, which has continuation of Start Date and time till the end date and time.
2. If Column J doesn't have the matching days for particular month, then its must split rows as per its continuation. For eg, Function 241024 with Project 153 and Activity CHIRE starts at 01-10-2016 and start time is 00:00:00 till 12-10-2016 and end time 10:30:00 then again it starts from 17-10-2016 and start time 00:00:00 till 31-10-2016 and end time 23:59:00. This break of time must be in two rows. The difficult one is Function 241003 with Project 2118 and Activity CHIRE starts at 01-10-2016 and start time is 00:00:00 till 04-10-2016 and end time is 19:20:00, then it starts again on 05-10-2016 with start time 06:00:00 till 17-10-2016 with end time 09:00:00, then it starts again on 18-10-2016 with start time 14:30:00 till 20-10-2016 with end time 06:30:00. This break of time must be in three rows.
3. If any function with same project with Activity like Lodging, Meals & Meal2 must be merged in one row with total of Column I. For eg, Function 191001 with project 158 and Activity Array (Lodging, Meals & Meal2) and changing Start Date to the first day of the particular month (in this case, its 01-10-2016), start time to be 00:00:00, End Date should be the last day of the particular month (in this case, its 31-10-2016), End time should be 23:59:00.
All above criteria is highlighted in expected results sheet, like 1st criteria highlighted as green, 2nd criteria highlighted as mustard & 3rd criteria highlighted as yellow. Critical breaks in 2nd Criteria is highlighted as Blue.
Kindly assist in attached sheet, preferably VBA.
Consolidate-Rows.xlsx
I am stuck with merging and splitting rows.
1. If Column J has a value of days of particular month (suppose attached file has first non-blank cell value in Column E, which is start date is 01-10-2016, VBA must look in column J the number of days for October which is 31), if it finds the matching days for particular month, then it must merge all the rows corresponding to that particular Function as per their activity and Project to one row and changing Start Date to the first day of the particular month (in this case, its 01-10-2016), start time to be 00:00:00, End Date should be the last day of the particular month (in this case, its 31-10-2016), End time should be 23:59:00. For eg, Function 241014 with Project 152 and Activity CHIRE, which has continuation of Start Date and time till the end date and time.
2. If Column J doesn't have the matching days for particular month, then its must split rows as per its continuation. For eg, Function 241024 with Project 153 and Activity CHIRE starts at 01-10-2016 and start time is 00:00:00 till 12-10-2016 and end time 10:30:00 then again it starts from 17-10-2016 and start time 00:00:00 till 31-10-2016 and end time 23:59:00. This break of time must be in two rows. The difficult one is Function 241003 with Project 2118 and Activity CHIRE starts at 01-10-2016 and start time is 00:00:00 till 04-10-2016 and end time is 19:20:00, then it starts again on 05-10-2016 with start time 06:00:00 till 17-10-2016 with end time 09:00:00, then it starts again on 18-10-2016 with start time 14:30:00 till 20-10-2016 with end time 06:30:00. This break of time must be in three rows.
3. If any function with same project with Activity like Lodging, Meals & Meal2 must be merged in one row with total of Column I. For eg, Function 191001 with project 158 and Activity Array (Lodging, Meals & Meal2) and changing Start Date to the first day of the particular month (in this case, its 01-10-2016), start time to be 00:00:00, End Date should be the last day of the particular month (in this case, its 31-10-2016), End time should be 23:59:00.
All above criteria is highlighted in expected results sheet, like 1st criteria highlighted as green, 2nd criteria highlighted as mustard & 3rd criteria highlighted as yellow. Critical breaks in 2nd Criteria is highlighted as Blue.
Kindly assist in attached sheet, preferably VBA.
Consolidate-Rows.xlsx
Martin Liss
I started on your step 1 by sorting the CurrentMonth sheet by columns A to D and then summarizing when B, C or D changes. When I do that I don't get the same results as you do so please restate when I should show the summarized row.
ASKER
Martin,
It should be sorted as Column D, B, C & then E
It should be sorted as Column D, B, C & then E
So the Sr.No. is not part of the sort?
ASKER
not at all, its just the numbering after result
For eg, Function 241024 with Project 153 and Activity CHIRE starts at 01-10-2016 and start time is 00:00:00 till 12-10-2016 and end time 10:30:00 then again it starts from 17-10-2016 and start time 00:00:00 till 31-10-2016 and end time 23:59:00.Will the start again time always be 00:00:00?
In the attached workbook I copied you CurrentMonth and ExpectedResult sheets as backup. They are named CurrentMonth (2) and ExpectedResult (2). My output is in the new ExpectedResult sheet. It needs more work because I'm producing two more rows than you but I won't be able to figure out why until tomorrow. You'll find that the code is protected. It won't be in my next version. I'll you the password in an EE message.
28993659.xlsm
28993659.xlsm
ASKER
Very Good Work Martin,
I have compared with Manual sheet and found below differences:
1. Function 241005 with Project 186 and Activity CHIRE has start date 04-10-2016 and start time is 19:20:00 which has continuation until end date 31-10-2016 and end time 23:59:00. It should be merged in 1 row, but as per VBA result it has split in two rows. Same case with Function 191001 & 171003.
2. Function 361001 with Project 222 and Activity CHIRE has start date 01-10-2016 and start time is 00:00:00 which has continuation until end date 31-10-2016 and end time 23:59:00 and same function with same project but Activity CRW1 has same start date & time with end date & time. These should split in two rows with 31 sum, but as per VBA its merged to one row with 62 sum.
Please check and advice.
I have compared with Manual sheet and found below differences:
1. Function 241005 with Project 186 and Activity CHIRE has start date 04-10-2016 and start time is 19:20:00 which has continuation until end date 31-10-2016 and end time 23:59:00. It should be merged in 1 row, but as per VBA result it has split in two rows. Same case with Function 191001 & 171003.
2. Function 361001 with Project 222 and Activity CHIRE has start date 01-10-2016 and start time is 00:00:00 which has continuation until end date 31-10-2016 and end time 23:59:00 and same function with same project but Activity CRW1 has same start date & time with end date & time. These should split in two rows with 31 sum, but as per VBA its merged to one row with 62 sum.
Please check and advice.
When I do a cell by cell comparison between your ExpectedResults and mine, I find that in row 3 you have an end date of 10/12/2016 but I have 10/21/2016. Here is the data.
This I believe is one of the times when the data should be split. I assume that the first three rows should be in the first part of the split and the other two in the second, so shouldn't the end date of the first part be 10/21/2016 since that's the end date of the third row?
This I believe is one of the times when the data should be split. I assume that the first three rows should be in the first part of the split and the other two in the second, so shouldn't the end date of the first part be 10/21/2016 since that's the end date of the third row?
ASKER
1. Function 241005 with Project 186 and Activity CHIRE has start date 04-10-2016 and start time is 19:20:00 which has continuation until end date 31-10-2016 and end time 23:59:00. It should be merged in 1 row, but as per VBA result it has split in two rows. Same case with Function 191001 & 171003.This one I understood as you are looking for 00:00:00 corresponding with 23:59:00, if its not matching, then with wont merge.
ASKER
Good catch Martin,
Yes this should be 10/12/2016 not 10/21/2016
Yes this should be 10/12/2016 not 10/21/2016
Please verify that the End Time and Qty are also incorrect.
ASKER
Yes first row should have End time 10/12/2016 and end time 10:30:00 and qty should be 11.44.
Second row should have start time 10/17/2016 and start time 00:00:00 with end day 10/31/2016 and end time 23:59:00 and qty should be 14.63
Second row should have start time 10/17/2016 and start time 00:00:00 with end day 10/31/2016 and end time 23:59:00 and qty should be 14.63
1. Function 241005 with Project 186 and Activity CHIRE has start date 04-10-2016 and start time is 19:20:00 which has continuation until end date 31-10-2016 and end time 23:59:00. It should be merged in 1 row, but as per VBA result it has split in two rows.I don't understand why it should be merged. My understanding is that when the Project, Activity and Function are the same but the Start Time is less than the Start Time in the previous row (like in rows 177 and 178) that the data should be split. Please explain the difference between the two sets of data.
ASKER
The Data in Current month what you see is updated weekly. If you see my initial post
This is the tricking part.
If any function with same project and activity doesn't have continuation every week, then we have to split as per their break time.
2. If Column J doesn't have the matching days for particular month, then its must split rows as per its continuation. For eg, Function 241024 with Project 153 and Activity CHIRE starts at 01-10-2016 and start time is 00:00:00 till 12-10-2016 and end time 10:30:00 then again it starts from 17-10-2016 and start time 00:00:00 till 31-10-2016 and end time 23:59:00. This break of time must be in two rows. The difficult one is Function 241003 with Project 2118 and Activity CHIRE starts at 01-10-2016 and start time is 00:00:00 till 04-10-2016 and end time is 19:20:00, then it starts again on 05-10-2016 with start time 06:00:00 till 17-10-2016 with end time 09:00:00, then it starts again on 18-10-2016 with start time 14:30:00 till 20-10-2016 with end time 06:30:00. This break of time must be in three rows.
This is the tricking part.
If any function with same project and activity doesn't have continuation every week, then we have to split as per their break time.
ASKER
Martin,
See below link, Rob Heson helped to get some where started, this may help you too:
Merging multiple rows to one
As its formula based, I don't want to refer, I need VBA to get started.
See below link, Rob Heson helped to get some where started, this may help you too:
Merging multiple rows to one
As its formula based, I don't want to refer, I need VBA to get started.
Are you saying that the Start Time has nothing to do with splitting? If so I think I understand what needs to be done but please tell me what day is the first day of the week? Is it Monday? Also is Nov 2, 2016 in Week 1 because it's in the first week in November, or is it week in 40 because it's in the 40th week of the year
ASKER
Start Date and Time is the first date and time of particular month.
I don't think that answered my question so let me ask it in a different way.
Given the data that I posted in post ID: 41951730, why do I split the data between the 3rd and 4th rows? Is it
1) because the start time in row 4 is less than the start time in row 3, or
2) is it because you are saying that it has something to do with the week numbers for Nov 17 and Nov 22, or
3) there's some other reason?
Given the data that I posted in post ID: 41951730, why do I split the data between the 3rd and 4th rows? Is it
1) because the start time in row 4 is less than the start time in row 3, or
2) is it because you are saying that it has something to do with the week numbers for Nov 17 and Nov 22, or
3) there's some other reason?
ASKER
Sorry Martin,
I don't understand, why you asked about Nov, as the data is only for Oct month.
Anyway, splitting rows are applicable only to those whose continuation of Date & Time doesn't follow in the next row.
For Function 241024 with same project 153 and activity CHIRE, first row is having full week working, second row end date is 10/12/2016 (and not the 10/14/2016) and end time is 10:30:00 (and not the 23:59:00), then 3rd row its start date is 10/17/2016 (and not the 10/15/2016) and start time is 09:00:00 (and not the 00:00:00).
Yes you were right, even my start date for any month would be 01st day of the month, weekly splits are from Saturday to Friday. If any month doesn't ends on Friday, the last date still would be the last day of any month and if any month doesn't starts on Saturday, the first date still would be the first day of the any month and first week ends on Friday.
Martin,
You have already done a great job, if you would be able to just edit your VBA code for those functions with same projects and activity equals the days of any month and Meals, Lodging, Meal2 data to be merged in one row, rest they all can be displayed with the weekly splits. I will happily accept your solution.
Please do the needful.
I don't understand, why you asked about Nov, as the data is only for Oct month.
Anyway, splitting rows are applicable only to those whose continuation of Date & Time doesn't follow in the next row.
For Function 241024 with same project 153 and activity CHIRE, first row is having full week working, second row end date is 10/12/2016 (and not the 10/14/2016) and end time is 10:30:00 (and not the 23:59:00), then 3rd row its start date is 10/17/2016 (and not the 10/15/2016) and start time is 09:00:00 (and not the 00:00:00).
Yes you were right, even my start date for any month would be 01st day of the month, weekly splits are from Saturday to Friday. If any month doesn't ends on Friday, the last date still would be the last day of any month and if any month doesn't starts on Saturday, the first date still would be the first day of the any month and first week ends on Friday.
Martin,
You have already done a great job, if you would be able to just edit your VBA code for those functions with same projects and activity equals the days of any month and Meals, Lodging, Meal2 data to be merged in one row, rest they all can be displayed with the weekly splits. I will happily accept your solution.
Please do the needful.
I'm sorry, I should have said October.
For Function 241024 with same project 153 and activity CHIRE, first row is having full week working, second row end date is 10/12/2016 (and not the 10/14/2016) and end time is 10:30:00 (and not the 23:59:00), then 3rd row its start date is 10/17/2016 (and not the 10/15/2016) and start time is 09:00:00 (and not the 00:00:00).I can see those things but please just give me the generalized reason why we split between the 3rd and 4th rows. In other words something like "When Project, Activity and Function are the same but <some column name(s)> are <describe situation> we need to split the rows. For example (and this is most likely wrong) "when the Project, Activity and Function are the same but the Start Time of a row is less than the Start Time in the previous row then the data should be split.
Meals, Lodging, Meal2 data to be merged in one rowDon't I already do that?
ASKER
Martin,
The easiest way would be, if any Function with same Project & Activity doesn't have start time in Column F is not "00;00:00" & End Time in Column H in not "23:59:00" then we split rows. I think we must have helper column for such sessions.
I added a Column K to group Lodging, Meals & Meal2 to be considered as Meals with below formula:
Then I added another Column L to determine what I needed, with below formula which your VBA should do :
Then I added another Column M for Meals with below formula:
I cannot incorporate these formulas in VBA.
Hope this will help editing your current VBA.
Consolidate-Rows-28993659.xlsm
The easiest way would be, if any Function with same Project & Activity doesn't have start time in Column F is not "00;00:00" & End Time in Column H in not "23:59:00" then we split rows. I think we must have helper column for such sessions.
I added a Column K to group Lodging, Meals & Meal2 to be considered as Meals with below formula:
=IF(OR($C2="Lodging",$C2="Meals",$C2="Meal2"),"Meals",$C2)Then I added another Column L to determine what I needed, with below formula which your VBA should do :
=IF($K2="Meals","",IF(AND($B2=$B3,$C2=$C3,$D2=$D3),"",IF(SUMIFS($I:$I,$D:$D,$D2,$B:$B,$B2,$C:$C,$C2)=0,"",IF(SUMIFS($I:$I,$D:$D,$D2,$B:$B,$B2,$C:$C,$C2)=((EOMONTH($E2,0)-($E2-DAY($E2)+1)+1)),"Merge in one row","Split as per breaks"))))Then I added another Column M for Meals with below formula:
=IF($K2<>"Meals","",IF(SUMIFS($I:$I,$K:$K,$K2,$D:$D,$D2,$B:$B,$B2)=SUMIFS($I:$I,$K:$K,$K3,$D:$D,$D3,$B:$B,$B3),"","Merge with sum of Qty"))I cannot incorporate these formulas in VBA.
Hope this will help editing your current VBA.
Consolidate-Rows-28993659.xlsm
ASKER
Ok I added combined Action column in L as per below formula:
=IF($K2="Meals",IF(SUMIFS($I:$I,$K:$K,$K2,$D:$D,$D2,$B:$B,$B2)=SUMIFS($I:$I,$K:$K,$K3,$D:$D,$D3,$B:$B,$B3),"","Merge with sum of Qty"),IF(AND($B2=$B3,$C2=$C3,$D2=$D3),"",IF(SUMIFS($I:$I,$D:$D,$D2,$B:$B,$B2,$C:$C,$C2)=0,"",IF(SUMIFS($I:$I,$D:$D,$D2,$B:$B,$B2,$C:$C,$C2)=((EOMONTH($E2,0)-($E2-DAY($E2)+1)+1)),"Merge in one row","Split as per breaks"))))
I just wanted to tell you that I've essentially rewritten my code and I'm very close to a complete solution.
ASKER
Thanks Martin.
You are very close, great work.
You are very close, great work.
OK, it's possible that we're done. In this version I use column L as a helper column. It temporarily contains the value "start" on each row in the CurrentMonth sheet that is the first row of a set of rows that are going to be summarized. I also added a sub called CheckForErrors that row by row, cell by cell, compares your expected results to mine, and if they don't agree then a line is written to the Immediate Window. When you look at that window you'll find that we do have a few differences. Please carefully examine those differences and if you think my results are wrong then please tell me in general terms why they are wrong. For example in Row 50 Col "I" of the expected results you have 3 but I have 31 and I don't see any reason why it should be 3.
28993659a.xlsm
28993659a.xlsm
ASKER
Fantastic,
The only problem is when I double click to open the file, it open's 3 workbook, I checked in Workbook Open events, but there's no code.
Please advice, it must be through module.
The only problem is when I double click to open the file, it open's 3 workbook, I checked in Workbook Open events, but there's no code.
Please advice, it must be through module.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect Martin,
You are copying and pasting with format, please advice which line I must change to copy and just paste special formulas.
You are copying and pasting with format, please advice which line I must change to copy and just paste special formulas.
' Summarize the previous unsummarized rows
.Cells(lngStartRow, "A").EntireRow.Copy
Sheets("ExpectedResult").Cells(Sheets("ExpectedResult").Range("A1048576").End(xlUp).Row + 1, "A").PasteSpecial Paste:=xlPasteAllExceptBorders' Start looping through all the data rowws on the CurrentMonth sheet
strOldProject = .Cells(2, "B")
strOldFunction = .Cells(2, "D")
Sheets("ExpectedResult").UsedRange.Cells.ClearContents
lngStartRow = 2
dteFirstOfMonth = DateSerial(Year(.Range("E2")), Month(.Range("E2")), 1)
.Cells(1, "A").EntireRow.Copy
Sheets("ExpectedResult").Cells(1, "A").PasteSpecial Paste:=xlPasteAllExceptBorders
Please be more specific about what you want to paste. Maybe it would be easier for you to tell me what you don't want to paste. Do you want to paste everything except the fill color?
ASKER
you want to paste everything except the fill color?
Yes Please. No Colors in expected result sheet.
The second one of those pieces of code you pasted just copies the heading and so I assume you don't want to change that, but after line 3 in the first one, add these lines.
With Sheets("ExpectedResult").Rows(Sheets("ExpectedResult").Range("A1048576").End(xlUp).Row).Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End WithASKER
Thanks a lot Martin,
You are genius.
It was tricking, but you made it easy.
You are genius.
It was tricking, but you made it easy.
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016