Data Validation List without duplicates

[dsubash]

Dear experts,

I have a database in Sheet 1, which contains the following fields
Col. A - List of Branches (Contains Duplicate Entries)
Col. B - List of Products (Contains Duplicate Entries) - certain products are unique to only certain branches while few products are available in all branches.
Col. C - Col. M - other details.

This sheet contains more than 10000 records containing the transactions for a particular month.

I need to prepare a report in Sheet 2 with the Branch and Product as criterias.
In Cell. A2, I have created a dropdown list for branches (after removing duplicates) and used Name Manager to create a Data Validation List.
I need the following in Cell B2 (Sheet 2)
a. A dropdown list of products from Col. B of Sheet 1
b. I need only the products list available against the branch selected in Cell A1 and should not display the products that are not available in the specific branch.
c. Duplicate product names to be removed and only unique names to be available in the dropdown list.
d. Option for autofill product names when one or more characters are typed.
e. Dropdown list should not contain blank entries
f. If possible Product names to be alphabetically displayed.

I tried to use Vlookup and for listing products against each branch in different columns, tried removing duplicates from this list in another column and using name manager for each branch. But since my list of branches and Products are more (20 + branches and 500 + products), I found it difficult to create multiple columns for vlookup and removing duplicates. Also had the problem of blanks in the drop down.

Is there any other options (VBA Code) to get what i want. I am using Office 2019 Professional and hence cannot use formulas avaialble in later versions (Sort / Unique, etc).

I tried viewing many threads in the forum, but was unable to find what i want. Threads available were mostly for higher versions of excel.

Thanks and regards
Subash D

 

[Akuini]

Is there any other options (VBA Code) to get what i want. I am using Office 2019 Professional and hence cannot use formulas avaialble in later versions (Sort / Unique, etc).

1. To arrange a set of dependent data validation, you can try this method:
3 level dependent data validation, with a macro to create a helper table.
Note: to make things easier, you can add a command button on the sheet to run the macro to refresh/recreate the table.
2. To get autocomplete data validation via a dynamic combobox, try this add-in:
Search deList

 

[kvsrinivasamurthy]

Pl post sample data, preferably in XL2BB format.

 

[dsubash]

Pl post sample data, preferably in XL2BB format.

Dear Murthy,

Please find below the sample file in XL2BB format

Sheet 1 (Database)

Sample Data - 25-12-2024.xlsm
	A	B	C	D	E	F	G
