populate average price on listbox based on selected sheet from combobox

[Hasson]

Hi
I need merging duplicates items based on column D for QTY in column E and show price average for column F and show in listbox based on selected sheet from combobox1 except STOCK sheet will show as in sheet without any price average.
each sheet should merging alone for duplicates items.
the data in sheets

list.xlsm
	A	B	C	D	E	F	G
1	DATE	NAME	INVOIC	ID	QTY	UNIT PRICE	TOTAL
2	01/01/2023	AHHGGJ-1	VVT/009	FOOILLL-100	220.00	110.00	24,200.00
3	01/01/2023	AHHGGJ-1	VVT/009	FOOILLL-101	100.00	140.00	14,000.00
4	01/01/2023	AHHGGJ-1	VVT/009	FOOILLL-102	110.00	200.00	22,000.00
5	01/01/2023	AHHGGJ-1	VVT/009	FOOILLL-103	340.00	120.00	40,800.00
6	02/01/2023	AHHGGJ-2	VVT/010	FOOILLL-101	120.00	155.00	18,600.00
7	02/01/2023	AHHGGJ-2	VVT/010	FOOILLL-100	60.00	160.00	9,600.00
8	03/01/2023	AHHGGJ-3	VVT/011	FOOILLL-103	60.00	155.00	9,300.00
9	03/01/2023	AHHGGJ-3	VVT/011	FOOILLL-101	60.00	175.00	10,500.00
10	03/01/2023	AHHGGJ-3	VVT/011	FOOILLL-100	60.00	144.00	8,640.00
11	03/01/2023	AHHGGJ-3	VVT/011	FOOILLL-104	22.00	134.00	2,948.00
12	03/01/2023	AHHGGJ-3	VVT/011	FOOILLL-105	60.00	133.00	7,980.00
BBR

Cell Formulas
Range	Formula
G2:G12	G2	=E2*F2

list.xlsm
	A	B	C	D	E	F	G
1	DATE	NAME	INVOIC	ID	QTY	UNIT PRICE	TOTAL
2	01/02/2023	QTYY223	SDFF/99	FOOILLL-100	10.00	150.00	1,500.00
3	02/02/2023	QTYY223	SDFF/99	FOOILLL-101	10.00	160.00	1,600.00
4	03/02/2023	QTYY224	SDFF/100	FOOILLL-102	20.00	220.00	4,400.00
5	04/02/2023	QTYY225	SDFF/101	FOOILLL-103	15.00	150.00	2,250.00
6	05/02/2023	QTYY226	SDFF/102	FOOILLL-101	12.00	180.00	2,160.00
7	06/02/2023	QTYY227	SDFF/103	FOOILLL-100	5.00	170.00	850.00
8	07/02/2023	QTYY228	SDFF/104	FOOILLL-103	9.00	185.00	1,665.00
9	08/02/2023	QTYY229	SDFF/105	FOOILLL-101	8.00	166.00	1,328.00
10	09/02/2023	QTYY230	SDFF/106	FOOILLL-100	4.00	177.00	708.00
11	09/02/2023	QTYY230	SDFF/106	FOOILLL-102	20.00	212.00	4,240.00
BMTR

Cell Formulas
Range	Formula
G2:G11	G2	=E2*F2

list.xlsm
	A	B	C	D	E	F	G
1	DATE	NAME	INVOIC	ID	QTY	UNIT PRICE	TOTAL
2	15/02/2023	VRRR788	VBN/900-90	FOOILLL-100	10.00	110.00	1,100.00
3	16/02/2023	VRRR789	VBN/900-91	FOOILLL-101	10.00	140.00	1,400.00
4	17/02/2023	VRRR790	VBN/900-92	FOOILLL-101	12.00	200.00	2,400.00
5	18/02/2023	VRRR791	VBN/900-93	FOOILLL-100	5.00	120.00	600.00
6	19/02/2023	VRRR792	VBN/900-94	FOOILLL-103	9.00	155.00	1,395.00
7	20/02/2023	VRRR793	VBN/900-95	FOOILLL-101	8.00	144.00	1,152.00
8	21/02/2023	VRRR794	VBN/900-96	FOOILLL-100	4.00	175.00	700.00
VSR

Cell Formulas
Range	Formula
G2:G8	G2	=E2*F2

list.xlsm
	A	B	C	D	E	F	G
1	DATE	NAME	INVOIC	ID	QTY	UNIT PRICE	TOTAL
2	28/02/2023	SDER4	FGRT500-00	FOOILLL-100	5.00	150.00	750.00
3	01/03/2023	SDER4	FGRT500-00	FOOILLL-101	5.00	160.00	800.00
4	02/03/2023	SDER4	FGRT500-00	FOOILLL-102	5.00	220.00	1,100.00
5	03/03/2023	SDER4	FGRT500-00	FOOILLL-103	10.00	150.00	1,500.00
6	04/03/2023	VRRR793	FGRT500-01	FOOILLL-103	8.00	166.00	1,328.00
7	05/03/2023	VRRR794	FGRT500-02	FOOILLL-103	4.00	177.00	708.00
8	06/03/2023	VRRR794	FGRT500-03	FOOILLL-101	4.00	190.00	760.00
STR

Cell Formulas
Range	Formula
G2:G8	G2	=E2*F2

list.xlsm
	A	B	C	D	E
1	ITEM	ID	QTY	UNIT PRICE	TOTAL
2	1	FOOILLL-100	20.00	112.00	2,240.00
3	2	FOOILLL-101	200.00	145.00	29,000.00
4	3	FOOILLL-102	100.00	190.00	19,000.00
5	4	FOOILLL-103	50.00	165.00	8,250.00
6	5	FOOILLL-104	100.00	120.00	12,000.00
7	6	FOOILLL-105	400.00	125.00	50,000.00
8	7	FOOILLL-106	430.00	133.00	57,190.00
9	8	FOOILLL-107	213.00	150.00	31,950.00
10	9	FOOILLL-108	123.00	180.00	22,140.00
11	10	FOOILLL-109	123.00	170.00	20,910.00
12	11	FOOILLL-110	124.00	155.00	19,220.00
13	12	FOOILLL-111	56.00	128.00	7,168.00
14	13	FOOILLL-112	17.00	198.00	3,366.00
15	14	FOOILLL-113	11.00	189.00	2,079.00
16	15	FOOILLL-114	1,234.00	199.00	245,566.00
STOCK

Cell Formulas
Range	Formula
E2:E16	E2	=C2*D2

when select sheet should merge the QTY and show average price to unit price column and QTY* UNIT PRICE in listbox for instance

and if the combobox1 is empty then
will create names headers based on sheets names and show QTY every sheet after merge and the column QTY in listbox will calculate across sheets like this STOCK+BBR-BMTR+VSR-STR , but when creae unit price should be average price based on two sheets together STOCK,BBR when merge the ID
result when combobox1 is empty

by the way I have 7500 rows for each sheet at least .
thanks

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

merging data based on sheet name using combobox from userform

Hi I need merging duplicates items based on column D for QTY in column E and show price average for column F and show in listbox based on selected sheet from combobox1 except STOCK sheet will show as in sheet without any price average with ignore the others columns from showing in listbox...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.