Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-22-2019, 01:55 PM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default Macro to combine data from multiple rows if same identifier


I am trying to develop a macro to find rows in my spreadsheet that share the same account number (in col S), then combine the data into a single row and delete the extra row.

My data resides in columns A-Y and contains twelve months of numerical data (col F-Q) where some rows have Actual values and some rows have Forecast values. In the rows with the Actuals, the forecast months are all zero, and the rows with Forecasts, the actuals months are all zero - no overlapping data. The forecast rows also contain other relevant data in columns A-E so the macro should probably add the Actuals to the Forecast row and delete the Actuals row.

The spreadsheet is typically around 2000 rows long, but the actual size varies each month. And each month the columns containing the Actuals will increase while the columns with Forecasts will decrease until eventually there are no Forecasts. Hoping you can help me solve this

Any help would be greatly appreciated!
Reply With Quote
  #2  
Old 05-22-2019, 03:47 PM
NoSparks NoSparks is offline Macro to combine data from multiple rows if same identifier Windows 7 64bit Macro to combine data from multiple rows if same identifier Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
I am trying to develop a macro to find rows in my spreadsheet that share the same account number (in col S)
a filter will do this, as will the Range.Find / .FindNext method.
Quote:
My data resides in columns A-Y and contains twelve months of numerical data (col F-Q) where some rows have Actual values and some rows have Forecast values.
12 columns for months, what distinguishes these from being Actual or Forecast?

A great help here would come from you.
Posting a sample workbook, not all 2000 rows, that is typical of what you're starting with and containing a sheet (manually put together) to indicate what you're wanting to end up with would be extremely helpful towards getting a solution.
Reply With Quote
  #3  
Old 05-23-2019, 02:09 PM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default Example data

Here's an example of the file I am trying to combine. In column S I have created a field that combines three attributes (dept, acct and vendor) into one value. If this value exists is consecutive rows, and the two rows contain no data in overlapping months (i.e., typically the first row is forecast data and the second is actuals data with no overlapping months), then the code should copy the actuals data into the forecast row and delete the actuals row. The code needs to work regardless of which months are actuals and which are forecasts as that will change each month. Hope this helps clarify what I'm trying to do.

I appreciate your help!
Attached Files
File Type: xlsx Example data.xlsx (17.6 KB, 8 views)
Reply With Quote
  #4  
Old 05-23-2019, 09:46 PM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default

Hey NoSparks is this the kind of file you were asking me to post? Any suggestions on a potential VBA solution? Hoping you can help, thnaks!
Reply With Quote
  #5  
Old 05-23-2019, 10:17 PM
NoSparks NoSparks is offline Macro to combine data from multiple rows if same identifier Windows 7 64bit Macro to combine data from multiple rows if same identifier Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Try this on the file you've posted
Code:
Sub CombineData()
    
    Dim i As Long, lr As Long
    Dim rng As Range, cel As Range
    
Application.ScreenUpdating = False
With Sheets("Vendor Report")
    lr = .Range("S" & Rows.Count).End(xlUp).Row
    For i = lr To 3 Step -1
        If .Cells(i, "S") = .Cells(i - 1, "S") Then
            Set rng = .Range(.Cells(i, "F"), .Cells(i, "Q"))
            For Each cel In rng
                If cel.Value <> 0 And cel.Value <> "" Then
                    If cel.Offset(-1) = 0 Or cel.Offset(-1) = "" Then
                        cel.Offset(-1) = cel.Value
                    End If
                End If
            Next cel
            .Rows(i).Delete
        End If
    Next i
End With
Application.ScreenUpdating = True
    
End Sub
Reply With Quote
  #6  
Old 05-24-2019, 11:27 AM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default Huge Thank You!

NoSparks,

This code work perfectly, I really appreciate your help. I struggled for days trying to figure this out!
Reply With Quote
  #7  
Old 06-02-2019, 10:06 PM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default

NoSparks,

