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

 

[kvsrinivasamurthy]

Glad that the problem solved.

 

[dsubash]

Dear Moorthy,

I applied your formulas and codes for a different file. Everything works fine expect one aspect.

In Sheet 2 (Requirements), When I change Column B8 - B200, I need only the list of Items corresponding to the Instrument name in Column B to the available in the dropdown box in Column C. Right now, this works fine only for Cell B8, but from B9 onwards, it is not working properly.

I have attached the sample file for your reference. Please let me know where am i going wrong.

Sheet 1 (Master Sheet)

Instruments & Reagents - Sample.xlsm
	A	B	C	D	E	F	G	H	I
1	Instrument / Department	Item Name	Type	UOM	Price			Instrument / Department	Item Name
2	RTPCR	0.1ml 96 well PCR Plate	Glassware	Nos				RTPCR	Coralyser Cuvettes
3	RTPCR	0.2ml 96 well PCR Plate-25's	Glassware	Box				1	Coralyzer Surfactant-2X75ml
4	RTPCR	0.2ml PCR 8 Strip	Glassware	Box				Autobio	Coral Glucose-3X150ml
5	1	8 Strip Caps	Glassware	Pkt				Biomedical Waste	Coralyser 200 Glucose-80ml
6	1	Acetic Acid Glacial-500ml	Chemical	Nos				QC	Coralyser 200 Magnesium-2X40ml
7	1	Acid Fast Stain Kit	Chemical	Box	278			House Keeping	Coralyzer 200 Albumin Kit-80ml
8	Autobio	Autobio-Cuvette 1000's	Glassware	Pkt	2360			Coralyser	Coralyzer 200 Alkaline Phosphatase-40ml
9	1	AVE-Buffer 40ml	Chemical	Nos	11800			Zybio	Coralyzer 200 Calcium Kit-40ml
10	1	AVE-Cleanser I-500ml	Chemical	Nos	14160			Microbiology	Coralyzer 200 CK MB-40ml
11	1	AVE-Cleanser II-40ml	Chemical	Nos	11800			Centrifuge	Coralyzer 200 CK NAC-40ml
12	1	Barium Chloride 10% 500ml	Chemical	Nos				Mispa	Coralyzer 200 Creatinine-60ml
13	1	Benzidine Powder-25gm	Chemical	Nos				Sample Collection	Coralyzer 200 Gamma GT-40ml
14	Biomedical Waste	Biomedical Waste Cover Blue-Medium	House Keeping	Nos	15			Truenat	Coralyzer 200 HDL-D Cholestrol-40ml
15	Biomedical Waste	Biomedical Waste Cover Green-Small	House Keeping	Nos	15			Printing	Coralyzer 200 LDH-40ml
16	Biomedical Waste	Biomedical Waste Cover Red-Medium	House Keeping	Nos	15			Radiology	Coralyzer 200 LDL-D Cholestrol-40ml
17	Biomedical Waste	Biomedical Waste Cover Yellow-Medium	House Keeping	Nos	15			Finecare	Coralyzer 200 Lipase-25ml
18	QC	Bio-Rad QC Level 1-5ml	Chemical	Nos	840			Elisa	Coralyzer 200 Phosphorus(UV)-40ml
19	QC	Bio-Rad QC Level 2-5ml	Chemical	Nos	840			Lifotronics	Coralyzer 200 SGOT Kit-80ml
20	1	Blood Culture Broth-20ml	Chemical	Nos	146			Stationery	Coralyzer 200 SGPT-80ml
21	1	Blood Culture Broth-70ml	Chemical	Nos	152			Surgical	Coralyzer 200 Total Protein-80ml
22	1	BT Cleaning Solution	Chemical	Nos	7000				Coralyzer 200 Triglyceride-80ml
23	1	BT Washing Solution	Chemical	Nos	3500				Coralyzer 200 Urea Kit-80ml
24	House Keeping	Buds	House Keeping	Pkt	10				Coralyzer 200 Uric Acid-40ml
25	1	Calcium Chloride-10ml	Chemical	Nos	208				coralyzer 200-Amylase Kit-40ml
26	1	Cartridge BG10-25T	Chemical	Nos	13216				Coralyzer 200-Cholestrol Kit-80ml
27	1	Cedarwood Oil-25ml	Chemical	Nos	520				Coralyzer 200-Direct Bilirubin-80ml
28	1	Chloride-25T	Chemical	Box	410				Coralyzer 200-Iron Kit 75ml
29	Coralyser	Coralyser Cuvettes	Glassware	Nos	85				Coralyzer 200-Total Bilirubin-80ml
30	Coralyser	Coralyzer Surfactant-2X75ml	Chemical	Nos	2493				Coralyzer Cleaning Solution-250ml
31	1	Cover Slip	Glassware	Nos	95
32	House Keeping	Deionized Water-20Ltr	House Keeping	Can	250
Master