1	Branch	Product	Type	UOM	Date	Inv. No.	Price
2	Sydney	0.1ml 96 well PCR Plate	Glassware	Nos	01/12/2024	55970
3	Ireland	0.2ml 96 well PCR Plate-25's	Glassware	Box	02/12/2024	56013
4	Japan	0.2ml PCR 8 Strip	Glassware	Box	02/12/2024	56014
5	Tokyo	8 Strip Caps	Glassware	Pkt	02/12/2024	56018
6	Delhi	Acid Fast Stain Kit	Chemical	Box	02/12/2024	56020
7	Ohio	Autobio-Cuvette 1000's	Reagent	Nos	02/12/2024	56021
8	New Jersey	AVE-Buffer 40ml	Reagent	Box	02/12/2024	56022
9	Bangkok	AVE-Cleanser I-500ml	Reagent	Box	02/12/2024	56023
10	Bangalore	AVE-Cleanser II-40ml	Reagent	Box	02/12/2024	56024
11	New Jersey	Barium Chloride 10% 500ml	Reagent	Box	02/12/2024	56026
12	Bangkok	Benzidine Powder-25gm	Reagent	Nos	02/12/2024	56027
13	Bangalore	Biomedical Waste Cover Blue-Medium	Reagent	Nos	02/12/2024	56029
14	Tokyo	Biomedical Waste Cover Green-Small	Reagent	Nos	02/12/2024	56036
15	Delhi	Biomedical Waste Cover Red-Medium	Reagent	Nos	02/12/2024	56037
16	Ohio	Biomedical Waste Cover Yellow-Medium	Reagent	Nos	02/12/2024	56039
17	New Jersey	Bio-Rad QC Level 1-5ml	Reagent	Nos	02/12/2024	56040
18	Bangkok	Bio-Rad QC Level 2-5ml	Reagent	Nos	02/12/2024	56046
19	Bangalore	Blood Culture Broth-20ml	Reagent	Nos	02/12/2024	56047
20	New Jersey	Blood Culture Broth-70ml	Reagent	Nos	02/12/2024	56048
21	Bangkok	BT Cleaning Solution	Reagent	Nos	02/12/2024	56049
22	Bangalore	BT Washing Solution	Reagent	Nos	02/12/2024	56050
23	Bangkok	Buds	Reagent	Nos	02/12/2024	56051
24	Bangalore	Calcium Chloride-10ml	Reagent	Nos	02/12/2024	56052
25	Tokyo	Cartridge BG10-25T	Reagent	Nos	02/12/2024	56053
26	Delhi	Cedarwood Oil-25ml	Reagent	Nos	02/12/2024	56054
27	Ohio	Chloride-25T	Reagent	Nos	02/12/2024	56055
28	New Jersey	Coralyser Cuvettes	Reagent	Nos	02/12/2024	56056
29	Bangkok	Coralyzer Surfactant-2X75ml	Reagent	Nos	02/12/2024	56058
30	Bangalore	Cover Slip	Reagent	Nos	02/12/2024	56059
31	New Jersey	Deionized Water-20Ltr	Reagent	Nos	02/12/2024	56062
32	Bangalore	Diluent-20Ltr (Matrix 5 Part)	Reagent	Nos	02/12/2024	56063
33	Bangkok	Diluent-20Ltr (Zybio Z3)	Reagent	Nos	02/12/2024	56064
34	Bangalore	Diluent-20Ltr (Zybio Z5)	Reagent	Nos	02/12/2024	56065
35	Tokyo	Amikacin 30mcg-100's (AK30)	Chemical	Box	02/12/2024	56066
36	Sydney	Amoxicillin/Clavulanate 20/10mcg 100's (AMC20/10)	Chemical	Box	02/12/2024	56070
37	Ireland	Azithromycin 15mcg 100's (AZM15)	Reagent	Box	02/12/2024	56072
38	Japan	Aztreonam 30mcg 100's (AT30)	Reagent	Box	02/12/2024	56073
39	Tokyo	Bacitracin-100's	Reagent	Box	02/12/2024	56074
40	Delhi	Cefazolin 30mcg 100's (CZ30)	Reagent	Nos	02/12/2024	56075
41	Ohio	Cefdinir 5MCG 100's	Reagent	Nos	02/12/2024	56076
42	New Jersey	Cefepime 30mcg 100's (CPM30)	Reagent	Nos	02/12/2024	56077
43	Bangkok	Cefixime 5mcg 100's	Reagent	Nos	02/12/2024	56078
44	Bangalore	Cefotoxime 30mcg 100's (CTX30)	Reagent	Nos	02/12/2024	56081
Sheet 1

Sheet 2 (Report)

Sample Data - 25-12-2024.xlsm
	A	B	C	D	E	F
1	Branch	Product
2	Branch to be selected using Data Validation -- List	a. A dropdown list of products from Col. B of Sheet 1 b. I need only the products list available against the branch selected in Cell A1 and should not display the products that are not available in the specific branch. c. Duplicate product names to be removed and only unique names to be available in the dropdown list. d. Option for autofill product names when one or more characters are typed. e. Dropdown list should not contain blank entries f. If possible Product names to be alphabetically displayed.
3
4
5
6	Sl. No	Type	UOM	Date	Inv. No.	Price
7	1
8	2
9	3
10	4	Need to display all items matching the Branch and Product criteria from sheet 1
11	5
12	6
13	7
14	8
15	9
16	10
17	11
18	12
19	13
20	14
21	15
22	16
23	17
24	18
25	19
26	20
27	21
28	22
29
Sheet 2

This is only a sample data. Hope this is fine with you. Actually my database runs to more than 100000 rows.

Thanks & Regards
Subash D

 

[kvsrinivasamurthy]