Been using this code to build report for the past week and came across an issue. The example data didn't reflect a few new scenarios I found in a few reports. In the original data all consecutive rows with the same account number in col S where forecast and actuals rows where the code then combined them and and then deleted the actuals row. Now I also have consecutive rows with the same account number where then are (1) two forecast rows followed by an actuals row, and (2) two forecast rows with no actuals row. In both of these scenarios the second forecast row is getting deleted. Can the code be modified to not delete second row unless its an actuals rows that has been combined withe a forecast row? I will attach a second example. Thanks!
Reply With Quote
  #8  
Old 06-02-2019, 10:10 PM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default New example data

This new example data includes the two new scenarios I described in my last post. See the tab titled Vendor report - Original and see the two highlighted rows. Thanks!
Attached Files
File Type: xlsm Example data 2.xlsm (41.6 KB, 7 views)
Reply With Quote
  #9  
Old 06-03-2019, 10:30 AM
NoSparks NoSparks is offline Macro to combine data from multiple rows if same identifier Windows 7 64bit Macro to combine data from multiple rows if same identifier Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

The original suggestion did not distinguish between actual and forcast rows, it just combined them with the assumption that there would only be 2 rows and it didn't really matter if forcast or actual was first.


To accommodate the new scenarios a helper column is used to identify the actual rows.
Have used column 30 which is "AD" but you can easily adjust it if that's an issue.
This helper column is cleared at the end of the macro.
Row 1 merged cells for Actual and Current Fcst must be correct as it's the merged columns of Actual that things are based on.
Also now it does matter that the forcast row(s) precede the actual row.


Give this a try

Code:
Sub CombineData_v2()
    
    Dim i As Long, lr As Long
    Dim actCount As Long, helpCol As Long
    
Application.ScreenUpdating = False

With Sheets("Vendor Report")
    lr = .Range("S" & Rows.Count).End(xlUp).Row
    actCount = .Range("F1").MergeArea.Columns.Count
    helpCol = 30    'column 30 is column "AD"
    
    'determine if actual and identify in helpCol
    For i = lr To 3 Step -1
        If Application.Sum(.Range(Cells(i, 6), Cells(i, 6 + actCount))) > 0 And _
                Application.Sum(.Range(Cells(i, 6 + actCount + 1), Cells(i, 17))) = 0 Then
            .Cells(i, helpCol) = "A"
        End If
    Next i
    
    'loop again and only deal with actual rows
    For i = lr To 3 Step -1
        If .Cells(i, helpCol) = "A" Then
            If .Cells(i, "S") = .Cells(i - 1, "S") Then     'Dept-Account-Vendor match
                'copy actual to forcast
                .Cells(i - 1, "F").Resize(, actCount).Value = .Cells(i, "F").Resize(, actCount).Value
                'remove the actual row
                .Rows(i).Delete
            End If
        End If
    Next i
    
    'remove helper column
    .Columns(helpCol).ClearContents
    
End With

Application.ScreenUpdating = True
    
End Sub
Reply With Quote
  #10  
Old 06-04-2019, 10:37 PM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default

Thanks NoSparks, I'll give this a try. I really appreciate your help!
Reply With Quote
  #11  
Old 06-05-2019, 12:30 AM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default Some data changes

Hey NoSparks,

I tested the code and although it ran perfectly against the example data that I provided, the actual data file has a few subtle differences that seem to effect how the code runs.

1) The merged headers are not merged anymore (merge happens later in the code after all editing is completed)

2) Data contains negative values, not just positive values

3) All month columns now contain values, blanks are now zeros.

4) I've added helper data to all actuals rows in column AD as part of my report build so no need for your code to add the "A"s to actuals rows.

I've attached a better example file, can you take a look and see what needs to be updated in the code as a result of these changes? Thanks so much for your continued help. I feel like we're really close to a solve here.
Attached Files
File Type: xlsm Example data 3.xlsm (26.7 KB, 6 views)
Reply With Quote
  #12  
Old 06-05-2019, 07:41 AM
NoSparks NoSparks is offline Macro to combine data from multiple rows if same identifier Windows 7 64bit Macro to combine data from multiple rows if same identifier Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Give this a shot
Code:
Sub CombineData_v3()
    
    Dim i As Long, lr As Long, helpCol As Long
    Dim firstFcst As Long
    
