Incrementing cell address inside Filter function

[KarthickDijo]

Hi All,

Could anyone help me out in achieving the same output as showed in the output. Or help me out in incrementing the cell address as below.

=FILTER(Sheet1!C:C,((Sheet1!G:G=(MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K3)))*(Sheet1!J:J=Sheet2!K3))) ---> This will give a result of two
=FILTER(Sheet1!C:C,((Sheet1!G:G=(MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K4)))*(Sheet1!J:J=Sheet2!K4))) ---> This will give a result of four
=FILTER(Sheet1!C:C,((Sheet1!G:G=(MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K5)))*(Sheet1!J:J=Sheet2!K5))) ---> This will give a result of three

Likewise is there any option to auto increment the cell address.

Thanks in advance !!

Book14.xlsx
	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		ss	ddd				SKU	Option 1 Name	Option 1 Value	Value 1 Norm	USD Unit Wholesale Price	Option Image
2		Concat	rr				ModifierItemID	ModifierListName	Delete	ModifierItemName	BasePrice	PhotoName	ModifierListKey
3		01, 02	3.44				POP049-01
4		Blue, Gray, Khaki, Red	23.79				POP049-02
5		Blue, Green, Red	13.17				ASJP018-B3-S
6		Blue, Dark Gray, Gray, Pink, White	7.75				ASJP018-G3-S
7		Apricot	4.69				ASJP018-K1-S
8		01	3.73				ASJP018-R1-S
9		01, 02, 03, 04, 05, 06, 07	5.19				STC185-B3-S
10		White	3.74				STC185-G1-S
11		Blue, Green	14.29				STC185-R1-S
12		Black, Red	3.25
13		Pink	3.74
14		Black	3.13
15		Black, Green, Khaki, Red	11.88
16		Red	3.13
17		Black, Gray	8.45
18		Apricot, Black, White	12.74
19		Black, Gray, White	5.85
20		Green	3.74
21		Blue, Dark Blue, Leopard, Multicolor, Purple	9.99
22		Light Blue	12.35
23		Blue, Dark Gray	13.17
24		Leopard	3.89
25		Apricot, Beige, Black, Coffee, Green, Khaki, Light Blue, Multicolor, Orange, Wine Red	16.25
26		Army Green, Dark Gray, Khaki, Light Gray, Wine Red	24.69
27		Apricot, Blue, Gray, Green, Purple	26.39
28		Apricot, Army Green, Black, Brown, Gray, Green, Navy, Pink, White, Wine Red	14.04
29		Silver	3.25
30		Apricot, Green, Pink	21.19
31		Coffee	13.39
32		Black, Blue, Dark Gray	13.17
33		Coffee, Dark Gray, Gray	10.66
34		Blue, Dark Gray, Orange, Rose Red	12.81
35		Black, Green, Khaki, Light Blue, Navy, Pink, White, Wine Red	16.64
36		Black, Gold, Pink, Purple, Red	8.45
37		Blue, Khaki, Yellow	19.3
38		Camel	15.34
39		Brown	3.73
40		Black, Green, Yellow	15.34
41		Orange	4.69
42		Blue	3.73
43		Green, Orange, White	17.54
44		B3-EST011, B3-EST018B, G1-EST011, G1-EST018B	16.01
45		Apricot, Black, Brown, Burgundy, Lavender	28.67
46		Gray, Hallmark, Santa	16.94
47		Blue, Pink, White	7.79
48		Black, Blue, Orange	24.69
49		Black, Blue, Pink, Yellow	17.94
50		Black, Blue, Red, Rose Red, White	13.74
51		Black, Blue	13.11
52		Apricot, Black, Blue, Khaki, Orange, White	28.59
53		Dark Gray, Gray, White	11.11
54		01, 02, 03, 04, 05, 06	3.74
55		Army Green, Black, Camouflage	7.79
56		Multicolor	3.73
57		Brown, Dark Gray, Gray, Green, Wine Red	19.24
58		01, 02, 03, 04, 05, 06, 07, 08, 09, 10	3.25
59		Apricot, Black, Gray, Orange, Purple	18.72
60		Blue, Green, Wine Red	18.89
61		Black, Gray, Khaki, Pink, White	11.24
62		Black, Blue, Dark Blue, Dark Gray, Light Blue	16.25
63		Black, Brown, Green, Multicolor	14.13
64		01, 02, 03, 04	3.74
65		01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14	3.89
66		Blue, Gray, Khaki	15.06
67		Blue, Brown, Dark Brown, Dark Green, Green, Navy, Orange, Red, Sky Blue, Yellow	39.58
68		Blue, Light purple	21.24
69		Black, Brown	4.56
70		Gold, Multicolor	4.99
71		Blue, Dark Gray, Green, Red	14.99
72		Gray	9.09
73		Light Blue, Orange, Pink, Yellow	25.09
74		Navy	3.89
75		Green, Orange, Pink	17.16
76		01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12	3.25
77		Khaki	9.75
78		Blue, Wine Red	18.88
79		Black, Green, Orange, Rose Red	28.34
80		Apricot, Beige, Black, Brown, Khaki	19.49
81		Apricot, Beige, Black, Brown, Gray, Khaki, Pink	16.89
82		Gray, Pink	4.99
83		Black, Red, Yellow	3.89
84		Black, Green, Light green, Red	16.94
85		Purple	10.39
86		White, Yellow	3.89
87		Black, Blue, Green, White	11.69
88		Apricot, Black, Brown, Dark Blue, Gray	25.03
89		Black, Wine Red, Yellow	18.59
Sheet2