Cell Formulas
Range	Formula
H2:H32	H2	=IFERROR(INDEX($A$2:$A$500,AGGREGATE(15,6,ROW($A$2:$A$500)/(ISERROR(MATCH($A$2:$A$500,$H$1:$H1,0))),1)-ROW($A$1)),"")
I2:I32	I2	=IFERROR(INDEX($B$2:$B$500,AGGREGATE(15,6,ROW($B$2:$B$500)/(ISERROR(MATCH($B$2:$B$500,$I$1:$I1,0))*($A$2:$A$500=Requirements!$B$8)),1)-ROW($B$1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Master!$A$1:$D$498	H2:H32

Sheet 2 (Requirements Sheet)

Instruments & Reagents - Sample.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3			Branch
4			Requirements for the Month of
5
6	Sl. No.	Requirements (Select from the dropdown list)		Type	UOM	Stock in Hand		Weekly Requirements					Total Cost	Remarks
7		Instrument / Department	Item Name			Unopened	In Use	1st Week	2nd Week	3rd Week	4th Week	Tot. Qty.
8	1	Coralyser	Zybio-ALT	Reagent	Nos			1	2			3	2940
9	2	Truenat	Coralyser Cuvettes	Glassware	Nos
10	3	House Keeping	Coralyser Cuvettes	Glassware	Nos
11	4	Autobio	Coralyser Cuvettes	Glassware	Nos
12	5	House Keeping	Coralyser Cuvettes	Glassware	Nos
13
14
Requirements

Cell Formulas
Range	Formula
D8:D14	D8	=IF(C8="","",VLOOKUP(C8,Master!$B$2:$E$451,2,0))
E8:E14	E8	=IF(C8="","",VLOOKUP(C8,Master!$B$2:$E$451,3,0))
L8:L14	L8	=IF(C8="","",SUM(H8:K8))
M8:M14	M8	=IF(C8="","",VLOOKUP(C8,Master!$B$2:$E$500,4,0)*L8)
A8	A8	=IF(LEN(B8)>0,SUM(A7)+1,"")
A9:A14	A9	=IF(LEN(C9)>0,SUM(A8)+1,"")

Cells with Data Validation
Cell	Allow	Criteria
B8:B23	List	=OFFSET(Master!$H$2,0,0,COUNTIF(Master!$H$2:$H$404,"?*"))
C8	List	=OFFSET(Master!$I$2,0,0,COUNTIF(Master!$I$2:$I$500,"?*"))
C9:C13	List	=OFFSET(Master!$I$2,0,0,COUNTIF(Master!$I$2:$I500,"?*"))
C14:C413	List	=OFFSET(Master!$I$2,0,0,COUNTIF(Master!$I$2:$I$500,"?*"))

Thanks & Regards
Subash D

 

[kvsrinivasamurthy]

Since you are having problem of slow here is the revised solution for Post#1.

Validation list.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Branch	Product	Type	UOM	Date	Inv. No.	Price				Unique Branches	Sydney	Ireland	Japan	Tokyo	Delhi	Ohio	New Jersey	Bangkok	Bangalore
2	Sydney	0.1ml 96 well PCR Plate	Glassware	Nos	01-12-2024	55970					Sydney	0.1ml 96 well PCR Plate	0.2ml 96 well PCR Plate-25's	0.2ml PCR 8 Strip	8 Strip Caps	Acid Fast Stain Kit	Autobio-Cuvette 1000's	AVE-Buffer 40ml	AVE-Cleanser I-500ml	AVE-Cleanser II-40ml
3	Ireland	0.2ml 96 well PCR Plate-25's	Glassware	Box	02-12-2024	56013					Ireland				Biomedical Waste Cover Green-Small	Biomedical Waste Cover Red-Medium	Biomedical Waste Cover Yellow-Medium	Barium Chloride 10% 500ml	Benzidine Powder-25gm	Biomedical Waste Cover Blue-Medium
4	Japan	0.2ml PCR 8 Strip	Glassware	Box	02-12-2024	56014					Japan							Bio-Rad QC Level 1-5ml	Bio-Rad QC Level 2-5ml
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
L1:V1	L1	=INDEX($K$2:$K$48,COLUMNS($L$1:L$1))
K2:K18	K2	=IFERROR(INDEX($A$2:$A$44,AGGREGATE(15,6,ROW($A$2:$A$44)/(($A$2:$A$44<>"")*ISERROR(MATCH($A$2:$A$44,$K$1:$K1,0))),1)-ROW($A$1)),"")
L2:V18	L2	=IF(L$1="","",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=L$1)),ROWS(L$2:L2))-ROW($B$1)),""))

