Shift Data in 2 Columns to Match Another

[DSA123]

Hello Everyone,

I've run into a bit of a road block. I get a .PDF output from an accounting program and copy/paste the data into excel, then convert text to columns. I am trying to match the GL code in Column A with the totals for that specific account located in columns B and C, several rows down. Columns A, B, and C show the state of my data prior to sorting it, and the lines under "Intended Output" show how I would like the data to output.


I am trying to automate this process, so I can paste data into columns A, B, & C in the raw format and have it automatically spit out the required numbers in the format of the Intended Output. The GL codes remain the same, but the numbers and the number of rows will change. I've color coded them for ease of review.
I would appreciate any help, and I thank you in advance!

https://imgur.com/a/N7gQg6i

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub GetTotals()
   Dim Ar As Areas
   Dim i As Long
   
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Total", True, xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlConstants, xlLogical).Areas
      .Replace True, "Total", xlWhole, , False, , False, False
   End With
   Range("E2").Value = Range("A2").Value
   For i = 1 To Ar.Count
      Range("F" & i + 1).Resize(, 2).Value = Ar(i).Offset(, 1).Resize(, 2).Value
      If i < Ar.Count Then Range("E" & i + 2).Value = Ar(i).Offset(1).Value
   Next i
End Sub

 

[DSA123]

That works very well! Thank you so much, I appreciate your help immensely!

And thank you for the welcome, I would like to peruse the forums here since there is alot of good information here.

Could you provide a brief walkthrough of how this VBA works, or, if you do not have the time or inclination, could you provide educational resources on how to learn the necessary skills for this?

Thanks!

 

[Fluff]

I've added some comments HTH

Sub GetTotals()
   Dim Ar As Areas
   Dim i As Long
   
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Total", True, xlWhole, , False, , False, False                      'Replaces the word Total with True
      Set Ar = .SpecialCells(xlConstants, xlLogical).Areas                          'sets an area containing any cell in col A that contains either true or false
      .Replace True, "Total", xlWhole, , False, , False, False                      'Replaces the word True with Total
   End With
   Range("E2").Value = Range("A2").Value
   For i = 1 To Ar.Count                                                            'loops through the areas
      Range("F" & i + 1).Resize(, 2).Value = Ar(i).Offset(, 1).Resize(, 2).Value    'copies the values in col B:C (adjacent to each area) to F:G
      If i < Ar.Count Then Range("E" & i + 2).Value = Ar(i).Offset(1).Value         'copies the value below the area to col E
   Next i
End Sub