Validation list.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Branch	Product	Type	UOM	Date	Inv. No.	Price				Unique Branches	Branch Products
2	Sydney	0.1ml 96 well PCR Plate	Glassware	Nos	45627	55970					Sydney	AVE-Cleanser II-40ml
3	Ireland	0.2ml 96 well PCR Plate-25's	Glassware	Box	45628	56013					Ireland	Biomedical Waste Cover Blue-Medium
4	Japan	0.2ml PCR 8 Strip	Glassware	Box	45628	56014					Japan	Blood Culture Broth-20ml
5	Tokyo	8 Strip Caps	Glassware	Pkt	45628	56018					Tokyo	BT Washing Solution
6	Delhi	Acid Fast Stain Kit	Chemical	Box	45628	56020					Delhi	Calcium Chloride-10ml
7	Ohio	Autobio-Cuvette 1000's	Reagent	Nos	45628	56021					Ohio	Cover Slip
8	New Jersey	AVE-Buffer 40ml	Reagent	Box	45628	56022					New Jersey	Diluent-20Ltr (Matrix 5 Part)
9	Bangkok	AVE-Cleanser I-500ml	Reagent	Box	45628	56023					Bangkok	Diluent-20Ltr (Zybio Z5)
10	Bangalore	AVE-Cleanser II-40ml	Reagent	Box	45628	56024					Bangalore	Cefotoxime 30mcg 100's (CTX30)
11	New Jersey	Barium Chloride 10% 500ml	Reagent	Box	45628	56026
12	Bangkok	Benzidine Powder-25gm	Reagent	Nos	45628	56027
13	Bangalore	Biomedical Waste Cover Blue-Medium	Reagent	Nos	45628	56029
14	Tokyo	Biomedical Waste Cover Green-Small	Reagent	Nos	45628	56036
15	Delhi	Biomedical Waste Cover Red-Medium	Reagent	Nos	45628	56037
16	Ohio	Biomedical Waste Cover Yellow-Medium	Reagent	Nos	45628	56039
17	New Jersey	Bio-Rad QC Level 1-5ml	Reagent	Nos	45628	56040
18	Bangkok	Bio-Rad QC Level 2-5ml	Reagent	Nos	45628	56046
19	Bangalore	Blood Culture Broth-20ml	Reagent	Nos	45628	56047
20	New Jersey	Blood Culture Broth-70ml	Reagent	Nos	45628	56048
21	Bangkok	BT Cleaning Solution	Reagent	Nos	45628	56049
22	Bangalore	BT Washing Solution	Reagent	Nos	45628	56050
23	Bangkok	Buds	Reagent	Nos	45628	56051
24	Bangalore	Calcium Chloride-10ml	Reagent	Nos	45628	56052			Calcium Chloride-10ml
25	Tokyo	Cartridge BG10-25T	Reagent	Nos	45628	56053
26	Delhi	Cedarwood Oil-25ml	Reagent	Nos	45628	56054
27	Ohio	Chloride-25T	Reagent	Nos	45628	56055
28	New Jersey	Coralyser Cuvettes	Reagent	Nos	45628	56056
29	Bangkok	Coralyzer Surfactant-2X75ml	Reagent	Nos	45628	56058
30	Bangalore	Cover Slip	Reagent	Nos	45628	56059
31	New Jersey	Deionized Water-20Ltr	Reagent	Nos	45628	56062
32	Bangalore	Diluent-20Ltr (Matrix 5 Part)	Reagent	Nos	45628	56063
33	Bangkok	Diluent-20Ltr (Zybio Z3)	Reagent	Nos	45628	56064
34	Bangalore	Diluent-20Ltr (Zybio Z5)	Reagent	Nos	45628	56065
35	Tokyo	Amikacin 30mcg-100's (AK30)	Chemical	Box	45628	56066
36	Sydney	Amoxicillin/Clavulanate 20/10mcg 100's (AMC20/10)	Chemical	Box	45628	56070
37	Ireland	Azithromycin 15mcg 100's (AZM15)	Reagent	Box	45628	56072
38	Japan	Aztreonam 30mcg 100's (AT30)	Reagent	Box	45628	56073
39	Tokyo	Bacitracin-100's	Reagent	Box	45628	56074
40	Delhi	Cefazolin 30mcg 100's (CZ30)	Reagent	Nos	45628	56075
41	Ohio	Cefdinir 5MCG 100's	Reagent	Nos	45628	56076
42	New Jersey	Cefepime 30mcg 100's (CPM30)	Reagent	Nos	45628	56077
43	Bangkok	Cefixime 5mcg 100's	Reagent	Nos	45628	56078
44	Bangalore	Cefotoxime 30mcg 100's (CTX30)	Reagent	Nos	45628	56081
45
Sheet1

