Removing Duplicates from Dropdown List

Long story short! I work for a small electronics company and the previous engineer locked our files and recently passed away from a heart-attack, I'm having to reconstruct our spreadsheets. I have no knowledge of excel and i have been fumbling my way through it. Any help would be very much appreciated! So, Thank you in advance.

What i have: i have B1 on the FormTable sheet set as a dropdown box to select an item from Column B on the RoutingTable sheet.

What im looking for: I need to remove duplicates out of the dropdown box. After selecting from the dropdown box in B1 on the FormTable Sheet, i need Column B5 and C5 on the FormTable Sheet to match the data on the RoutingTable's corresponding cells.
Also, i need exact returns such as... Loc 1 matches Loc 1, if there is no Loc match then leave it blank and move on to the next match.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I will attempt to do it for you this time.)

Are you really using Excel 2007?

Originally Posted by bashscript

yes ma'am i would, but unfortunately as you can see if you read my post.. that i do not have enough knowledge of excel to properly title my post by what function i need help with. Sorry for the inconvenience and if you further insist.. perhaps you can help me accurately title this post by reading it and telling me what you think it should be titled based on my needs.

Did you read my post in its entirety?

OK. So, a quick question: the list that feeds the drop-down - will this ever change and/or grow?

OK. I'm about to log off for the evening, but if nobody else chimes in, I'll have another look in the morning. It's a shame you don't have a more recent version of Excel.

Okay, so when you pick, for example, USB Outlet from your dropdown box, Excel won't know which "USB Outlet" you've chosen, so you've narrowed your choices down to, for Wire #
USB PWR
USB GND
USB DATA+
USB DATA-
Do you want those 4 values to appear in B5 to B8?

That is correct. When i select USB INPUT, i need B5 and on to popluate with the correct wire based on the Loc to the left. Any result that doesnt have an LOC should be skipped and left blank.

sorry for the late reply... i was on lunch break. Thank you for responding!

So, let's say i picked USB INPUT and it has a return for 4 wires that are not in ascending or descending order. Like Loc of 2, 4, 6, and 8. i would need loc 1, 3, 5,7 to be skipped while the rows on 2, 4, 6, 8 being populated with the correct wires.

First, to address your title and removing duplicates from the dropdown
You need to create a new list without duplicates and reference that. Easiest way is to copy and paste the list somewhere else and then use Excel's "Remove Duplicates" functionality (on the Data Tab). I think Excel 2007 has this.
However, since your data is expanding, we can do this with formulas.
In F2 of RoutingTable tab, I entered this formula copied down

Formula:

Please Login or Register  to view this content.

You can hide this column if you like for esthetics.

Now to fill in Col C of FormTable
I used this Arrayed Formula copied down

Formula:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

See attachment

I'm here all week.

Okay, in this workbook, you don't have the dropdown (I'm looking at "Form Board Tables"). Instead you have a table for each "From Conn". Is that the way you are going to go? So you want each table filled in as per the previous request?

Okay, similar to post #14. In U2 copied down on "Routing Chart"

Formula:

Please Login or Register  to view this content.

Then I created a dynamic named range called "MyList"

Formula:

Please Login or Register  to view this content.

I used MyList in the Data Validation > List to create the dropdown menus. (I just did the one in cell A1)
Lastly, in Form Board Tables B2 copied down, this Arrayed formula

Formula:

Please Login or Register  to view this content.

See attachment.
Any questions?

renamed the file to test008. it crashed when changing the data in column B, then i opened the file again.. changed some data in the same column with no crash, but when i went to change update the list on column U2 of the Routing Chart.. it crashed again.

also there are some error messages popping up when opening the file.
Attachment 727285Attachment 727287

Crashed when deleting merged cell A40 on the Routing Chart Sheet

Not sure what that Attachment Error is. I'll ask around. The list in Col U is automated based on what's in B. What were you trying to change in Column U?
Your data is not that large. It shouldn't crash.

I had dragged the formula down to U21 so there's room for up to 12 new unique entries before you'd need to modify it. I cannot reproduce the crashing. I posted a request for someone else to assist. Hopefully someone has an answer.

i did a computer restart and the crash has not come back... however.. the U column is not updating with the new entries into column B. is there something that i should be doing that i am not?

[update] it crashed again.. this time i dont know why.. i will be testing it on another workstation here in a few minutes.

[update] had a co-worker try it on their station and when adding rows to the Routing Chart; it crashes every time. Perhaps, there is some bit of code here conflicting with our version of excel?

I added "New Type" into Routing Chart B39 and it automatically appeared in U10. I assume your calculations are set to "automatic". Try clicking F9 which will force a calculation.

yeah, something must be conflicting between our versions.. F9 didnt update anything and when i selected Column U and dragged the formula down from U3 to U20 it crashed.

This time i was able to add Rows to the Routing Chart without a crash. Entered data into the new fields without a crash.. but when i did what i stated above it crashed.


[troubleshoot update]
opened the file and saved it with no changes = no crash
tried saving after adding rows in the middle of the table with no data = crash
tried saving after adding rows to the end of the table with no data = crash
tried saving after adding rows in the middle of the table with data = crash
tried saving after adding rows to the end of the table with data = crash.

[update] Set my calculations to manual.. Column U updated with F9, but crashed at save after adding new row to the bottom of the routing chart and entering the new data.
[update] Tried selecting my new entry from the dropdown box and after the selection, it crashed. I think something funky is going on with column U??

Time to update???

Glad to help. Good luck with your updates.

I am not sure what you expect the results to be. I modified the formula in B5 to read

Formula:

Please Login or Register  to view this content.

This is an arrayed function and needs to be entered with CNTRL SHFT ENTER. It looks in both B and F of Routing Chart for matches to A1. It returns only 1 and 3 as "F1-2" and "F3-2" aren't listed in your locations col A. As I have no idea what any of this means in practicality, I can't really know what is expected or why you list certain locations and not others.

Sorry for not being more descriptive... F1-2 and F3-2 are Fuse locations.. they are normally inputted manually on the Form Board Table.
What we are attempting to accomplish with all this is, for example: We Select our "From Connector" in the drop down list, which then populates the Wires that come from that Connector and applies them to the appropriate Pin locations on the selected Connector.

Sometimes, a company will input things backwards which is why i needed the F Column "To Connector" to also show in the drop down list.

Okay, so does my formula fix the issue? IF not, what do you expect to populate?