Automatically finding relevant data and putting into drop down list

[chizz0320]

Good morning everyone.

I need some urgent help on this, and I've attached a sample spreadsheet to illustrate my point.

On my document, there are two sheets, A and B. I have used Sheet A A21:A24 as examples. What I need to do is this:

1. Identify the last numbers in the entry - in this case, 1120
2. Go to Sheet B, and look for entries that are also ending in 1120 - in this case, C1:C3
3. Also note the value in column E - in this case, E1:E3
4. Go back to Sheet A, and create two drop down menus for each of those entries found in Step 1, as shown in the sample file

Trickier entries might be A256:A258, where there are five numbers in the end instead of four, namely 11771, 11772, and 11773.

All of this should be automated as much as possible, since there are about 5000 entries. I assume that scripts/macros/VBA will have to be used and I have no problem with that other than that I don't have much knowledge in that field.

This is quite urgent and any help would be greatly appreciated. Please let me know if you need anymore information on this.

Meanwhile, I'll go back to manually doing this... I've done 200 so far?

Thanks people!

[arlu1201]

If i understand your format correctly, there will be only 4 or 5 digits at the end right? Any chances of any other number of digits?

[chizz0320]

Hi Arlette,

Thanks for the quick response! Most of them are of that format, but there are a few entries that look something like this

4144VSHH3256A
4144VI3256A

For these ones, 3256 will be used for matching.

If this criteria makes it more complex, please don't worry about it. I will go through the document once afterwards anyway so I can always manually change it.

Thanks so much!

[chizz0320]

Do any of you know of a way? Please help and let me know! Thanks!

[chizz0320]

Bump...

[arlu1201]

Will work something out. Its a lil complicated but can be done.

[arlu1201]

What if values (last 4 digits) from sheet A are not found in sheet B?

[chizz0320]

What if values (last 4 digits) from sheet A are not found in sheet B?

Thanks for helping Arlette!

If a value is not found, then skip the row in sheet A altogether (or just have it return #N/A) and restart from the next row.