Cell Formulas
Range	Formula
L2:L44	L2	=IFERROR(INDEX($B$2:$B$44,AGGREGATE(15,6,ROW($B$2:$B$44)/(ISERROR(MATCH($B$2:$B$44,$L$1:$L1,0))*($A$2:$A$44=Sheet2!$A$2)),1)-ROW($B$1)),"")
K2:K45	K2	=IFERROR(INDEX($A$2:$A$44,AGGREGATE(15,6,ROW($A$2:$A$44)/(ISERROR(MATCH($A$2:$A$44,$K$1:$K1,0))),1)-ROW($A$1)),"")

Copy down Formulas in K2 and L2 Till row Number 44

Validation list.xlsm
	A	B	C	D	E	F	G
1
2	Bangalore	BT Washing Solution
3
4
5
6	Sl. No	Type	UOM	Date	Inv. No.	Price
7	1	Reagent	Nos	45628	56050	0
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sheet2

Cell Formulas
Range	Formula
A7:A27	A7	=IF(LEN(B7)>0,SUM(A6)+1,"")
B7:F27	B7	=IFERROR(INDEX(Sheet1!C$2:C$44,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$44)/(ISNUMBER(MATCH(Sheet1!$A$2:$A$44,Sheet2!$A$2,0))*ISNUMBER(MATCH(Sheet1!$B$2:$B$44,Sheet2!$B$2,0))),ROWS($C$7:$C7))-ROW($C$1)),"")

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=OFFSET(Sheet1!$K$2,0,0,COUNTIF(Sheet1!$K$2:$K$100,"?*"))
B2	List	=OFFSET(Sheet1!$L$2,0,0,COUNTIF(Sheet1!$L$2:$L$100,"?*"))

Formulas in A6 , copy down.
Formula in B6 to be copied to full range.
Formua for Dv in A1
=OFFSET(Sheet1!$K$2,0,0,COUNTIF(Sheet1!$K$2:$K$100,"?*"))
Formula for Dv in B2
=OFFSET(Sheet1!$L$2,0,0,COUNTIF(Sheet1!$L$2:$L$100,"?*"))

 

[dsubash]

Validation list.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Branch	Product	Type	UOM	Date	Inv. No.	Price				Unique Branches	Branch Products
2	Sydney	0.1ml 96 well PCR Plate	Glassware	Nos	45627	55970					Sydney	AVE-Cleanser II-40ml
3	Ireland	0.2ml 96 well PCR Plate-25's	Glassware	Box	45628	56013					Ireland	Biomedical Waste Cover Blue-Medium
4	Japan	0.2ml PCR 8 Strip	Glassware	Box	45628	56014					Japan	Blood Culture Broth-20ml
5	Tokyo	8 Strip Caps	Glassware	Pkt	45628	56018					Tokyo	BT Washing Solution
6	Delhi	Acid Fast Stain Kit	Chemical	Box	45628	56020					Delhi	Calcium Chloride-10ml
7	Ohio	Autobio-Cuvette 1000's	Reagent	Nos	45628	56021					Ohio	Cover Slip
8	New Jersey	AVE-Buffer 40ml	Reagent	Box	45628	56022					New Jersey	Diluent-20Ltr (Matrix 5 Part)
9	Bangkok	AVE-Cleanser I-500ml	Reagent	Box	45628	56023					Bangkok	Diluent-20Ltr (Zybio Z5)
10	Bangalore	AVE-Cleanser II-40ml	Reagent	Box	45628	56024					Bangalore	Cefotoxime 30mcg 100's (CTX30)
11	New Jersey	Barium Chloride 10% 500ml	Reagent	Box	45628	56026
12	Bangkok	Benzidine Powder-25gm	Reagent	Nos	45628	56027
13	Bangalore	Biomedical Waste Cover Blue-Medium	Reagent	Nos	45628	56029
14	Tokyo	Biomedical Waste Cover Green-Small	Reagent	Nos	45628	56036
15	Delhi	Biomedical Waste Cover Red-Medium	Reagent	Nos	45628	56037
16	Ohio	Biomedical Waste Cover Yellow-Medium	Reagent	Nos	45628	56039
17	New Jersey	Bio-Rad QC Level 1-5ml	Reagent	Nos	45628	56040
18	Bangkok	Bio-Rad QC Level 2-5ml	Reagent	Nos	45628	56046
19	Bangalore	Blood Culture Broth-20ml	Reagent	Nos	45628	56047
20	New Jersey	Blood Culture Broth-70ml	Reagent	Nos	45628	56048
21	Bangkok	BT Cleaning Solution	Reagent	Nos	45628	56049
22	Bangalore	BT Washing Solution	Reagent	Nos	45628	56050
23	Bangkok	Buds	Reagent	Nos	45628	56051
24	Bangalore	Calcium Chloride-10ml	Reagent	Nos	45628	56052			Calcium Chloride-10ml
25	Tokyo	Cartridge BG10-25T	Reagent	Nos	45628	56053
26	Delhi	Cedarwood Oil-25ml	Reagent	Nos	45628	56054
27	Ohio	Chloride-25T	Reagent	Nos	45628	56055
28	New Jersey	Coralyser Cuvettes	Reagent	Nos	45628	56056
29	Bangkok	Coralyzer Surfactant-2X75ml	Reagent	Nos	45628	56058
30	Bangalore	Cover Slip	Reagent	Nos	45628	56059
31	New Jersey	Deionized Water-20Ltr	Reagent	Nos	45628	56062
32	Bangalore	Diluent-20Ltr (Matrix 5 Part)	Reagent	Nos	45628	56063
33	Bangkok	Diluent-20Ltr (Zybio Z3)	Reagent	Nos	45628	56064
34	Bangalore	Diluent-20Ltr (Zybio Z5)	Reagent	Nos	45628	56065
35	Tokyo	Amikacin 30mcg-100's (AK30)	Chemical	Box	45628	56066
36	Sydney	Amoxicillin/Clavulanate 20/10mcg 100's (AMC20/10)	Chemical	Box	45628	56070
37	Ireland	Azithromycin 15mcg 100's (AZM15)	Reagent	Box	45628	56072
38	Japan	Aztreonam 30mcg 100's (AT30)	Reagent	Box	45628	56073
39	Tokyo	Bacitracin-100's	Reagent	Box	45628	56074
40	Delhi	Cefazolin 30mcg 100's (CZ30)	Reagent	Nos	45628	56075
41	Ohio	Cefdinir 5MCG 100's	Reagent	Nos	45628	56076
42	New Jersey	Cefepime 30mcg 100's (CPM30)	Reagent	Nos	45628	56077
43	Bangkok	Cefixime 5mcg 100's	Reagent	Nos	45628	56078
44	Bangalore	Cefotoxime 30mcg 100's (CTX30)	Reagent	Nos	45628	56081
45
Sheet1

Cell Formulas
Range	Formula
L2:L44	L2	=IFERROR(INDEX($B$2:$B$44,AGGREGATE(15,6,ROW($B$2:$B$44)/(ISERROR(MATCH($B$2:$B$44,$L$1:$L1,0))*($A$2:$A$44=Sheet2!$A$2)),1)-ROW($B$1)),"")
K2:K45	K2	=IFERROR(INDEX($A$2:$A$44,AGGREGATE(15,6,ROW($A$2:$A$44)/(ISERROR(MATCH($A$2:$A$44,$K$1:$K1,0))),1)-ROW($A$1)),"")

Copy down Formulas in K2 and L2 Till row Number 44

Validation list.xlsm
	A	B	C	D	E	F	G
1
2	Bangalore	BT Washing Solution
3
4
5
6	Sl. No	Type	UOM	Date	Inv. No.	Price
7	1	Reagent	Nos	45628	56050	0
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sheet2

Cell Formulas
Range	Formula
A7:A27	A7	=IF(LEN(B7)>0,SUM(A6)+1,"")
B7:F27	B7	=IFERROR(INDEX(Sheet1!C$2:C$44,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$44)/(ISNUMBER(MATCH(Sheet1!$A$2:$A$44,Sheet2!$A$2,0))*ISNUMBER(MATCH(Sheet1!$B$2:$B$44,Sheet2!$B$2,0))),ROWS($C$7:$C7))-ROW($C$1)),"")

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=OFFSET(Sheet1!$K$2,0,0,COUNTIF(Sheet1!$K$2:$K$100,"?*"))
B2	List	=OFFSET(Sheet1!$L$2,0,0,COUNTIF(Sheet1!$L$2:$L$100,"?*"))