With Sheets("Vendor Report")
    lr = .Range("S" & Rows.Count).End(xlUp).Row
    firstFcst = .Cells(1, .Columns.Count).End(xlToLeft).Column
    helpCol = 30    'AD
    For i = lr To 3 Step -1
        If .Cells(i, helpCol) = "A" And .Cells(i, "S") = .Cells(i - 1, "S") Then
            .Cells(i - 1, "F").Resize(, firstFcst - 6).Value = .Cells(i, "F").Resize(, firstFcst - 6).Value
            .Rows(i).Delete
        End If
    Next i
End With
End Sub
Reply With Quote
  #13  
Old 06-10-2019, 12:56 AM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default

NoSparks,

Thanks again for hanging in with me on this one. Its been a bit of a learning curve for me and I'm still hitting a few snags with the actual data set. I ran this new code and it is successful in preserving forecast rows with a column "S" value that is the same as the one above it. Unfortunately when it finds an actual row and a forecast row with matching column S values, it is copying all 12 months from the Actuals column and overwriting data in the forecast column. Its really strange because it works perfectly in the dataset I posted.

I've been trying to figure out what's different, same columns and headers and similar data. Only thing I noticed is my dataset in excel seems to be longer than it is, meaning my scroll bar thinks there's data in about 1000 cells below my actual data. I've tried clearing them and see no values or formulas. But when I Shift-Control-End it includes a bunch of blank cells. Do you think that could be causing the issue?

Also in testing twice the code got stuck at the following line. Not sure why...

.Cells(i - 1, "F").Resize(, firstFcst - 6).Value = .Cells(i, "F").Resize(, firstFcst - 6).Value

Any suggestions where I might go next, I'm hitting a wall on this one. Thanks for your help!
Reply With Quote
  #14  
Old 06-10-2019, 06:51 AM
NoSparks NoSparks is offline Macro to combine data from multiple rows if same identifier Windows 7 64bit Macro to combine data from multiple rows if same identifier Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
I've been trying to figure out what's different, same columns and headers and similar data. Only thing I noticed is my dataset in excel seems to be longer than it is, meaning my scroll bar thinks there's data in about 1000 cells below my actual data. I've tried clearing them and see no values or formulas. But when I Shift-Control-End it includes a bunch of blank cells. Do you think that could be causing the issue?
Didn't noticed the size of the used range before.
Run this macro against the sheet and see if it rectifies things.
If it doesn't, you'll need to attach another Example data sheet that has these issues.
Code:
'CREDIT: MARK858
'https://www.mrexcel.com/forum/excel-questions/1073109-usedrange-no-longer-working.html#post5155296
Sub LoseThatWeightx()
    Dim xx As Long, LastRow As Long, LastCol As Long
    Application.ScreenUpdating = False
    With ActiveSheet
        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
        .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
    End With
    xx = Application.ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = True
End Sub
Reply With Quote
  #15  
Old 06-10-2019, 10:53 PM
Tphil413 Tphil413 is offline Macro to combine data from multiple rows if same identifier Windows 10 Macro to combine data from multiple rows if same identifier Office 2016
Novice
Macro to combine data from multiple rows if same identifier
 
Join Date: May 2019
Posts: 18
Tphil413 is on a distinguished road
Default

NoSparks,

I ran the code you provided and although it fixed the problem with the extra rows, that apparently wasn't the issue. It's still taking the actuals including the zero months and copying over all forecast months. Its like the resize command isn't working as designed. Not sure what is throwing it off. Is there an alternative to this approach to combine the actual and forecast rows? Thx.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to combine multiple rows in to one through an equation on EXCEL? emitb Excel 1 07-31-2018 04:22 PM
Combine multiple 'form data' into one all-encompassing Word document Padraig Clarke Word 1 04-19-2017 05:28 AM
Summarising data from multiple rows mardecl1 Excel 5 01-06-2017 04:05 AM
Macro to Combine Multiple Rows into One Row brunssl2 Excel Programming 10 01-13-2015 01:56 PM
Macro to combine data from multiple rows if same identifier data roll up under unique identifier NewGirl Mail Merge 1 11-15-2012 04:47 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:07 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft