combining data in listbox on userform across multiple sheets and calculate values amongst them

[KalilMe]

hi experts
I have many sheets about five sheets contains data are almost 3000 rows for each sheet and it will increase continuiosly . so what I want when run the userform should merge the duplicate items based on COL B across the sheets each sheet repeat the items except the first sheet because this data collected from prevouis year. the others sheets are currnt year operations with considering the second sheet somtimes contains new item then should show in listbox . after merge duplicate items should show the QTY for each sheet . about COL 11 the calculate like this as item (FR1)=200+200-5+4-20=379 . as to COLS 12,13 (UNIT COST,UNIT SALES ) should not summing . should keep as it is .
as to COL 14 should calculate like this (15-12)*379=1137
see the row1 in listbox
so if any body have suggestion to do that by using helper sheet or doing directly without using helper sheet I accept all the suggestion .

COLLECTION (2).xlsm
	A	B	C	D	E	F	G	H
1	ITEM	ID	BR	TY	OR	QTY	UNIT COST	UNIT SALE
2	1	FR-1	FR	BANANA	TT	200.00	$12.00	$15.00
3	2	FR-2	FR	APPLE	LL	100.00	$11.00	$17.00
4	3	FR-3	FR	PEAR	NN	60.00	$12.00	$15.00
5	4	FR-4	FR	BANANA	QQ	55.00	$13.00	$17.00
6	5	VEG1	VEG	TOMATO	SS	50.00	$14.00	$16.00
7	6	VEG2	VEG	TOMATO	AA	50.00	$11.00	$15.00
8	6	FR-5	FR1	PEAR	MM	0.00	$11.00	$15.00
STA

COLLECTION (2).xlsm
	A	B	C	D	E	F	G	H
1	DATE	ID	BR	TY	OR	QTY	UNIT COST	TOTAL
2	1/1/2021	FR-1	FR	BANANA	TT	100.00	$12.00	$1,200.00
3	1/2/2021	FR-2	FR	APPLE	LL	50.00	$11.00	$550.00
4	1/3/2021	FR-3	FR	PEAR	NN	60.00	$12.00	$720.00
5	1/4/2021	FR-4	FR	BANANA	QQ	60.00	$13.00	$780.00
6	1/5/2021	VEG1	VEG	TOMATO	SS	65.00	$14.00	$910.00
7	1/6/2021	VEG2	VEG	TOMATO	AA	40.00	$11.00	$440.00
8	1/7/2021	FR-1	FR	BANANA	TT	100.00	$12.00	$1,200.00
9	1/8/2021	FR-5	FR1	PEAR	MM	55.00	$14.00	$770.00
RPA

Cell Formulas
Range	Formula
H2:H9	H2	=G2*F2

COLLECTION (2).xlsm
	A	B	C	D	E	F	G	H
1	DATE	ID	BR	TY	OR	QTY	UNIT SALE	TOTAL
2	2/1/2021	FR-1	FR	BANANA	TT	5.00	$15.00	$75.00
3	2/3/2021	FR-3	FR	PEAR	NN	5.00	$15.00	$75.00
4	2/4/2021	FR-4	FR	BANANA	QQ	2.00	$17.00	$34.00
5	2/5/2021	VEG1	VEG	TOMATO	SS	3.00	$16.00	$48.00
6	2/6/2021	VEG2	VEG	TOMATO	AA	4.00	$15.00	$60.00
7	2/8/2021	FR-5	FR1	PEAR	MM	2.00	$15.00	$30.00
8	2/8/2021	FR-5	FR1	PEAR	MM	2.00	$20.00	$40.00
SR

Cell Formulas
Range	Formula
H2:H8	H2	=G2*F2

COLLECTION (2).xlsm
	A	B	C	D	E	F	G	H
1	DATE	ID	BR	TY	OR	QTY	PRICE	TOTAL
2	3/1/2021	FR-1	FR	BANANA	TT	2.00	$15.00	$30.00
3	3/2/2021	FR-3	FR	PEAR	NN	2.00	$15.00	$30.00
4	3/3/2021	FR-1	FR	BANANA	TT	2.00	$15.00	$30.00
5	3/4/2021	FR-3	FR	PEAR	NN	2.00	$15.00	$30.00
RR

Cell Formulas
Range	Formula
H2:H5	H2	=G2*F2

COLLECTION (2).xlsm
	A	B	C	D	E	F	G	H
1	DATE	ID	BR	TY	OR	QTY	PRICE	TOTAL
2	1/1/2021	FR-1	FR	BANANA	TT	5.00	$12.00	$60.00
3	1/2/2021	FR-2	FR	APPLE	LL	10.00	$11.00	$110.00
4	1/3/2021	FR-1	FR	BANANA	TT	15.00	$12.00	$180.00
5	1/4/2021	FR-2	FR	APPLE	LL	20.00	$11.00	$220.00
SS

Cell Formulas
Range	Formula
H2:H5	H2	=G2*F2

the result should be in listbox

thanks in advance

 

[DanteAmor]

You could help with the following information.

I have many sheets about five sheets contains data

So there are more sheets in the book, but shouldn't they be considered to fill the listbox?

five sheets contains data

Currently 5 sheets should be considered to fill the listbox, but in the future, will there be more sheets?

the others sheets are currnt year operations with considering the second sheet somtimes contains new item then should show in listbox .

So in sheet2 "RPA" are there IDs that are not in sheet1 "STA"?

(FR1)=200+200-5+4-20=379

You can explain what the pattern is for knowing when a sheet is positive and when it is negative.

 

[KalilMe]

