collection showed data in listbox with sheet data and show in another listbox

[Maklil]

Hello ,
I have code to show data in listbox1 like this

I have data in sheet

mk (1).xlsm
	A	B	C
1	ITEM	SAFES	BALANCES
2	1	ASDFT SAFE	300,000.00
3	2	YOUSEF SAFE	150,000.00
4	3	AFORI BANK	250,000.00
5	4	AMTOR BANK	15,000.00
6	5	ASTORI BANK	100,000.00
7	6	AMM SAFE	280,000.00
8	7	HBS BANK	100,000.00
9	TOTAL		1,195,000.00
BAB

Cell Formulas
Range	Formula
C9	C9	=SUM(C2:C8)

and I add listbox2 , so what I want merge data amounts in PAID,RECEIVED columns based on items are existed in SAFES column for listbox1 ,as to amounts in column C for BAB sheet should sum with amounts for RECEIVED column and show result in listbox2 like this picture,

also should add BALANCE column to subtract RECEIVED column from PAID column and add TOTAL row to sum RECEIVED,PAID columns and in BALANCE column subtract RECEIVED column from ,PAID column.
there are items are existed in BAB sheet and they are not existed in listbox1 so should show in listbox2 based on BAB sheet.
when show result in listbox2 I expect the showed data in listbox1 could be 15000 rows.
thanks

 

[DanteAmor]

The data in the BAB sheet is not ordered by the SAFES column, however, in the listbox it does appear ordered by SAFES. So to achieve that, I need to copy the data from columns A:C and paste into columns AA:AC and sort the data by SAFES. Ultimately the data in columns AA:AC will be deleted.

Use the following code to load listbox2:

Private Sub CommandButton2_Click()
  Dim a As Variant, b As Variant, c As Variant
  Dim dic As Object
  Dim i As Long, lr As Long, nRow As Long
  Dim dRec As Double, dPai As Double
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  Application.ScreenUpdating = False
  With Sheets("BAB")
    lr = Sheets("BAB").Range("A" & Rows.Count).End(3).Row - 1
    .Range("A1:C" & lr).Copy .Range("AA1")
    .Range("AB1:AC" & lr).Sort .Range("AB1"), xlAscending, Header:=xlYes
    a = .Range("AA1:AE" & lr + 1).Value
    .Range("AA:AC").Clear
  End With
  Application.ScreenUpdating = True
  
  For i = 2 To UBound(a)
    dic(a(i, 2)) = i
  Next
  
  c = a
  b = ListBox1.List
  For i = 1 To UBound(b)
    If dic.exists(b(i, 2)) Then
      nRow = dic(b(i, 2))
      c(nRow, 3) = c(nRow, 3) + CDbl(Replace(b(i, 5), "-", 0))
      c(nRow, 4) = c(nRow, 4) + CDbl(Replace(b(i, 3), "-", 0))
      c(nRow, 5) = c(nRow, 3) - c(nRow, 4)
    End If
  Next
  
  For i = 2 To UBound(c)
    dRec = dRec + c(i, 3)
    dPai = dPai + c(i, 4)
    c(i, 3) = Format(Val(c(i, 3)), "#,##0.00;;-")
    c(i, 4) = Format(Val(c(i, 4)), "#,##0.00;;-")
    c(i, 5) = Format(Val(c(i, 5)), "#,##0.00;;-")
  Next
  c(1, 1) = "ITEM"
  c(1, 2) = "SAFES"
  c(1, 3) = "RECEIVED"
  c(1, 4) = "PAID"
  c(1, 5) = "BALANCE"
  c(UBound(c), 1) = "TOTAL"
  c(UBound(c), 3) = Format(dRec, "#,##0.00;;-")
  c(UBound(c), 4) = Format(dPai, "#,##0.00;;-")
  c(UBound(c), 5) = Format(dRec - dPai, "#,##0.00;;-")
  
  With ListBox2
    .ColumnCount = 5
    .List = c
  End With
End Sub

 

[Maklil]

thank you so much .