macro merge data based on column and sum values based on two columns and insert column balance

Ali M

Active Member
Joined
Oct 10, 2021
Messages
287
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
hi
I have repeated data for each name . so what I want merge duplicate names based on column B and split column C into two columns and if threre repeate item also should merge with comma and summing values in columns D,E and insert column BALANCE to subtract column D from E
first sheet
deb.xlsm
ABCDE
1DATENAMECONDITIONDEBITCREDIT
220/09/2021ALI1INVOICE110,000.000
321/09/2021ALI1INVOICE25,000.000
422/09/2021ALI3INVOICE320,000.000
523/09/2021ALI4INVOICE415,000.000
624/09/2021ALI5INVOICE525,000.000
725/09/2021ALI6INVOICE630,000.000
826/09/2021ALI7INVOICE71,500.000
927/09/2021ALI7INVOICE810,000.000
1028/09/2021ALI9INVOICE92,000.000
1129/09/2021ALI10INVOICE103,500.000
1230/09/2021ALI11INVOICE111,500.000
1301/10/2021ALI12INVOICE1210,000.000
1402/10/2021ALI13INVOICE1320,000.000
1503/10/2021ALI14INVOICE1430,000.000
1604/10/2021ALI11INVOICE1540,000.000
1705/10/2021ALI15INVOICE163,000.000
1806/10/2021ALI14VOUCHER12,000.000
1907/10/2021ALI15VOUCHER21,500.000
2008/10/2021ALI16INVOICE1730,000.000
2109/10/2021ALI16VOUCHER52,000.000
2210/10/2021ALI16VOUCHER61,000.00
FIRST



the result should in second sheet . it should create the whole data with headers
deb.xlsm
ABCDEFG
1ITEMNAMEINVOICE NOVOUCHER NODEBITCREDITBALANCE
21ALI1INVOICE1,2-15,000.000-15,000.000
32ALI3INVOICE3-20,000.000-20,000.000
43ALI4INVOICE4-15,000.000-15,000.000
54ALI5INVOICE5-25,000.000-25,000.000
65ALI6INVOICE6-30,000.000-30,000.000
76ALI7INVOICE7-1,500.000-1,500.000
87ALI7INVOICE8-10,000.000-10,000.000
98ALI9INVOICE9-2,000.000-2,000.000
109ALI10INVOICE10-3,500.000-3,500.000
1110ALI11INVOICE11,15-41,500.000-41,500.000
1211ALI12INVOICE12-10,000.000-10,000.000
1312ALI13INVOICE13-20,000.000-20,000.000
1413ALI14INVOICE14-30,000.0002,000.00028,000.000
1514ALI15INVOICE16VOUCHER23,000.0001,500.0001,500.000
1615ALI16INVOICE17VOUCHER5,630,000.0003,000.00027,000.000
SECOND
Cell Formulas
RangeFormula
G2:G16G2=E2-F2
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Any way
Try
VBA Code:
Sub test()
a = Sheets("sheet1").Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
  For i = 2 To UBound(a)
    If Not .Exists(a(i, 2)) Then
   .Add a(i, 2), Array(.Count + 1, a(i, 2), a(i, 3), 0, a(i, 4), 0, 0)
    w = .Item(a(i, 2))
   w(6) = w(4) - w(5)
  .Item(a(i, 2)) = w
  Else
  w = .Item(a(i, 2))
  w(5) = IIf(a(i, 5) <> 0, w(5) + a(i, 5), w(5))
  If a(i, 3) Like ("INVOICE*") Then
    w(2) = w(2) & "," & Right(a(i, 3), Len(a(i, 3)) - 7)
    w(4) = w(4) + a(i, 4)
    w(6) = w(4) - w(5)
   .Item(a(i, 2)) = w
  Else
   w(3) = IIf(w(3) = 0, a(i, 3), w(3) & "," & Right(a(i, 3), Len(a(i, 3)) - 7))
   w(5) = w(5) + a(i, 4)
   w(6) = w(4) - w(5)
  .Item(a(i, 2)) = w
 End If
End If
Next
i = .Count: itm = .items
End With
With Sheets("sheet2").Range("a2").Resize(i, 7)
.Offset(, 3).NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);_( ""-""_);_(@_)"
.Offset(-1).Resize(1) = Array("ITEM", "NAME", "INVOICE NO", "VOUCHER NO", "DEBIT", "CREDIT", "BALANCE")
.Resize(, 6).Value = Application.Transpose(Application.Transpose(itm))
.Offset(, 6).Resize(i, 1).FormulaR1C1 = "=RC[-2]-RC[-1]"
      With .Offset(-1).Resize(i + 1)
       .Resize(1).Interior.Color = vbGreen '5287936
       .Resize(1).Font.Bold = True
       .HorizontalAlignment = xlCenter
       .Borders(xlEdgeLeft).LineStyle = xlContinuous
       .Borders(xlEdgeTop).LineStyle = xlContinuous
       .Borders.Weight = xlThin
       .EntireColumn.AutoFit
       End With: End With
Sheets("sheet2").Activate
End Sub
 
Upvote 0
Solution
You are welcome
And thank you for the feedback
Be happy and safe
 
Upvote 0
where is the column BALANCE?

Simply add a measure to subtract one column from the other. Missed it in the requirement, but it is a case of simple math.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top