filter data based on multiple price for each sheet based on two columns

[Ali M]

hi experts

I hope finding solution here . despite of my request is not easy .
so I have data in sheet MAIN. what I want filter data based on column B ,G for each sheet with just merge and sum duplicate item based on column B in column F and column H= column FxG
it shouldn't merge column H,G . I put the formula in column H how calculate, but I don't want show the formula in column H .
so in sheet BIG should merge duplicate items based on column B and bring big price based on column G as I highlighted by red color
also in sheet SMALL should merge duplicate items based on column B and bring small price based on column G as I highlighted by red color
the same thing in sheet AVERAGE should merge duplicate items based on column B and average price based on column G as I highlighted by red color

NOTE: my data are about 1000 rows in sheet main and it's increasable

Pri.xlsm
	A	B	C	D	E	F	G	H
1	DATE	SSTR	TTR	MMR	NNR	QTY	PRICE	TOTAL
2	07/08/2021	SLFR-100	FOOD-1	PR100	NRIT1	10.00	25.00	250.00
3	08/08/2021	SLFR-101	FOOD-2	PR101	NRIT2	20.00	30.00	600.00
4	09/08/2021	SLFR-102	FOOD-3	PR102	NRIT3	23.00	35.00	805.00
5	10/08/2021	SLFR-103	FOOD-4	PR103	NRIT4	25.00	30.00	750.00
6	11/08/2021	SLFR-104	FOOD-5	PR104	NRIT5	20.00	40.00	800.00
7	12/08/2021	SLFR-105	FOOD-6	PR105	NRIT6	10.00	45.00	450.00
8	13/08/2021	SLFR-106	FOOD-7	PR106	NRIT7	20.00	45.00	900.00
9	14/08/2021	SLFR-107	FOOD-8	PR107	NRIT8	15.00	55.00	825.00
10	15/08/2021	SLFR-108	FOOD-9	PR108	NRIT9	5.00	60.00	300.00
11	16/08/2021	SLFR-109	FOOD-10	PR109	NRIT10	10.00	70.00	700.00
12	17/08/2021	SLFR-110	FOOD-11	PR110	NRIT11	20.00	80.00	1,600.00
13	18/08/2021	SLFR-111	FOOD-12	PR111	NRIT12	25.00	85.00	2,125.00
14	19/08/2021	SLFR-112	FOOD-13	PR112	NRIT13	20.00	90.00	1,800.00
15	20/08/2021	SLFR-113	FOOD-14	PR113	NRIT14	20.00	25.00	500.00
16	21/08/2021	SLFR-114	FOOD-15	PR114	NRIT15	10.00	30.00	300.00
17	22/08/2021	SLFR-115	FOOD-16	PR115	NRIT16	10.00	45.00	450.00
18	23/08/2021	SLFR-116	FOOD-17	PR116	NRIT17	10.00	40.00	400.00
19	24/08/2021	SLFR-117	FOOD-18	PR117	NRIT18	15.00	50.00	750.00
20	25/08/2021	SLFR-118	FOOD-19	PR118	NRIT19	15.00	60.00	900.00
21	26/08/2021	SLFR-119	FOOD-20	PR119	NRIT20	5.00	45.00	225.00
22	27/08/2021	SLFR-115	FOOD-16	PR116	NRIT17	5.00	20.00	100.00
23	28/08/2021	SLFR-116	FOOD-17	PR117	NRIT18	10.00	35.00	350.00
24	29/08/2021	SLFR-117	FOOD-18	PR118	NRIT19	20.00	50.00	1,000.00
25	30/08/2021	SLFR-118	FOOD-19	PR119	NRIT20	10.00	45.00	450.00
26	31/08/2021	SLFR-119	FOOD-20	PR120	NRIT21	20.00	25.00	500.00
27	01/09/2021	SLFR-120	FOOD-21	PR121	NRIT22	10.00	20.00	200.00
28	02/09/2021	SLFR-121	FOOD-22	PR122	NRIT23	5.00	20.00	100.00
29	03/09/2021	SLFR-122	FOOD-23	PR123	NRIT24	20.00	25.00	500.00
MAIN

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

