Solved

Merging multiple rows to one

Posted on 2016-11-22
22
33 Views
Last Modified: 2016-11-23
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
Comment
Question by:Shums
  • 8
  • 8
  • 6
22 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41897496
Please post a representative sample of the data in a workbook with what you have and what you need in different worksheets.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41897527
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
 
LVL 1

Author Comment

by:Shums
ID: 41897556
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41897607
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
 
LVL 1

Author Comment

by:Shums
ID: 41897629
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
 
LVL 1

Author Comment

by:Shums
ID: 41897635
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41897648
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
 
LVL 45

Expert Comment

by:aikimark
ID: 41897655
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41897656
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
 
LVL 1

Author Comment

by:Shums
ID: 41897792
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41897816
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:aikimark
ID: 41897833
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
 
LVL 1

Author Comment

by:Shums
ID: 41897866
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
 
LVL 1

Author Comment

by:Shums
ID: 41897928
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
 
LVL 45

Expert Comment

by:aikimark
ID: 41897960
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
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 200 total points
ID: 41898157
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
 
LVL 1

Author Comment

by:Shums
ID: 41898739
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41898773
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
 
LVL 45

Expert Comment

by:aikimark
ID: 41899276
@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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41899342
I think I may have come up with a way round it. Working on it now, might take me an hour or so.
1
 
LVL 31

Accepted Solution

by:
Rob Henson earned 300 total points
ID: 41899407
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
 
LVL 1

Author Closing Comment

by:Shums
ID: 41899711
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now