#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Quote:
Quote:
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. |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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!
|
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Huge Thank You!
NoSparks,
This code work perfectly, I really appreciate your help. I struggled for days trying to figure this out! |
#7
|
|||
|
|||
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! |
#8
|
|||
|
|||
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!
|
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
Thanks NoSparks, I'll give this a try. I really appreciate your help!
|
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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! |
#14
|
|||
|
|||
Quote:
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 |
#15
|
|||
|
|||
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. |
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 |
data roll up under unique identifier | NewGirl | Mail Merge | 1 | 11-15-2012 04:47 PM |