Validation list.xlsm
	A	B	C	D	E	F	G
1
2	Bangalore	AVE-Cleanser II-40ml
3
4
5
6	Sl. No	Type	UOM	Date	Inv. No.	Price
7	1	Reagent	Box	45628	56024	0
8
9
Sheet 2

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

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=OFFSET(Master!$H$2,0,0,COUNTIF(Master!$H$2:$H$100,"?*"))
B2	List	=OFFSET('Sheet 1'!$K$2,0,MATCH($A2,'Sheet 1'!$L$1:$AJ$1,0),COUNTIF(INDEX('Sheet 1'!$L$2:$AJ$48,,MATCH($A2,'Sheet 1'!$L$1:$AJ$1,0)),"*?"))

 

[kvsrinivasamurthy]

Ref Post#12

Validation list.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Instrument / Department	RTPCR	1	Autobio	Biomedical Waste	QC	House Keeping	Coralyser	Bangkok	Bangalore	Tokyo	Sydney	Ireland
2		RTPCR	0.1ml 96 well PCR Plate	8 Strip Caps	Autobio-Cuvette 1000's	Biomedical Waste Cover Blue-Medium	Bio-Rad QC Level 1-5ml	Buds	Coralyser Cuvettes	Diluent-20Ltr (Zybio Z3)	Diluent-20Ltr (Zybio Z5)	Amikacin 30mcg-100's (AK30)	Amoxicillin/Clavulanate 20/10mcg 100's (AMC20/10)	Azithromycin 15mcg 100's (AZM15)
3		1	0.2ml PCR 8 Strip	Acetic Acid Glacial-500ml		Biomedical Waste Cover Green-Small	Bio-Rad QC Level 2-5ml	Deionized Water-20Ltr	Coralyzer Surfactant-2X75ml	Cefixime 5mcg 100's	Cefotoxime 30mcg 100's (CTX30)	Bacitracin-100's
4		Autobio		Acid Fast Stain Kit		Biomedical Waste Cover Red-Medium
5		Biomedical Waste		AVE-Buffer 40ml		Biomedical Waste Cover Yellow-Medium
6		QC		AVE-Cleanser I-500ml
7		House Keeping		AVE-Cleanser II-40ml
8		Coralyser		Barium Chloride 10% 500ml
9		Bangkok		Benzidine Powder-25gm
10		Bangalore		Blood Culture Broth-20ml
11		Tokyo		Blood Culture Broth-70ml
12		Sydney		BT Cleaning Solution
13		Ireland		BT Washing Solution
14		Japan		Calcium Chloride-10ml
15		Delhi		Cartridge BG10-25T
16		Ohio		Cedarwood Oil-25ml
17		New Jersey		Chloride-25T
18				Cover Slip
19
20
21
Master