Pri.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	SSTR	TTR	MMR	NNR	QTY	PRICE	TOTAL
2	1	SLFR-100	FOOD-1	PR100	NRIT1	10.00	25.00	250.00
3	2	SLFR-101	FOOD-2	PR101	NRIT2	20.00	30.00	600.00
4	3	SLFR-102	FOOD-3	PR102	NRIT3	23.00	35.00	805.00
5	4	SLFR-103	FOOD-4	PR103	NRIT4	25.00	30.00	750.00
6	5	SLFR-104	FOOD-5	PR104	NRIT5	20.00	40.00	800.00
7	6	SLFR-105	FOOD-6	PR105	NRIT6	10.00	45.00	450.00
8	7	SLFR-106	FOOD-7	PR106	NRIT7	20.00	45.00	900.00
9	8	SLFR-107	FOOD-8	PR107	NRIT8	15.00	55.00	825.00
10	9	SLFR-108	FOOD-9	PR108	NRIT9	5.00	60.00	300.00
11	10	SLFR-109	FOOD-10	PR109	NRIT10	10.00	70.00	700.00
12	11	SLFR-110	FOOD-11	PR110	NRIT11	20.00	80.00	1,600.00
13	12	SLFR-111	FOOD-12	PR111	NRIT12	25.00	85.00	2,125.00
14	13	SLFR-112	FOOD-13	PR112	NRIT13	20.00	90.00	1,800.00
15	14	SLFR-113	FOOD-14	PR113	NRIT14	20.00	25.00	500.00
16	15	SLFR-114	FOOD-15	PR114	NRIT15	10.00	30.00	300.00
17	16	SLFR-115	FOOD-16	PR115	NRIT16	15.00	45.00	675.00
18	17	SLFR-116	FOOD-17	PR116	NRIT17	20.00	40.00	800.00
19	18	SLFR-117	FOOD-18	PR117	NRIT18	35.00	50.00	1,750.00
20	19	SLFR-118	FOOD-19	PR118	NRIT19	25.00	60.00	1,500.00
21	20	SLFR-119	FOOD-20	PR119	NRIT20	25.00	45.00	1,125.00
22	21	SLFR-120	FOOD-21	PR121	NRIT22	10.00	20.00	200.00
23	22	SLFR-121	FOOD-22	PR122	NRIT23	5.00	20.00	100.00
24	23	SLFR-122	FOOD-23	PR123	NRIT24	20.00	25.00	500.00
BIG

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

Pri.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	SSTR	TTR	MMR	NNR	QTY	PRICE	TOTAL
2	1	SLFR-100	FOOD-1	PR100	NRIT1	10.00	25.00	250.00
3	2	SLFR-101	FOOD-2	PR101	NRIT2	20.00	30.00	600.00
4	3	SLFR-102	FOOD-3	PR102	NRIT3	23.00	35.00	805.00
5	4	SLFR-103	FOOD-4	PR103	NRIT4	25.00	30.00	750.00
6	5	SLFR-104	FOOD-5	PR104	NRIT5	20.00	40.00	800.00
7	6	SLFR-105	FOOD-6	PR105	NRIT6	10.00	45.00	450.00
8	7	SLFR-106	FOOD-7	PR106	NRIT7	20.00	45.00	900.00
9	8	SLFR-107	FOOD-8	PR107	NRIT8	15.00	55.00	825.00
10	9	SLFR-108	FOOD-9	PR108	NRIT9	5.00	60.00	300.00
11	10	SLFR-109	FOOD-10	PR109	NRIT10	10.00	70.00	700.00
12	11	SLFR-110	FOOD-11	PR110	NRIT11	20.00	80.00	1,600.00
13	12	SLFR-111	FOOD-12	PR111	NRIT12	25.00	85.00	2,125.00
14	13	SLFR-112	FOOD-13	PR112	NRIT13	20.00	90.00	1,800.00
15	14	SLFR-113	FOOD-14	PR113	NRIT14	20.00	25.00	500.00
16	15	SLFR-114	FOOD-15	PR114	NRIT15	10.00	30.00	300.00
17	16	SLFR-115	FOOD-16	PR115	NRIT16	15.00	20.00	300.00
18	17	SLFR-116	FOOD-17	PR116	NRIT17	20.00	35.00	700.00
19	18	SLFR-117	FOOD-18	PR117	NRIT18	35.00	50.00	1,750.00
20	19	SLFR-118	FOOD-19	PR118	NRIT19	25.00	45.00	1,125.00
21	20	SLFR-119	FOOD-20	PR119	NRIT20	25.00	25.00	625.00
22	21	SLFR-120	FOOD-21	PR121	NRIT22	10.00	20.00	200.00
23	22	SLFR-121	FOOD-22	PR122	NRIT23	5.00	20.00	100.00
24	23	SLFR-122	FOOD-23	PR123	NRIT24	20.00	25.00	500.00
SMALL

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