Formulas in A6 , copy down.
Formula in B6 to be copied to full range.
Formua for Dv in A1
=OFFSET(Sheet1!$K$2,0,0,COUNTIF(Sheet1!$K$2:$K$100,"?*"))
Formula for Dv in B2
=OFFSET(Sheet1!$L$2,0,0,COUNTIF(Sheet1!$L$2:$L$100,"?*"))

Sir, you formula works fine in Col. K, but when copied your formula in Col. L, it is asking for source file (image given below)

Any changes to the formula to be done? Yet to try the formulas in Sheet 2.

 

[kvsrinivasamurthy]

Also use this worksheet event code for Sheet1. Whenever A2 value changed in Sheet2 B2 value will become empty.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then Target.Offset(0, 1) = ""
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Select worksheet in drop down instead of General
Paste the code
Close the VB window.
Save the file as .xlsm

 

[kvsrinivasamurthy]

I have used sheet names as Sheet1 and Sheet2.
But Your sheet names are Sheet 1 and Sheet 2.
So in formulas Change Sheet1 as 'Sheet 1' and Sheet2 as 'Sheet 2'.

 

[dsubash]

Copied your code, but still it is asking for the source file

Sample Data - 25-12-2024.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Branch	Product	Type	UOM	Date	Inv. No.	Price				Unique Branches	Branch Product
2	Sydney	0.1ml 96 well PCR Plate	Glassware	Nos	01/12/2024	55970					Sydney
3	Ireland	0.2ml 96 well PCR Plate-25's	Glassware	Box	02/12/2024	56013					Ireland
4	Japan	0.2ml PCR 8 Strip	Glassware	Box	02/12/2024	56014					Japan
5	Tokyo	8 Strip Caps	Glassware	Pkt	02/12/2024	56018					Tokyo
6	Delhi	Acid Fast Stain Kit	Chemical	Box	02/12/2024	56020					Delhi
7	Ohio	Autobio-Cuvette 1000's	Reagent	Nos	02/12/2024	56021					Ohio
8	New Jersey	AVE-Buffer 40ml	Reagent	Box	02/12/2024	56022					New Jersey
9	Bangkok	AVE-Cleanser I-500ml	Reagent	Box	02/12/2024	56023					Bangkok
10	Bangalore	AVE-Cleanser II-40ml	Reagent	Box	02/12/2024	56024					Bangalore
11	New Jersey	Barium Chloride 10% 500ml	Reagent	Box	02/12/2024	56026
12	Bangkok	Benzidine Powder-25gm	Reagent	Nos	02/12/2024	56027
13	Bangalore	Biomedical Waste Cover Blue-Medium	Reagent	Nos	02/12/2024	56029
14	Tokyo	Biomedical Waste Cover Green-Small	Reagent	Nos	02/12/2024	56036
15	Delhi	Biomedical Waste Cover Red-Medium	Reagent	Nos	02/12/2024	56037
16	Ohio	Biomedical Waste Cover Yellow-Medium	Reagent	Nos	02/12/2024	56039
17	New Jersey	Bio-Rad QC Level 1-5ml	Reagent	Nos	02/12/2024	56040
18	Bangkok	Bio-Rad QC Level 2-5ml	Reagent	Nos	02/12/2024	56046
Sheet 1

Cell Formulas
Range	Formula
K2:K18	K2	=IFERROR(INDEX($A$2:$A$44,AGGREGATE(15,6,ROW($A$2:$A$44)/(ISERROR(MATCH($A$2:$A$44,$K$1:$K1,0))),1)-ROW($A$1)),"")
L2	L2	=IFERROR(INDEX($B$2:$B$215,AGGREGATE(15,6,ROW($B$2:$B$215)/(ISERROR(MATCH($B$2:$B$215,$L$1:$L1,0))*($A$2:$A$215=Sheet2!$A$2)),1)-ROW($B$1)),"")
L3:L18	L3	=IFERROR(INDEX($B$2:$B$215,AGGREGATE(15,6,ROW($B$2:$B$215)/(ISERROR(MATCH($B$2:$B$215,$L$1:$L2,0))*($A$2:$A$215=Sheet2!$A$2)),1)-ROW($B$1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
'Sheet 1'!_FilterDatabase	='Sheet 1'!$A$1:$A$10000	K2:K18

 

[dsubash]

Thanks So much Mr. Murthy.

Your codes and formula's returned values as expected.

Only issue is due to the large data (20000 rows approx), excel is taking too much time to calculate and return values, otherwise, I am able to prepare the required reports easily.