Cell Formulas
Range	Formula
I1:T1	I1	=INDEX($H$2:$H$48,COLUMNS($I$1:I$1))
H2:H21	H2	=IFERROR(INDEX($A$2:$A$44,AGGREGATE(15,6,ROW($A$2:$A$44)/(ISERROR(MATCH($A$2:$A$44,$H$1:$H1,0))),1)-ROW($A$1)),"")
I2:T21	I2	=IF(I$1="","",IFERROR(INDEX($B$2:$B$44,AGGREGATE(15,6,ROW($B$2:$B$44)/(ISERROR(MATCH($B$2:$B$44,$I$1:$I1,0))*($A$2:$A$44=I$1)),ROWS(I$2:I2))-ROW($B$1)),""))

Validation list.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2			RTPCR
3			Branch
4			Requirements for the Month of
5
6	Sl. No.	Requirements (Select from the dropdown list)		Type	UOM	Stock in Hand		Weekly Requirements					Total Cost	Remarks
7		Instrument / Department	Item Name			Unopened	In Use	1st Week	2nd Week	3rd Week	4th Week	Tot. Qty.
8
9	1	Autobio	Autobio-Cuvette 1000's
10
11	1	House Keeping	Deionized Water-20Ltr
12
13	1	Biomedical Waste	Biomedical Waste Cover Red-Medium
14
Requirements

Cell Formulas
Range	Formula
C2	C2	=OFFSET(Master!$H$2,0,0,COUNTIF(Master!$H$2:$H$100,"?*"))
A8:A14	A8	=IF(LEN(B8)>0,SUM(A7)+1,"")

Cells with Data Validation
Cell	Allow	Criteria
B8:B20	List	=OFFSET(Master!$H$2,0,0,COUNTIF(Master!$H$2:$H$48,"*?")+1)
C8:C20	List	=OFFSET(Master!$H$2,0,MATCH($B8,Master!$I$1:$AJ$1,0),COUNTIF(INDEX(Master!$I$2:$AJ$48,,MATCH($B8,Master!$I$1:$AJ$1,0)),"*?"))

In Master sheet Formula in T1 may be extended further.

 

[dsubash]

Ref Post#12

Validation list.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Instrument / Department	RTPCR	1	Autobio	Biomedical Waste	QC	House Keeping	Coralyser	Bangkok	Bangalore	Tokyo	Sydney	Ireland
2		RTPCR	0.1ml 96 well PCR Plate	8 Strip Caps	Autobio-Cuvette 1000's	Biomedical Waste Cover Blue-Medium	Bio-Rad QC Level 1-5ml	Buds	Coralyser Cuvettes	Diluent-20Ltr (Zybio Z3)	Diluent-20Ltr (Zybio Z5)	Amikacin 30mcg-100's (AK30)	Amoxicillin/Clavulanate 20/10mcg 100's (AMC20/10)	Azithromycin 15mcg 100's (AZM15)
3		1	0.2ml PCR 8 Strip	Acetic Acid Glacial-500ml		Biomedical Waste Cover Green-Small	Bio-Rad QC Level 2-5ml	Deionized Water-20Ltr	Coralyzer Surfactant-2X75ml	Cefixime 5mcg 100's	Cefotoxime 30mcg 100's (CTX30)	Bacitracin-100's
4		Autobio		Acid Fast Stain Kit		Biomedical Waste Cover Red-Medium
5		Biomedical Waste		AVE-Buffer 40ml		Biomedical Waste Cover Yellow-Medium
6		QC		AVE-Cleanser I-500ml
7		House Keeping		AVE-Cleanser II-40ml
8		Coralyser		Barium Chloride 10% 500ml
9		Bangkok		Benzidine Powder-25gm
10		Bangalore		Blood Culture Broth-20ml
11		Tokyo		Blood Culture Broth-70ml
12		Sydney		BT Cleaning Solution
13		Ireland		BT Washing Solution
14		Japan		Calcium Chloride-10ml
15		Delhi		Cartridge BG10-25T
16		Ohio		Cedarwood Oil-25ml
17		New Jersey		Chloride-25T
18				Cover Slip
19
20
21
Master

