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]

Have you tried Post#29.
I got correct result
NOTE; I had already told you to use Worksheetevent for Sheet 2, not for Sheet 1. code should be removed from Sheet 1. Code is there in POst#24.

 

[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)),"*?"))

 

[dsubash]

Sir I tried your formula in a different sheet. The formula is working fine, but the issue is not all the details are displayed,
for example. I have two Item names for the Instrument ABG (Col. X), but only one instrument is displayed in Col AL. But for all other intruments from Cols AI to BA the item names are properly displayed.

I am unable to find the issue. Can you please help me out to sort this issue.

I have attached the original file link for your reference.

Dropbox

www.dropbox.com

Thanks in Advance
Subash D

 

[kvsrinivasamurthy]

Change the formula in AH2 as below a nd copy to full required range.

=IF(AH$1="","",IFERROR(INDEX($Y$2:$Y$477,AGGREGATE(15,6,ROW($Y$2:$Y$477)/(ISERROR(MATCH($Y$2:$Y$477,AH$1:AH1,0))*($X$2:$X$477=AH$1)),1)-ROW($Y$1)),""))

 

[dsubash]

Change the formula in AH2 as below a nd copy to full required range.

=IF(AH$1="","",IFERROR(INDEX($Y$2:$Y$477,AGGREGATE(15,6,ROW($Y$2:$Y$477)/(ISERROR(MATCH($Y$2:$Y$477,AH$1:AH1,0))*($X$2:$X$477=AH$1)),1)-ROW($Y$1)),""))

Thanks for the speedy reply, it works fine now.

I also have another query. I need to upload this sheet to google sheets. will these formulas work in google sheets? I also have link of this sheet to another sheet in the same workbook which is to be uploaded to google sheets.

Thanks
Subash D

 

[kvsrinivasamurthy]

In Column X data is arranged in sorted form. If it is always in the same form in the file a better and faster formula can be done. Is it Ok .

 

[kvsrinivasamurthy]

I don't know about google sheets. Aggregate function is not there in google sheets.

 

[kvsrinivasamurthy]

For google sheets.
In AG2.

=UNIQUE(X2:X206)

In AH2 copied upto BZ2. If required extend further.

=IF(AH$1="","",unique(filter($Y$2:$Y$206,$X$2:$X$206=AH$1)))

 

[dsubash]

Sorry for the delayed reply.

Your Unique formula did not return the required results, but got this formula from another source which worked well in google sheets. this formula used "Agrregate" instead of unique and filter

=IF(AH$1="","",IFERROR(INDEX($Y$2:$Y$477,AGGREGATE(15,6,ROW($Y$2:$Y$477)/(ISERROR(MATCH($Y$2:$Y$477,$AH$1:$AH1,0))*($X$2:$X$477=AH$1)),ROWS(AH$2:AH2))-ROW($Y$1)),""))