Shums Faruk
asked on
Merging multiple rows to one
Dear Experts,
My Data starts as A1 has header of Function, B1 has header Activity, C1 has header Start Date, D1 has Start Time, E1 has header End Date, F1 has header End Time & G1 as header Quantity.
Suppose my I have Function in A2, Activity in B2, Multiple Start Dates from C2 to C5, Multiple Start Time from D2 to D5, Multiple End Dates from E2 to E5, Multiple End Times from F2 to F5 & Multiple Quantity from G2 to G5.
Quantity is the difference of Start Date - Start Time to End Date - End Time.
For instance, 100012 function in A2 has 01/10/2016 as start date in B2, 00:00 in C2 as start time, 07/10/2016 as end dates in D2, 23:59 in E2 as end time. and continues different dates and time until B5 to E5.
It must check the first date (not the 1st of Oct, but it would be any start date in Oct) in Start Date and last date (not the 31st of Oct, but it would be any last date in Oct) in End Date, sum the quantity for particular Function in another sheet with just 1 row with all the column headers.
Any Advice?
My Data starts as A1 has header of Function, B1 has header Activity, C1 has header Start Date, D1 has Start Time, E1 has header End Date, F1 has header End Time & G1 as header Quantity.
Suppose my I have Function in A2, Activity in B2, Multiple Start Dates from C2 to C5, Multiple Start Time from D2 to D5, Multiple End Dates from E2 to E5, Multiple End Times from F2 to F5 & Multiple Quantity from G2 to G5.
Quantity is the difference of Start Date - Start Time to End Date - End Time.
For instance, 100012 function in A2 has 01/10/2016 as start date in B2, 00:00 in C2 as start time, 07/10/2016 as end dates in D2, 23:59 in E2 as end time. and continues different dates and time until B5 to E5.
It must check the first date (not the 1st of Oct, but it would be any start date in Oct) in Start Date and last date (not the 31st of Oct, but it would be any last date in Oct) in End Date, sum the quantity for particular Function in another sheet with just 1 row with all the column headers.
Any Advice?
Please post a representative sample of the data in a workbook with what you have and what you need in different worksheets.
Sounds like you might be able to summarise the data with a pivot table but as Aikimark has suggested, a representative example is a good place to start.
ASKER
Thanks guys,
Please see attached...sample in sheet 1 and result is sheet 2.
I would prefer VBA, bcoz original data is of 15000 rows.
Merging-Row-Sample.xlsx
Please see attached...sample in sheet 1 and result is sheet 2.
I would prefer VBA, bcoz original data is of 15000 rows.
Merging-Row-Sample.xlsx
If columns A and B were fully populated with a function reference in every row a Pivot Table would be able to do exactly what you want, even with 15000 rows. The pivot would then show Min date from column C, Max Date from column D and sum of quantity from column G.
If that is something to consider, the population of columns A & B of the real data would be simple to do. Apply a filter on the data and select column A to show blanks. In the first blank cell (A3 in sample) type =A2 ie referencing the cell above and then use Ctrl + D to fill down the entire column. The formula will only be populated into the visible cells ie the blank rows and will adjust relative to the cell being populated. Unfilter and then copy paste values to make these entries static and then future entries can be made with the function in column A for every entry.
If that is something to consider, the population of columns A & B of the real data would be simple to do. Apply a filter on the data and select column A to show blanks. In the first blank cell (A3 in sample) type =A2 ie referencing the cell above and then use Ctrl + D to fill down the entire column. The formula will only be populated into the visible cells ie the blank rows and will adjust relative to the cell being populated. Unfilter and then copy paste values to make these entries static and then future entries can be made with the function in column A for every entry.
ASKER
Thanks Rob,
Initially I did tried pivot and was not giving me expected result, so I have to try some other method.
Please find attached, with Data, Pivot method & required result.
Merging-Row-Sample.xlsx
Initially I did tried pivot and was not giving me expected result, so I have to try some other method.
Please find attached, with Data, Pivot method & required result.
Merging-Row-Sample.xlsx
ASKER
Oh Yes, there should be selection of Month as well, which I will add in any cell above header row, when I run VBA, it should display required result only for that particular month.
See attached.
Amended Pivot by dragging Dates and Times into Value field and changed Field Settings to Min for Start Date & Start TIme and to Max for Finish Date & Finish Time.
For month selection I have added a column to the data rounding the start date to the first of the month and then dragged this field to a page filter in the Pivot.
Merging-Row-Sample--1-.xlsx
Amended Pivot by dragging Dates and Times into Value field and changed Field Settings to Min for Start Date & Start TIme and to Max for Finish Date & Finish Time.
For month selection I have added a column to the data rounding the start date to the first of the month and then dragged this field to a page filter in the Pivot.
Merging-Row-Sample--1-.xlsx
Please test this:
Sub Q_28984723()
Dim wksSrc As Worksheet
Dim wksTgt As Worksheet
Dim rngSrc As Range
Dim rngTgt As Range
Set wksSrc = Worksheets("Sheet1")
Set wksTgt = Worksheets("Sheet3")
Set rngSrc = wksSrc.Range("A2")
Set rngTgt = wksTgt.Range("A2")
Application.ScreenUpdating = False
Do
wksSrc.Range(rngSrc, rngSrc.Offset(0, 3)).Copy rngTgt
If rngSrc.End(xlDown).Row = wksSrc.Rows.Count Then
wksSrc.Range(rngSrc.Offset(0, 4).End(xlDown), rngSrc.Offset(0, 5).End(xlDown)).Copy rngTgt.Offset(0, 4)
Else
wksSrc.Range(rngSrc.End(xlDown).Offset(-1, 4), rngSrc.End(xlDown).Offset(-1, 5)).Copy rngTgt.Offset(0, 4)
End If
rngTgt.Offset(0, 6).FormulaR1C1 = "=ROUND((rc[-2]+rc[-1])-(rc[-4]+rc[-3]),2)"
Set rngSrc = rngSrc.End(xlDown)
Set rngTgt = rngTgt.Offset(1)
Loop Until rngSrc.Row = wksSrc.Rows.Count
Set rngSrc = wksSrc.Range("A1")
Set rngTgt = wksTgt.Range("A1")
wksSrc.Range(rngSrc, rngSrc.End(xlToRight)).Copy rngTgt
Application.ScreenUpdating = True
End Sub
To get rid of the large negative numbers in the quantity column, amend the formula to:
=IF(C2="",0,ROUND((E2+F2)- (C2+D2),2) )
Thanks
Rob H
=IF(C2="",0,ROUND((E2+F2)-
Thanks
Rob H
ASKER
Thanks Rob,
Pivot works perfect. Remember, I am using other another VBA to upload current month data at the end in Data Sheet, will Pivot works, if I just refresh?
Aikimark,
Your VBA needs some alteration, it merges data as per Function, I am really sorry that I forgot to mention, I need to merge as per Activity for particular Function. Second apology to send pivot data initially.
Please find attached Aikimark, see my data sheet and required result sheet. Your VBA works from Sheet 1 to Sheet 2. Please see if you can add another module for Required Result Sheet.
Merging-Row-Sample-VBA-.xlsm
Pivot works perfect. Remember, I am using other another VBA to upload current month data at the end in Data Sheet, will Pivot works, if I just refresh?
Aikimark,
Your VBA needs some alteration, it merges data as per Function, I am really sorry that I forgot to mention, I need to merge as per Activity for particular Function. Second apology to send pivot data initially.
Please find attached Aikimark, see my data sheet and required result sheet. Your VBA works from Sheet 1 to Sheet 2. Please see if you can add another module for Required Result Sheet.
Merging-Row-Sample-VBA-.xlsm
You can set the Data area to a Dynamic Range so that the pivot will recognise the new entries.
Create a Range Name called PivotData and use this formula to specify the Range:
=OFFSET(Data!$A$1,0,0,COUN TA(Data!A: A),8)
Then in the Pivot Wizard, set the Range to =PivotData
Create a Range Name called PivotData and use this formula to specify the Range:
=OFFSET(Data!$A$1,0,0,COUN
Then in the Pivot Wizard, set the Range to =PivotData
Sub Q_28984723()
Dim wksSrc As Worksheet
Dim wksTgt As Worksheet
Dim rngSrc As Range
Dim rngTgt As Range
Set wksSrc = Worksheets("Sheet1")
Set wksTgt = Worksheets("Sheet3")
Set rngSrc = wksSrc.Range("A2")
Set rngTgt = wksTgt.Range("A4")
Application.ScreenUpdating = False
Do
wksSrc.Range(rngSrc, rngSrc.Offset(0, 3)).Copy rngTgt
If rngSrc.End(xlDown).Row = wksSrc.Rows.Count Then
wksSrc.Range(rngSrc.Offset(0, 4).End(xlDown), rngSrc.Offset(0, 5).End(xlDown)).Copy rngTgt.Offset(0, 4)
Else
wksSrc.Range(rngSrc.End(xlDown).Offset(-1, 4), rngSrc.End(xlDown).Offset(-1, 5)).Copy rngTgt.Offset(0, 4)
End If
rngTgt.Offset(0, 6).FormulaR1C1 = "=ROUND((rc[-2]+rc[-1])-(rc[-4]+rc[-3]),2)"
Set rngSrc = rngSrc.End(xlDown)
Set rngTgt = rngTgt.Offset(1)
Loop Until rngSrc.Row = wksSrc.Rows.Count
Set rngSrc = wksSrc.Range("A1")
Set rngTgt = wksTgt.Range("A3")
wksSrc.Range(rngSrc, rngSrc.End(xlToRight)).Copy rngTgt
wksTgt.Range("C1:F1").Value = Array("Start Date", _
WorksheetFunction.Min(wksTgt.Range(wksTgt.Range("C4"), wksTgt.Range("C4").End(xlDown))), _
"End Date", _
WorksheetFunction.Max(wksTgt.Range(wksTgt.Range("E4"), wksTgt.Range("E4").End(xlDown))))
wksTgt.Range("D1,F1").NumberFormat = "m/d/yyyy"
Application.ScreenUpdating = True
End Sub
ASKER
Thanks AikiMark,
Again you are basing the initial Sheet1 data, I would prefer to base from Data Sheet to get result in Require Result Sheet.
Secondly, I would like to pull data from Data Sheet between the period of D1 (Start Date) to F1 (End Date) in Required Result Sheet, your vba fills those dates as per data of C4 and D4 of Sheet3.
Thirdly, I would like to sum quantity as per activity for particular Function separately.
Please edit code accordingly in module 2.
Merging-Row-Sample-VBA--2.xlsm
Again you are basing the initial Sheet1 data, I would prefer to base from Data Sheet to get result in Require Result Sheet.
Secondly, I would like to pull data from Data Sheet between the period of D1 (Start Date) to F1 (End Date) in Required Result Sheet, your vba fills those dates as per data of C4 and D4 of Sheet3.
Thirdly, I would like to sum quantity as per activity for particular Function separately.
Please edit code accordingly in module 2.
Merging-Row-Sample-VBA--2.xlsm
ASKER
Rob,
Sorry to bother you again, If I follow Pivot method, it will sum the quantity as per function and activity. When there is Idle in between, it will still sum the whole days between start date and end date.
Please see attached, I have highlighted 241022 Function. Which was Charter Hired from 01-10-2016 to 19-10-2016 then Idle from 19-10-2016 to 26-10-2016 then again hired from 26-10-2016 to 31-10-2016. I would need a break of such Function, 1st row should have start date and end date for 1st period then 2nd row should have 2nd period, I believe in Pivot, we cannot split.
Any Advice?
Merging-Row-Sample--1-.xlsx
Sorry to bother you again, If I follow Pivot method, it will sum the quantity as per function and activity. When there is Idle in between, it will still sum the whole days between start date and end date.
Please see attached, I have highlighted 241022 Function. Which was Charter Hired from 01-10-2016 to 19-10-2016 then Idle from 19-10-2016 to 26-10-2016 then again hired from 26-10-2016 to 31-10-2016. I would need a break of such Function, 1st row should have start date and end date for 1st period then 2nd row should have 2nd period, I believe in Pivot, we cannot split.
Any Advice?
Merging-Row-Sample--1-.xlsx
1. if your data skips across selection criteria boundaries, you probably need to start a Gig on this
2. are your dates in m/d/yyyy format or d/m/yyyy format?
2. are your dates in m/d/yyyy format or d/m/yyyy format?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very Nice Aikimark,
I am having the same problem like with Pivot Method. Its not breaking the rows as per Idle time. I have highlighted those functions, if any Function is not continuously on hire for whole month. Those on hire should be split in next row.
241003 has four breaks, it should be summarized in 4 different rows.
Merging-Row-Sample-VBA-.xlsm
I am having the same problem like with Pivot Method. Its not breaking the rows as per Idle time. I have highlighted those functions, if any Function is not continuously on hire for whole month. Those on hire should be split in next row.
241003 has four breaks, it should be summarized in 4 different rows.
Merging-Row-Sample-VBA-.xlsm
Sorry, don't know of a way to split as required for off hire time.
The activity column is currenty sorted alphabetically but you can drag the IDLECON field below the CHIRE row if that makes it any clearer. Hover the cursor over the cell border of one of the IDLECON cells, click and drag to where needed; all occurences of IDLECON will then move to below CHIRE
The activity column is currenty sorted alphabetically but you can drag the IDLECON field below the CHIRE row if that makes it any clearer. Hover the cursor over the cell border of one of the IDLECON cells, click and drag to where needed; all occurences of IDLECON will then move to below CHIRE
@Shums
This is scope creep and, thus, this will likely be my last post in this thread. You really need to hire a professional. Head over to Gigs or Live.
https://www.experts-exchange.com/gigs/
https://www.experts-exchange.com/live/
I will leave you with this helpful hint: Reveal the entire problem in your initial question text and post representative sample data.
This is scope creep and, thus, this will likely be my last post in this thread. You really need to hire a professional. Head over to Gigs or Live.
https://www.experts-exchange.com/gigs/
https://www.experts-exchange.com/live/
I will leave you with this helpful hint: Reveal the entire problem in your initial question text and post representative sample data.
I think I may have come up with a way round it. Working on it now, might take me an hour or so.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much Rob & Aikimark.
Rob,
Some Function with same activity are having a break but their total sum is 31 for the month of Oct, which should appear in one row. I would need breaks only for those whose sum for the day doesn't complete the month cycle, like for Oct, is should be 31 days, which ever Function with same Activity doesn't have 31 days as their sum, I would need multiple rows just for them.
I will carry on from here. You lessen most of my work.
Truly appreciated your effort.
Kind Regards,
Shums
Rob,
Some Function with same activity are having a break but their total sum is 31 for the month of Oct, which should appear in one row. I would need breaks only for those whose sum for the day doesn't complete the month cycle, like for Oct, is should be 31 days, which ever Function with same Activity doesn't have 31 days as their sum, I would need multiple rows just for them.
I will carry on from here. You lessen most of my work.
Truly appreciated your effort.
Kind Regards,
Shums