So there are more sheets in the book, but shouldn't they be considered to fill the listbox?

yes

Currently 5 sheets should be considered to fill the listbox, but in the future, will there be more sheets?

yes

So in sheet2 "RPA" are there IDs that are not in sheet1 "STA"?

yes

You can explain what the pattern is for knowing when a sheet is positive and when it is negative.

as you higlight the numbers if they are bigger more than others sheets.
but this case is rare . but as you mentioned so I accept this condition in considering

 

[DanteAmor]

as you higlight the numbers if they are bigger more than others sheets.
but this case is rare . but as you mentioned so I accept this condition in considering

I did not understand.
You must explain when to add and when to subtract.

 

[KalilMe]

as the listbox first sheet +second sheet- three sheet + four sheet - five sheet
First, second, four are positive and three ,five are negative. calculation is only five sheets . Forget others sheets in future.

 

[DanteAmor]

as the listbox first sheet +second sheet- three sheet + four sheet - five sheet
First, second, four are positive and three ,five are negative.

Currently 5 sheets should be considered to fill the listbox, but in the future, will there be more sheets?

yes

And in the future. Will even sheets be positive and odd sheets will be negative?

The questions go in the direction of the growth of your database. If you mentioned that the number of sheets is going to grow, you will surely be here tomorrow asking for an update to the macro.

 

[DanteAmor]

So in sheet2 "RPA" are there IDs that are not in sheet1 "STA"?

yes

In this case, the information is incomplete, in sheet2 "RPA" the UNIT SALE data would be missing

 

[KalilMe]

not always what are existed in first three sheets should be in the others sheets about the ID
about your question .
yes.
So the sheet PRA is relating for purchase that's why no unit sale
first sheet contains two prices this inventory from previous year
Second is purchase for current year contain cost price

Third is sale so just contain sale price
Four depends on first, third
Five depends on first and second

 

[DanteAmor]

So the sheet PRA is relating for purchase that's why no unit sale
first sheet contains two prices this inventory from previous year
Second is purchase for current year contain cost price

Third is sale so just contain sale price
Four depends on first, third
Five depends on first and second

I don't understand your explanations.

But, as is your example, the following macro works.
Test the macro with your sample examples.

Private Sub UserForm_Activate()
  Dim sh1 As Worksheet, sh As Worksheet
  Dim a As Variant, b() As Variant, c As Variant, d As Variant
  Dim dic As Object
  Dim arSh As Variant
  Dim i As Long, j As Long, k As Long, m As Long, n As Long
  Dim p As Long, q As Long, u As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  'first sheet
  a = Sheets("STA").Range("A2", Sheets("STA").Range("H" & Rows.Count).End(3)).Value
  'second sheet
  d = Sheets("RPA").Range("A2", Sheets("RPA").Range("H" & Rows.Count).End(3)).Value
  'Sheet names, from 2 to last
  arSh = Array("RPA", "SR", "RR", "SS")
  '
  u = UBound(arSh) + 2
  ReDim c(1 To UBound(a, 1) + UBound(d, 1), 1 To 9 + u)
  ListBox1.ColumnCount = 9 + u
  m = 6   'Initial column inside the listbox for the sheets
  '
  'For the first sheet
  For i = 1 To UBound(a)
    dic(a(i, 2)) = i
    For j = 1 To 6 'UBound(a, 2)
      c(i, j) = a(i, j)
    Next
    c(i, m + u) = a(i, 6)
    c(i, m + u + 1) = a(i, 7)
    c(i, m + u + 2) = a(i, 8)
    c(i, m + u + 3) = (c(i, m + u + 2) - c(i, m + u + 1)) * c(i, m + u)
  Next i
  '
  'For the second sheet
  p = dic.Count   'Number of indices
  For i = 1 To UBound(d)
    If Not dic.exists(d(i, 2)) Then
      p = p + 1
      dic(d(i, 2)) = p
      For j = 1 To 5
        c(p, j) = d(i, j)
      Next j
      c(p, m + u + 1) = d(i, 7)
      c(p, m + u + 2) = d(i, 7)
    End If
  Next i
  '
  n = 7   'To increase the column for each sheet
  q = 1   'If it's odd or even
  For Each sh In Sheets
    If Not IsError(Application.Match(sh.Name, arSh, 0)) Then
      q = q + 1
      Erase b()
      b = sh.Range("A2", sh.Range("H" & Rows.Count).End(3)).Value
      For i = 1 To UBound(b)
        If dic.exists(b(i, 2)) Then
          k = dic(b(i, 2))
          c(k, n) = c(k, n) + b(i, 6)
          If q Mod (2) = 0 Then
            c(k, m + u) = c(k, m + u) + b(i, 6)
          Else
            c(k, m + u) = c(k, m + u) - b(i, 6)
          End If
          c(k, m + u + 3) = (c(k, m + u + 2) - c(k, m + u + 1)) * c(k, m + u)
        End If
      Next
      n = n + 1
    End If
  Next
  ListBox1.List = c
End Sub

 

[KalilMe]

wow! very impressive but I have somthings need fixing
first I would show the currency and formatting numbers as what are existed in the sheets also show hyphen"-" for empty values as in OP
second this is my big mistake some times the prices changes .so I would take prices average just in sheet (STA,RPA,SA)
when take price average in COL12 in listbox should calculate the cost price average based on first sheet ,second(COL G)
COL13 in listbox should calculate the sale price average based on first sheet ,third (COLS G,H)
finally I know this doesn't mentioned in OP if you did it I truly appreciate. I add combobox1 contains sheets names . then should also apply your code when select specific sheet as code does when run userform
thanks again