Pri.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	SSTR	TTR	MMR	NNR	QTY	PRICE	TOTAL
2	1	SLFR-100	FOOD-1	PR100	NRIT1	10.00	25.00	250.00
3	2	SLFR-101	FOOD-2	PR101	NRIT2	20.00	30.00	600.00
4	3	SLFR-102	FOOD-3	PR102	NRIT3	23.00	35.00	805.00
5	4	SLFR-103	FOOD-4	PR103	NRIT4	25.00	30.00	750.00
6	5	SLFR-104	FOOD-5	PR104	NRIT5	20.00	40.00	800.00
7	6	SLFR-105	FOOD-6	PR105	NRIT6	10.00	45.00	450.00
8	7	SLFR-106	FOOD-7	PR106	NRIT7	20.00	45.00	900.00
9	8	SLFR-107	FOOD-8	PR107	NRIT8	15.00	55.00	825.00
10	9	SLFR-108	FOOD-9	PR108	NRIT9	5.00	60.00	300.00
11	10	SLFR-109	FOOD-10	PR109	NRIT10	10.00	70.00	700.00
12	11	SLFR-110	FOOD-11	PR110	NRIT11	20.00	80.00	1,600.00
13	12	SLFR-111	FOOD-12	PR111	NRIT12	25.00	85.00	2,125.00
14	13	SLFR-112	FOOD-13	PR112	NRIT13	20.00	90.00	1,800.00
15	14	SLFR-113	FOOD-14	PR113	NRIT14	20.00	25.00	500.00
16	15	SLFR-114	FOOD-15	PR114	NRIT15	10.00	30.00	300.00
17	16	SLFR-115	FOOD-16	PR115	NRIT16	15.00	32.50	487.50
18	17	SLFR-116	FOOD-17	PR116	NRIT17	20.00	37.50	750.00
19	18	SLFR-117	FOOD-18	PR117	NRIT18	35.00	50.00	1,750.00
20	19	SLFR-118	FOOD-19	PR118	NRIT19	25.00	52.50	1,312.50
21	20	SLFR-119	FOOD-20	PR119	NRIT20	25.00	35.00	875.00
22	21	SLFR-120	FOOD-21	PR121	NRIT22	10.00	20.00	200.00
23	22	SLFR-121	FOOD-22	PR122	NRIT23	5.00	20.00	100.00
24	23	SLFR-122	FOOD-23	PR123	NRIT24	20.00	25.00	500.00
AVERAGE

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

 

[jasonb75]

