Hiding duplicates from drop down lists

Is this what you had in mind? Happy to explain it if it is. There is no point in doing so until I really understand what you want!!

Glenn beat me to this (I went to lunch before posting!). My solution is very similar to his, except that I use two Named Ranges - one for the items already chosen and one for the unchosen items - and a different formula to achieve the same results. As he says above, if this is roughly what you want, then we can explain the details.

Screenshots are useless.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Glenn Kennedy,

Yes I will send you a shortened version of my list. My full list range is in the 1000 mark. For inserting items to my list at first you need to select a group in which items are stored, then you get their inventory numbers. I need that inventory numbers to not get duplicated. I narrowed this file to have only 2 item groups: "Motorinė" and "Elektriniai". In Ctrl+F3 you will see the same names as these two groups.

The key to whether or not this is do-able is the number of possible item Groups. Currently you are showing 2 (motorine and Elektrikinai). Each item group will generate two additional columns. So if you have a small number of these, it's easy. If you have many, it becomes VERY tedious... or impossible. So how many item groups are there?

I have 9 groups in total.

That's do-able, but it will take you about 30 mins to set it up. I sweated blood over this. It was easy enough to do what you wanted, but making the named ranges dynamic... so that all those empty rows DO NOT show up on your dropdowns took a little (!!) time.

1. Insert new rows - now F&G for Motorine and L&M for Eleektikinai. make sure that G and M and others are labelled Motorine, etc...

2. In F3, copied down:
=IF(D3="","",COUNTIF('item List'!$E$5:$E$130,Inventory!D3))
You will see from this, that I have allowed the item list column E to go down to row 130. Adjust if needed. This identifies those values that have already been selected in Item List Column E.

3. In G3, copied down:
=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(D$3:D$220)/(F$3:F$220=0),ROWS(G$3:G3))),"")
You will see from this, that I have allowed the Inventory column D to go down to row 220. Adjust if needed. This returns ONLY those items that have NOT already been used in Item List column E.

4. Clear the data validation from Inventory E5 to E130.

5. Set a Named Range (DD_Range) using this formula:
=OFFSET(INDEX(Inventory!$C$3:$M$3,,MATCH('Item List'!$D4,Inventory!$C$2:$M$2,0)),,,SUMPRODUCT(--(LEN(INDEX(Inventory!$C$3:$M$3,,MATCH('Item List'!$D4,Inventory!$C$2:$M$2,0)):INDEX(Inventory!$C$3:$M$220,218,MATCH('Item List'!$D4,Inventory!$C$2:$M$2,0)))>0)))

This was the one that took some time to get right!! Basically it identifies the range in Inventory, rows 5:220, that correspond to the value present in the corresponding row in column D of Item List (currently either Motorkine or Elektrakini). It then returns ONLY that part of the range that contains non-blank cells. In your previous DD lists, not only could you select duplicates, but there were dozens of blank rows. This formula is fully dynamic. The 218 in red corresponds to the maximum possible number of items in each list (starting from row 3 and ending in 220). If you cut the range back from 220, you will need to adjust this, too.

6. Set data validation in E5:E130 of Item List, using this:
Allow: List
Source: =DD_Range

7. To extend, simply copy/paste F2:G3 of inventory into the corresponding positions for the remaining Item groups.

I hope that this is what you want. I think that I haven't seen this approach working previously without VBA... which I know nothing about.

It looks good with your workbook, but unfortunately i can't gat it right on mine, I just do everything you instructed me and i get that DD_Range source evaluates to an error, I even copied all of my inventory list rows and then pasted on the workbook that you made, changed the DD_Range formula range from C2:M2 to C2:AW2 (thats my full inventory range) and it still doesn't work...

Unless I see your sheet, I cannot tell what's wrong.

Here's my sheet.

It was not working for Motorine becasue Inventory G2 did not have the accent on the final E. Fixed.

The same problem for Zenklai and Savaeige Both fixed.

It is (still) not working for Nuomuojami, as it is not included in the range out to AC2!!

Nuomuojami doesn't need that formula it can have duplicates. May I ask you maybe you sent me a wrong file???? because this one let's inventory items be duplicated..error ss.png

In the Used column in Inventory, most of the formulae in row 3 were pointing to the code number in row 5. They are now all pointing to row 3 and it works. You will need to add Nuomuojami to the range, too. However, don't use the same formulae. Just copy/paste the values from the inventory number column into the light pink colum and make sure the spelling of Nuomuojami is correct.

Everything works fine now. Thank you Glenn for helping solve this problem!

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.