[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

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?
0
Shums
Asked:
Shums
  • 8
  • 8
  • 6
2 Solutions
 
aikimarkCommented:
Please post a representative sample of the data in a workbook with what you have and what you need in different worksheets.
0
 
Rob HensonIT & Database AssistantCommented:
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.
0
 
ShumsAsst. Financial ControllerAuthor Commented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rob HensonIT & Database AssistantCommented:
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.
0
 
ShumsAsst. Financial ControllerAuthor Commented:
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
0
 
ShumsAsst. Financial ControllerAuthor Commented:
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.
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
aikimarkCommented:
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

Open in new window

0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
ShumsAsst. Financial ControllerAuthor Commented:
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
0
 
Rob HensonIT & Database AssistantCommented:
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,COUNTA(Data!A:A),8)

Then in the Pivot Wizard, set the Range to =PivotData
0
 
aikimarkCommented:
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

Open in new window

0
 
ShumsAsst. Financial ControllerAuthor Commented:
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
0
 
ShumsAsst. Financial ControllerAuthor Commented:
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
0
 
aikimarkCommented:
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?
0
 
aikimarkCommented:
This will prompt for dates and place the result in Sheet4.  It will create Sheet3 and Sheet4, if necessary.  It will delete Sheet3.
Sub Q_28984723()
    Dim wksSrc As Worksheet
    Dim wksTgt As Worksheet
    Dim rngSrc As Range
    Dim rngTgt As Range
    Dim rng As Range
    Dim vDate As Variant
    
    Set wksSrc = Worksheets("Data")
    On Error Resume Next
    
    Application.ScreenUpdating = False
    Set wksTgt = Worksheets("Sheet4")
    If Err <> 0 Then
        Set wksTgt = Worksheets.Add
        wksTgt.Name = "Sheet4"
        Err.Clear
    Else
        wksTgt.UsedRange.ClearContents
    End If
    Set wksTgt = Worksheets("Sheet3")
    If Err <> 0 Then
        Set wksTgt = Worksheets.Add
        wksTgt.Name = "Sheet3"
        Err.Clear
    Else
        wksTgt.UsedRange.ClearContents
    End If
    Set rngSrc = wksSrc.Range("A2")
    
    wksTgt.Range("K1:L1") = Array("Start Date", "End Date")
    vDate = Application.InputBox("Please Enter Start Date (m/d/yyyy)", "Start Date Prompt")
    If IsDate(vDate) Then
        wksTgt.Range("K2").Value = ">=" & vDate
    Else
        MsgBox "Non-date value entered. Please try again"
        Application.ScreenUpdating = True
        Exit Sub
    End If
    vDate = Application.InputBox("Please Enter End Date (m/d/yyyy)", "End Date Prompt")
    If IsDate(vDate) Then
        wksTgt.Range("L2").Value = "<=" & vDate
    Else
        MsgBox "Non-date value entered. Please try again"
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    wksSrc.UsedRange.AdvancedFilter xlFilterCopy, wksTgt.Range("K1:L2"), wksTgt.Range("A3")

    wksTgt.Sort.SortFields.Clear
    wksTgt.Sort.SortFields.Add Key:=wksTgt.Range(wksTgt.Range("A4"), wksTgt.Range("A4").End(xlDown)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    wksTgt.Sort.SortFields.Add Key:=wksTgt.Range(wksTgt.Range("B4"), wksTgt.Range("B4").End(xlDown)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    wksTgt.Sort.SortFields.Add Key:=wksTgt.Range(wksTgt.Range("C4"), wksTgt.Range("C4").End(xlDown)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    wksTgt.Sort.SortFields.Add Key:=wksTgt.Range(wksTgt.Range("D4"), wksTgt.Range("D4").End(xlDown)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    wksTgt.Sort.SortFields.Add Key:=wksTgt.Range(wksTgt.Range("E4"), wksTgt.Range("E4").End(xlDown)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    wksTgt.Sort.SortFields.Add Key:=wksTgt.Range(wksTgt.Range("F4"), wksTgt.Range("F4").End(xlDown)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With wksTgt.Sort
        .SetRange wksTgt.Range(wksTgt.Range("A3"), wksTgt.Range("G3").End(xlDown))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    wksTgt.Range("H3").Value = "Key"
    wksTgt.Range("H4").Formula = "=A4&B4"
    Set rngTgt = wksTgt.Range("H4")
    rngTgt.AutoFill wksTgt.Range(rngTgt, rngTgt.Offset(0, -1).End(xlDown).Offset(0, 1))
    Set rngTgt = wksTgt.Range("H3").CurrentRegion
    rngTgt.Subtotal GroupBy:=8, Function:=xlSum, TotalList:=Array(7), _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    rngTgt.AutoFilter 8, "*Total"
    
    For Each rng In wksTgt.Range(rngTgt.Offset(1), rngTgt.Cells(1, 8).End(xlDown)).SpecialCells(xlCellTypeVisible).Rows
'        Stop
        rng.Cells(1, 1).FormulaR1C1 = "=r[-1]c"
        rng.Cells(1, 2).FormulaR1C1 = "=r[-1]c"
        rng.Cells(1, 3).Value = WorksheetFunction.Min(rng.Cells(1, 7).Precedents.Offset(0, -4))
        rng.Cells(1, 4).Value = WorksheetFunction.Min(rng.Cells(1, 7).Precedents.Offset(0, -3))
        rng.Cells(1, 5).Value = WorksheetFunction.Max(rng.Cells(1, 7).Precedents.Offset(0, -2))
        rng.Cells(1, 6).Value = WorksheetFunction.Max(rng.Cells(1, 7).Precedents.Offset(0, -1))
        rng.Cells(1, 7).Formula = Replace(rng.Cells(1, 7).Formula, "SUBTOTAL(9,", "SUM(")
    Next
    
    wksTgt.Range(rngTgt, rngTgt.Cells(1, 8).End(xlDown)).SpecialCells(xlCellTypeVisible).Rows.Copy Worksheets("Sheet4").Range("A3")
    Set wksTgt = Worksheets("Sheet4")
    wksTgt.Range("H3").End(xlDown).EntireRow.Delete
    wksTgt.Columns("H").Delete
    
    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.DisplayAlerts = False
    Worksheets("Sheet3").Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
ShumsAsst. Financial ControllerAuthor Commented:
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
0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
aikimarkCommented:
@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.
0
 
Rob HensonIT & Database AssistantCommented:
I think I may have come up with a way round it. Working on it now, might take me an hour or so.
1
 
Rob HensonIT & Database AssistantCommented:
See attached updated file.

Helper columns inserted to left of data:

1) Each line of data now has an ID in column A with Function & Activity and count of occurence of the combination of Function and Activity.
2) Each line has a session ID in column B. This looks at the pevious occurence of the matching function and activity and if it is just the row above it uses the same session ID otherwise it increments the session ID up by 1.
3) I have moved the Month column to column C so that your original data entries are now together.

This does rely on the data being sorted in order by Function then Start Date then Start Time. This appears to be a tracking log of some sort so I assume the entries would probably be entered in order anyway. If not we can write some simple code that would sort the data and then refresh the pivot table.

If you convert the data to a table (rather than standard list) the formulas in columns A to C will copy down as required and setting the pivot to look at the table will ensure it includes the new data. I have done this in the attached.

Hope this helps. This is about as far as I can go with this.

Thanks
Rob H
Merging-Row-Sample--1-.xlsx
0
 
ShumsAsst. Financial ControllerAuthor Commented:
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now