Help with grouping similar data together in a table

[spotty161]

Hi excel & vba experts, I am new to vba and would like to seek some help regarding a current problem I am facing.

I have a table with many columns, of which there are 4 which are of interest to me.

1. The desk code
2. The ID
3. Debit
4. Credit

Accounting knowledge would dictate that only 1 of the columns, either debit/credit will be filled with a number, while the other column would be 0.

There are many rows with similar desk codes and IDs.

What I would like to do is, for each unique combination of desk code + ID, have the net Debit or Credit amount in a single row. Thus alot of repeated rows would be eliminated. In fact, this is similar to what a pivot table achieves.

However, the output would have to be in the same format as the original file, just with much reduced number of rows, for the next step of processing.

Can anyone help me with a solution for this please? have been racking my brains for days.

Using excel 2010.

thanks!!!

 

[WarPigl3t]

Sub Macro1()
     deskCode = "A"  'Change A to whatever column the desk codes are in
     ID = "B"  'Change B to whatever column the IDs are in
     debit = "C"  'Change C to whatever column the debit amounts are in
     credit = "D"  'Change D to whatever column the credit amounts are in
     
     lastRow = Range("A" & Rows.Count).End(xlup).Row
     i = 2  'If you don't have headers, then i = 1
     Do Until i = lastRow
          c = i + 1
          Do Until c = lastRow
                If Range(deskCode & i).value = Range(deskCode & c).value And _
                     Range(ID & i).value = Range(ID & c).value Then
                     Range(debit & i).value = Range(debit & i).value + Range(debit & c).value
                     Range(credit & i).value = Range(credit & i).value + Range(credit & c).value
                     Range(deskCode & c).value = ""
                     Range(ID & c).value = ""
                     Range(debit & c).value = ""
                     Range(credit & c).value = ""
                End If
                c = c + 1
           Loop
           i = i + 1
     Loop
End Sub

Let me explain how this code works. Lets say column A is desk codes, column B is IDs, column C is debits, and column D is credits. Lets say that A2 and A5 have the same desk codes, B2 and B5 have the same IDs. The code will take C5 debit and add it to C2 debit. It will take D5 credit and add it to D2 credit. Then it will delete A5, B5, C5, and D5. So now you'll have a blank row. It will continue to find matches and eventually you'll have a bunch of blank rows where values use to be before they were merged together. I'm sure you don't want to have blank rows though so you'll need code to move the data up to get rid of blank rows. The following code is very similar to the code above. It requires 2 loops just like the last code.

x = 2  'if you don't have headers, x = 1
Do Until x = lastRow
     If Range(deskCode & x).value = "' And _
          Range(ID & x).value = "" And _
          Range(debit & x).value = "" And _
          Range(credit & x).value = "" Then
     'INSERT DELETE ROW CODE
     End If
     x = x + 1
Loop

Notice where I typed INSERT DELETE ROW CODE. I don't have it memorized. Have someone else help you delete that row if it is blank. Insert this code starting with x = 2 right after the Loop of the last code I gave you and before the End Sub of the last code I gave you. Good luck.

 

[spotty161]

hey, thank you so much for your help, I managed to figure everything out.

much appreciated!!