how to make a list to avoid repetitive texts

Before anything else, I want to say that I am beginner level in VBA so I hope somebody could help me figuring out the best solution on this.

Background:
This will be a database of problems occurring in the company i am working with and we want to have a database to find the contact person easier depending on the problem (see column D and E)

My Problem:
Data are the same in every group (countries in every region), I want it to be user friendly and not make the database very long because if I will continue copy - paste the same texts, I would probably be using 5000 rows

Hi Elainefish,

Could you be more specific about on what you need help?

Cheers

The attachment I did will be the source of data. What I want to do is to make it simpler so it will be user friendly because as you can see the all the texts I am using are just the same. I tried to make it as a drop down list but the problem is the result... The result should be like using "automatic filter" when you choose a text in column A, all the texts that corresponds the column A will show up in the remaining column, same as if I choose a text in the drop down in column B and so on... column C, D, E... until I get the answer in column F (contact person). The problem with the auto filter is that I have to use the same texts all the time in every row (see column E).. that is what I am trying to figure iut... simplifying the column E so it will not be a long list since my list of data are up to 1000+.

Hi there,
I've come up with the attached workbook; let me introduce my work.
As you asked, I have eliminated the auto filter.

In this workbook, you'll see 3 sheets.
-On the Sheet named 'Ask', the user should input the required fields (problem1, problem2, problem3, region, and country)
-I have used Data Validation to limit the values that can be entered to these cells. The lists of data validation can be found on the sheet named "Validation". Note that on the country part, I have put a conditional validation; for instance, if you choose Europe from as a region, than only european countries can be chosen..
(You can adjust those validation ranges as you like)
-The heart of all the work lies at the sheet named "Database". Notice that on this sheet, I have unmerged all cells; this is required to make the lookup function work. Next, on this sheet, you'll see a new column I added, with the header "Concatenate" (colored yellow).

So as you'll see in the formulas of the 'Ask' Sheet, the vlookup looks up the concatenated values from this range, and it gives you the contact person. Note that I've also added multiple if criteria, such that the lookup function will lookup the values only when all the required fields are entered. I've introduced this to eliminate unnecessary fields.

To make it user friendly, I suggest you to hide the 'Database' and 'Validation' sheets. Introducing workbook protection will also prevent users from accidentally deleting these important sheets. You can also hide the concatenate column on the ask sheet.


I hope this solves your problem

Cheers,

Do you have idea how to make the third column? The third column is dependent on list from column A and B