dsubash
New Member
- Joined
- Nov 22, 2024
- Messages
- 13
- Office Version
- 2019
- Prefer Not To Say
- Platform
- Windows
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
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
