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

 

[KalilMe]

sorry I delaed you but it makes me big confusing . som items don't calculate averege correctly . what happens ? see the highlighted unit cost by blue color for instance
FR2 should be (9+8+11)/3=9.333 but in listbox show 11
also FR5 should be (11+14)/2=12.5 but in listbox show 11

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	$9.00	$450.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	$14.00	$1,400.00
9	1/8/2021	FR-5	FR1	PEAR	MM	55.00	$14.00	$770.00
10	1/9/2021	FR-6	FR1	PEAR	MM	56.00	$15.00	$840.00
11	1/10/2021	FR-6	FR1	PEAR	MM	57.00	$16.00	$912.00
12	1/11/2021	FR-2	FR	APPLE	LL	50.00	$8.00	$400.00
RPA

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

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	$13.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	7	FR-5	FR1	PEAR	MM	0.00	$11.00	$15.00
9	8	FR-6	FR1	PEAR	MM	0.00	$12.00	$15.00
STA

 

[DanteAmor]

Do you have other sheets with other costs?

 

[KalilMe]

yes in sheet SS is unit cost and RR unit sales , but should ignore them . the price average depends on sheets(STA,RPA,SR)

 

[DanteAmor]

In sheet "RPA" the title "UNIT COST " has a blank space on the right.
Then...
Titles must be exactly the same.

 

[KalilMe]

awful! I don't believe this simple thing causes this problem.
actually I have a new requirements to complete this project . it's not fair to ask new requirements . as I see from the big code must take from you much more time to design it , and every time mod this code . so I will issue a new thread is linked with this thread contain the whole details . I hope from you complete your favor to achieve this project and see my new thread when you have time . this is your code and you know how it works and how should adjusting so if you see new thread when you have free time so I truly appreciate your help. I will issue in today or tommorrow .so it's up to you .
finally you achieved a great work today . thanks so much for your solution .

 

[DanteAmor]

It is the problem of all systems, the quality of the information.

I'm glad to help you. Thanks for the feedback.