Cell Formulas
Range	Formula
I1:T1	I1	=INDEX($H$2:$H$48,COLUMNS($I$1:I$1))
H2:H21	H2	=IFERROR(INDEX($A$2:$A$44,AGGREGATE(15,6,ROW($A$2:$A$44)/(ISERROR(MATCH($A$2:$A$44,$H$1:$H1,0))),1)-ROW($A$1)),"")
I2:T21	I2	=IF(I$1="","",IFERROR(INDEX($B$2:$B$44,AGGREGATE(15,6,ROW($B$2:$B$44)/(ISERROR(MATCH($B$2:$B$44,$I$1:$I1,0))*($A$2:$A$44=I$1)),ROWS(I$2:I2))-ROW($B$1)),""))

Validation list.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2			RTPCR
3			Branch
4			Requirements for the Month of
5
6	Sl. No.	Requirements (Select from the dropdown list)		Type	UOM	Stock in Hand		Weekly Requirements					Total Cost	Remarks
7		Instrument / Department	Item Name			Unopened	In Use	1st Week	2nd Week	3rd Week	4th Week	Tot. Qty.
8
9	1	Autobio	Autobio-Cuvette 1000's
10
11	1	House Keeping	Deionized Water-20Ltr
12
13	1	Biomedical Waste	Biomedical Waste Cover Red-Medium
14
Requirements

Cell Formulas
Range	Formula
C2	C2	=OFFSET(Master!$H$2,0,0,COUNTIF(Master!$H$2:$H$100,"?*"))
A8:A14	A8	=IF(LEN(B8)>0,SUM(A7)+1,"")

Cells with Data Validation
Cell	Allow	Criteria
B8:B20	List	=OFFSET(Master!$H$2,0,0,COUNTIF(Master!$H$2:$H$48,"*?")+1)
C8:C20	List	=OFFSET(Master!$H$2,0,MATCH($B8,Master!$I$1:$AJ$1,0),COUNTIF(INDEX(Master!$I$2:$AJ$48,,MATCH($B8,Master!$I$1:$AJ$1,0)),"*?"))

In Master sheet Formula in T1 may be extended further.

Thanks a lot Mr. Murthy.

It works fine and have circulated to all my branches.

I need a small addition: Is it possible to categorize the dropdown items in alphbetical order and I would also like to know if there can be an option of auto suggestion wherein the list narrows down based on the characters typed in the dropdown box instead of scrolling up and down for the required item.

Thanks and Regards
Subash D

 

[dsubash]

Since you are having problem of slow here is the revised solution for Post#1.