Do you really need it to be spread over multiple sheets with the duplicates removed? It can be done, but simply adding extra columns to the main sheet as below will be a much easier and more efficient way of getting the results.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	DATE	SSTR	TTR	MMR	NNR	QTY	PRICE	TOTAL	Big	Small	Average
2	07/08/2021	SLFR-100	FOOD-1	PR100	NRIT1	10.00	25.00	250.00	25.00	25.00	25.00
3	08/08/2021	SLFR-101	FOOD-2	PR101	NRIT2	20.00	30.00	600.00	30.00	30.00	30.00
4	09/08/2021	SLFR-102	FOOD-3	PR102	NRIT3	23.00	35.00	805.00	35.00	35.00	35.00
5	10/08/2021	SLFR-103	FOOD-4	PR103	NRIT4	25.00	30.00	750.00	30.00	30.00	30.00
6	11/08/2021	SLFR-104	FOOD-5	PR104	NRIT5	20.00	40.00	800.00	40.00	40.00	40.00
7	12/08/2021	SLFR-105	FOOD-6	PR105	NRIT6	10.00	45.00	450.00	45.00	45.00	45.00
8	13/08/2021	SLFR-106	FOOD-7	PR106	NRIT7	20.00	45.00	900.00	45.00	45.00	45.00
9	14/08/2021	SLFR-107	FOOD-8	PR107	NRIT8	15.00	55.00	825.00	55.00	55.00	55.00
10	15/08/2021	SLFR-108	FOOD-9	PR108	NRIT9	5.00	60.00	300.00	60.00	60.00	60.00
11	16/08/2021	SLFR-109	FOOD-10	PR109	NRIT10	10.00	70.00	700.00	70.00	70.00	70.00
12	17/08/2021	SLFR-110	FOOD-11	PR110	NRIT11	20.00	80.00	1600.00	80.00	80.00	80.00
13	18/08/2021	SLFR-111	FOOD-12	PR111	NRIT12	25.00	85.00	2125.00	85.00	85.00	85.00
14	19/08/2021	SLFR-112	FOOD-13	PR112	NRIT13	20.00	90.00	1800.00	90.00	90.00	90.00
15	20/08/2021	SLFR-113	FOOD-14	PR113	NRIT14	20.00	25.00	500.00	25.00	25.00	25.00
16	21/08/2021	SLFR-114	FOOD-15	PR114	NRIT15	10.00	30.00	300.00	30.00	30.00	30.00
17	22/08/2021	SLFR-115	FOOD-16	PR115	NRIT16	10.00	45.00	450.00	45.00	20.00	32.50
18	23/08/2021	SLFR-116	FOOD-17	PR116	NRIT17	10.00	40.00	400.00	40.00	35.00	37.50
19	24/08/2021	SLFR-117	FOOD-18	PR117	NRIT18	15.00	50.00	750.00	50.00	50.00	50.00
20	25/08/2021	SLFR-118	FOOD-19	PR118	NRIT19	15.00	60.00	900.00	60.00	45.00	52.50
21	26/08/2021	SLFR-119	FOOD-20	PR119	NRIT20	5.00	45.00	225.00	45.00	25.00	35.00
22	27/08/2021	SLFR-115	FOOD-16	PR116	NRIT17	5.00	20.00	100.00	45.00	20.00	32.50
23	28/08/2021	SLFR-116	FOOD-17	PR117	NRIT18	10.00	35.00	350.00	40.00	35.00	37.50
24	29/08/2021	SLFR-117	FOOD-18	PR118	NRIT19	20.00	50.00	1000.00	50.00	50.00	50.00
25	30/08/2021	SLFR-118	FOOD-19	PR119	NRIT20	10.00	45.00	450.00	60.00	45.00	52.50
26	31/08/2021	SLFR-119	FOOD-20	PR120	NRIT21	20.00	25.00	500.00	45.00	25.00	35.00
27	01/09/2021	SLFR-120	FOOD-21	PR121	NRIT22	10.00	20.00	200.00	20.00	20.00	20.00
28	02/09/2021	SLFR-121	FOOD-22	PR122	NRIT23	5.00	20.00	100.00	20.00	20.00	20.00
29	03/09/2021	SLFR-122	FOOD-23	PR123	NRIT24	20.00	25.00	500.00	25.00	25.00	25.00
Sheet1

Cell Formulas
Range	Formula
H2:H29	H2	=F2*G2
I2:I29	I2	=MAXIFS(G:G,B:B,B2)
J2:J29	J2	=MINIFS(G:G,B:B,B2)
K2:K29	K2	=AVERAGEIFS(G:G,B:B,B2)

 

[Ali M]

thank for your answering
as i said

each sheet with just merge and sum duplicate item based on column B

should merege

NOTE: my data are about 1000 rows in sheet main and it's increasable

then should do that by macro
thanks

 

[Ali M]

it's not possible by macro experts?

 

[mohadin]

Hi ALI M
If you still intrested
try

Sub test()
Dim a As Variant
a = Sheets("main").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(a(i, 2), a(i, 3), a(i, 4), a(i, 5), a(i, 6), a(i, 7), 0, 0, 0)
            w = .Item(a(i, 2))
            w(6) = a(i, 7): w(7) = a(i, 6): w(8) = a(i, 7)
            .Item(a(i, 2)) = w
        Else
            w = .Item(a(i, 2)): w(4) = w(4) + a(i, 6)
        If w(5) < a(i, 7) Then
            w(5) = w(5)
        Else
            If w(5) >= a(i, 7) Then
            w(6) = a(i, 7)
            End If
        w(8) = (w(5) + w(6)) / 2
        .Item(a(i, 2)) = w
        End If: End If
    Next