Cell Formulas
Range	Formula
K2:K1617	K2	=UNIQUE(Sheet1!J:J)
P3:P4	P3	=FILTER(Sheet1!C:C,((Sheet1!G:G=(MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K3)))*(Sheet1!J:J=Sheet2!K3)))
P5:P8	P5	=FILTER(Sheet1!C:C,((Sheet1!G:G=(MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K4)))*(Sheet1!J:J=Sheet2!K4)))
P9:P11	P9	=FILTER(Sheet1!C:C,((Sheet1!G:G=(MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K5)))*(Sheet1!J:J=Sheet2!K5)))
L3:L89	L3	=MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P1	Cell Value	duplicates	text	NO

 

[cmowla]

You can increment it using INDEX

Along with a helper column:

Book1.xlsb
	A	B									K
1	100										100
2	101										101
3	102										102
4	103										103
5	104										104
6	105										105
7
8		1
9		2
10		3
11		4
12		5
13		6
Sheet5

Cell Formulas
Range	Formula
A1:A6	A1	=INDEX(Sheet5!K:K,B8)
B9:B13	B9	=B8+1

Or if you set it up "correctly", using ROW():

Book1.xlsb
	A										K
1
2
3
4
5	100										100
6	101										101
7	102										102
8	103										103
9	104										104
10	105										105
Sheet5 (2)

Cell Formulas
Range	Formula
A5:A10	A5	=INDEX('Sheet5 (2)'!K:K,ROW())

 

[KarthickDijo]

You can increment it using INDEX

Along with a helper column:

Book1.xlsb
	A	B									K
1	100										100
2	101										101
3	102										102
4	103										103
5	104										104
6	105										105
7
8		1
9		2
10		3
11		4
12		5
13		6
Sheet5

Cell Formulas
Range	Formula
A1:A6	A1	=INDEX(Sheet5!K:K,B8)
B9:B13	B9	=B8+1

Or if you set it up "correctly", using ROW():

Book1.xlsb
	A										K
1
2
3
4
5	100										100
6	101										101
7	102										102
8	103										103
9	104										104
10	105										105
Sheet5 (2)

Cell Formulas
Range	Formula
A5:A10	A5	=INDEX('Sheet5 (2)'!K:K,ROW())

Actually i need to increment the K3 part (Sheet1!J:J=Sheet2!K3). Since am using array function and the result might have two/three or four results, I couldn't auto increment the K3 part.
In the sample I gave, I need the below result to happen automatically. I couldn't use the fill down option. These three were done manually.

P3:P4	P3	=FILTER(Sheet1!C:C,((Sheet1!G:G=(MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K3)))*(Sheet1!J:J=Sheet2!K3)))
P5:P8	P5	=FILTER(Sheet1!C:C,((Sheet1!G:G=(MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K4)))*(Sheet1!J:J=Sheet2!K4)))
P9:P11	P9	=FILTER(Sheet1!C:C,((Sheet1!G:G=(MINIFS(Sheet1!G:G,Sheet1!J:J,Sheet2!K5)))*(Sheet1!J:J=Sheet2!K5)))

 

[Fluff]

What version of Excel are you actually using, as 2019 does not have the Filter function?

 

[KarthickDijo]

What version of Excel are you actually using, as 2019 does not have the Filter function?

I have both 2019 & 365. Am trying this in 365. Mostly am using 2019 for personal use.

 

[Fluff]

Ok, do you have the VSTACK function in your 365 version of xl?

 

[KarthickDijo]

Ok, do you have the VSTACK function in your 365 version of xl?

Yeah I have the function.

 

[Fluff]

Ok, how about

=DROP(REDUCE("",K3:K5,LAMBDA(a,b,VSTACK(a,FILTER(Sheet1!C2:C1000,((Sheet1!G2:G1000=(MINIFS(Sheet1!G2:G1000,Sheet1!J2:J1000,b)))*(Sheet1!J2:J1000=b)))))),1)

 

[KarthickDijo]

Ok, how about

=DROP(REDUCE("",K3:K5,LAMBDA(a,b,VSTACK(a,FILTER(Sheet1!C2:C1000,((Sheet1!G2:G1000=(MINIFS(Sheet1!G2:G1000,Sheet1!J2:J1000,b)))*(Sheet1!J2:J1000=b)))))),1)

Nope Am getting #CALC error.

 

[Fluff]

In that case can you also post the data from sheet1.