Validation list.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Branch	Product	Type	UOM	Date	Inv. No.	Price				Unique Branches	Sydney	Ireland	Japan	Tokyo	Delhi	Ohio	New Jersey	Bangkok	Bangalore
2	Sydney	0.1ml 96 well PCR Plate	Glassware	Nos	01-12-2024	55970					Sydney	0.1ml 96 well PCR Plate	0.2ml 96 well PCR Plate-25's	0.2ml PCR 8 Strip	8 Strip Caps	Acid Fast Stain Kit	Autobio-Cuvette 1000's	AVE-Buffer 40ml	AVE-Cleanser I-500ml	AVE-Cleanser II-40ml
3	Ireland	0.2ml 96 well PCR Plate-25's	Glassware	Box	02-12-2024	56013					Ireland				Biomedical Waste Cover Green-Small	Biomedical Waste Cover Red-Medium	Biomedical Waste Cover Yellow-Medium	Barium Chloride 10% 500ml	Benzidine Powder-25gm	Biomedical Waste Cover Blue-Medium
4	Japan	0.2ml PCR 8 Strip	Glassware	Box	02-12-2024	56014					Japan							Bio-Rad QC Level 1-5ml	Bio-Rad QC Level 2-5ml
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
L1:V1	L1	=INDEX($K$2:$K$48,COLUMNS($L$1:L$1))
K2:K18	K2	=IFERROR(INDEX($A$2:$A$44,AGGREGATE(15,6,ROW($A$2:$A$44)/(($A$2:$A$44<>"")*ISERROR(MATCH($A$2:$A$44,$K$1:$K1,0))),1)-ROW($A$1)),"")
L2:V18	L2	=IF(L$1="","",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=L$1)),ROWS(L$2:L2))-ROW($B$1)),""))

Validation list.xlsm
	A	B	C	D	E	F	G
1
2	Bangalore	AVE-Cleanser II-40ml
3
4
5
6	Sl. No	Type	UOM	Date	Inv. No.	Price
7	1	Reagent	Box	45628	56024	0
8
9
Sheet 2

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

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=OFFSET(Master!$H$2,0,0,COUNTIF(Master!$H$2:$H$100,"?*"))
B2	List	=OFFSET('Sheet 1'!$K$2,0,MATCH($A2,'Sheet 1'!$L$1:$AJ$1,0),COUNTIF(INDEX('Sheet 1'!$L$2:$AJ$48,,MATCH($A2,'Sheet 1'!$L$1:$AJ$1,0)),"*?"))

Sir,

This one too much time even to copy formulas. It says "Calculating (12 Threads): 0%". I am not able to close this file nor open any other excel file when this specific file is open. Is it because of the range (20000 records). I am not able to complete copying the formulas in Sheet 1 itself. I have kept the file open for nearly 2 hours and had to close the file using task manager to work on other files.

Regards
Subash D

 

[kvsrinivasamurthy]

If Possible upload the file with only required Columns and erasing all other data in some website and give link here.. I will check.

 

[dsubash]

If Possible upload the file with only required Columns and erasing all other data in some website and give link here.. I will check.

Sir,

Have uploaded the actual template with all data's removed and retaining only the column headers in dropbox.

The link to download is

Dropbox

www.dropbox.com

Thanks
Subash D

 

[kvsrinivasamurthy]

Your file does not contain any data. I have asked you to keep the required data.
Anyway with reference to data in Post#1 Here is the VBA code for macro "GetBranchProductsList".
Remove all formulas From column K and on words in Sheet 1. Run the Macro to get list in Sheet 1.
For DV use formulas below for sheet 2.
For A2

=OFFSET('Sheet 1'!$K$2,0,0,COUNTA('Sheet 1'!$K$2:$K$1000))

For B2

=OFFSET('Sheet 1'!$K$2,0,MATCH($A2,'Sheet 1'!$L$1:$DZ$1,0),COUNTA(INDEX('Sheet 1'!$L$2:$DZ$1000,,MATCH($A2,'Sheet 1'!$L$1:$DZ$1,0))))

Max Rows I have taken is 1000. if required Change it.
VBA code;

Sub GetBranchProductsList()
Dim A, M, T&, Ta&, K&
Dim Dic1 As Object
Application.ScreenUpdating = False
A = Sheets("Sheet 1").Range("A1").CurrentRegion.Offset(1, 0).Resize(, 2)
Set Dic1 = CreateObject("Scripting.dictionary")
With Dic1
For T = 1 To UBound(A, 1) - 1
If .exists(A(T, 1)) Then
.Item(A(T, 1)) = .Item(A(T, 1)) & "," & A(T, 2)
Else
.Item(A(T, 1)) = A(T, 2)
End If
Next T
K = .Count
End With