a = Application.Index(Evaluate("row(1:" & .Count & ")"), 0, 0)
Sheets("big").Cells(2, 2).Resize(.Count, 6) = Application.Index(.items, Evaluate("row(1:" & .Count & ")"), Array(1, 2, 3, 4, 5, 6))
Sheets("big").Cells(1, 1).Resize(.Count) = a
Sheets("big").Cells(2, 8).Resize(.Count) = "=RC[-2]*RC[-1]"
Sheets("small").Cells(2, 2).Resize(.Count, 6) = Application.Index(.items, Evaluate("row(1:" & .Count & ")"), Array(1, 2, 3, 4, 5, 7))
Sheets("small").Cells(1, 1).Resize(.Count) = a
Sheets("small").Cells(2, 8).Resize(.Count) = "=RC[-2]*RC[-1]"
Sheets("average").Cells(2, 2).Resize(.Count, 6) = Application.Index(.items, Evaluate("row(1:" & .Count & ")"), Array(1, 2, 3, 4, 5, 9))
Sheets("average").Cells(1, 1).Resize(.Count) = a
Sheets("average").Cells(2, 8).Resize(.Count) = "=RC[-2]*RC[-1]"
End With
End Sub

 

[Ali M]

If you still intrested

surly yes . no body answers me so far . but your code starts sequence from row1 and delete header ITEM in A1 and replace of it number 1 ,also when show the values in the others sheets should show number format based on sheet MAIN . how should fix for these problems?

 

[mohadin]

Ok

code starts sequence from row1 and delete header ITEM in A1 and replace of it number 1

sorry it is miss typing fixed

number format

Fixed

Sub test()
Dim a, w As Variant
Dim i As Long
Dim bsht As Worksheet: Dim ssht As Worksheet: Dim avsht As Worksheet
Set bsht = Sheets("Big"): Set ssht = Sheets("Small"): Set avsht = Sheets("Average")
a = Sheets("main").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(a(i, 2), a(i, 3), a(i, 4), a(i, 5), a(i, 6), a(i, 7), 0, 0, 0)
            w = .Item(a(i, 2))
            w(6) = a(i, 7): w(7) = a(i, 6): w(8) = a(i, 7)
            .Item(a(i, 2)) = w
        Else
            w = .Item(a(i, 2)): w(4) = w(4) + a(i, 6)
        If w(5) < a(i, 7) Then
            w(5) = w(5)
        Else
            If w(5) >= a(i, 7) Then
            w(6) = a(i, 7)
            End If
        w(8) = (w(5) + w(6)) / 2
        .Item(a(i, 2)) = w
        End If: End If
    Next
a = Application.Index(Evaluate("row(1:" & .Count & ")"), 0, 0)
bsht.Cells(2, 2).Resize(.Count, 6) = Application.Index(.items, Evaluate("row(1:" & .Count & ")"), Array(1, 2, 3, 4, 5, 6))
bsht.Cells(2, 1).Resize(.Count) = a
bsht.Cells(2, 8).Resize(.Count) = "=RC[-2]*RC[-1]"
ssht.Cells(2, 2).Resize(.Count, 6) = Application.Index(.items, Evaluate("row(1:" & .Count & ")"), Array(1, 2, 3, 4, 5, 7))
ssht.Cells(2, 1).Resize(.Count) = a
ssht.Cells(2, 8).Resize(.Count) = "=RC[-2]*RC[-1]"
avsht.Cells(2, 2).Resize(.Count, 6) = Application.Index(.items, Evaluate("row(1:" & .Count & ")"), Array(1, 2, 3, 4, 5, 9))
avsht.Cells(2, 1).Resize(.Count) = a
avsht.Cells(2, 8).Resize(.Count) = "=RC[-2]*RC[-1]"
bsht.Cells(2, 6).Resize(.Count, 3).NumberFormat = "0.00"
ssht.Cells(2, 6).Resize(.Count, 3).NumberFormat = "0.00"
ssht.Cells(2, 6).Resize(.Count, 3).NumberFormat = "0.00"
End With
End Sub

 

[Ali M]

you repeat the sheet twice about NumberFormat and doesn't work as What I want , But I can figure out how mod it .

bsht.Cells(2, 6).Resize(.Count, 3).NumberFormat = "#,##0.00"
ssht.Cells(2, 6).Resize(.Count, 3).NumberFormat = "#,##0.00"
avsht.Cells(2, 6).Resize(.Count, 3).NumberFormat = "#,##0.00"

thanks so much buddy !

 

[mohadin]

You are welcome Ali M
Glade I could help
Miss typing as ever
Sorry for that as well