With Sheets("Sheet 1").Range("K1")
.CurrentRegion.Clear
.Value = "Branches"
.Offset(1, 0).Resize(K, 1) = WorksheetFunction.Transpose(Dic1.keys)
.Offset(0, 1).Resize(1, K) = Dic1.keys
For Ta = 0 To K - 1
M = Split(Dic1.Item(A(Ta + 1, 1)), ",")
If UBound(M) >= 0 Then .Offset(1, Ta + 1).Resize(UBound(M) + 1, 1) = WorksheetFunction.Transpose(M)
M = ""
Next Ta
End With

Application.ScreenUpdating = True
End Sub

 

[dsubash]

Your file does not contain any data. I have asked you to keep the required data.
Anyway with reference to data in Post#1 Here is the VBA code for macro "GetBranchProductsList".
Remove all formulas From column K and on words in Sheet 1. Run the Macro to get list in Sheet 1.
For DV use formulas below for sheet 2.
For A2

=OFFSET('Sheet 1'!$K$2,0,0,COUNTA('Sheet 1'!$K$2:$K$1000))

For B2

=OFFSET('Sheet 1'!$K$2,0,MATCH($A2,'Sheet 1'!$L$1:$DZ$1,0),COUNTA(INDEX('Sheet 1'!$L$2:$DZ$1000,,MATCH($A2,'Sheet 1'!$L$1:$DZ$1,0))))

Max Rows I have taken is 1000. if required Change it.
VBA code;

Sub GetBranchProductsList()
Dim A, M, T&, Ta&, K&
Dim Dic1 As Object
Application.ScreenUpdating = False
A = Sheets("Sheet 1").Range("A1").CurrentRegion.Offset(1, 0).Resize(, 2)
Set Dic1 = CreateObject("Scripting.dictionary")
With Dic1
For T = 1 To UBound(A, 1) - 1
If .exists(A(T, 1)) Then
.Item(A(T, 1)) = .Item(A(T, 1)) & "," & A(T, 2)
Else
.Item(A(T, 1)) = A(T, 2)
End If
Next T
K = .Count
End With

With Sheets("Sheet 1").Range("K1")
.CurrentRegion.Clear
.Value = "Branches"
.Offset(1, 0).Resize(K, 1) = WorksheetFunction.Transpose(Dic1.keys)
.Offset(0, 1).Resize(1, K) = Dic1.keys
For Ta = 0 To K - 1
M = Split(Dic1.Item(A(Ta + 1, 1)), ",")
If UBound(M) >= 0 Then .Offset(1, Ta + 1).Resize(UBound(M) + 1, 1) = WorksheetFunction.Transpose(M)
M = ""
Next Ta
End With

Application.ScreenUpdating = True
End Sub

sIR,

Applied your code, but the names are split into different rows and hence, the DV in the next page doesn't work properly. given below is the screenshot of the same.

Referrer Due Statement - Jan 2025.xlsm.xlsm
	O	P
1	Branches	Tindivanam
2	Tindivanam	JIPMER HOSPITALS
3		Self
4		Self
5		RAMACHANDRA HOSPITAL
6		ARCHANA HOSPITAL
7		DR.R.PALANIRAMAN
8		MBBS.
9		DCH.
10		MRCPCH(UK).
11
12		Self
13		DR.R.PALANIRAMAN
14		MBBS.
15		DCH.
16		MRCPCH(UK).
17
18		MUTHU CLINICAL LABORATORY
19		DR.R.PALANIRAMAN
20		MBBS.
21		DCH.
22		MRCPCH(UK).
23
24		J.FRANKLIN SELVARAJ. MBBS.
25		C.DIAB.
26
27		DINESH JOTHIMANI
28		DINESH JOTHIMANI
Sheet1

Also, duplicate names are found.