Data Validation: Remove only one entry of multiple entries in list

**elmerg** · 09-13-2013, 02:08 PM

Hey all,

I've searched and couldn't really find anything that was related to what I am looking for. I am writing a tracker for a game and am using drop-down lists to choose stats. One set of stats has multiples of the same number in it, however. I am using info found in a link from here, to create the dropdown menu and have the entry be removed.

When I choose a number that has multiples, however, removes all the entries rather than just one. Is there a way to make it only remove one of the entries? Here's what I'm using:

Formula:



Column A has numbers 4, 3, 3, 2, 2, 2, 1, 1, 1, 1 which are the master list contents.



Column B has a formula to generate a number to tell whether or not that number has been used in that range. That formula is:



=IF(COUNTIF('Character Tracker v1.0'!$B$25:$B$50,A12)>=1,"",ROW()) 



Column C has a formula to remove used entries from the list. Column C itself is the list source. That formula is: 



=IF(ROW(A7)-ROW(A$7)+1>COUNT(B$7:B$16),"", INDEX(A:A,SMALL(B$7:B$16,1+ROW(A7)-ROW(A$7))))

Any help would be appreciated. I just can't figure out what to do, since I'm sure I have to alter the formula in Column B but I'm not sure how.

Thanks in advance!

**protonLeah** · 09-13-2013, 02:27 PM

To attach a Workbook
(please do not post pictures of worksheets)

Click Advanced (next to quick post),
Scroll down until you see "Manage Attachments",
Click that then select "add files" (top right corner).
Click "Select Files" find your file, click "open" click "upload"
Once the upload is completed the file name will appear below the input boxes in this window.
Click "Done" at bottom right to close the Attachment Manager.
Click "Submit Reply"

**elmerg** · 09-13-2013, 04:45 PM

Let's try this again. Attached is the workbook I'm working in. The two sheets in question are:

Character Tracker v1.0
Data

On the sheet Character Tracker, halfway down is a list of skills. In column B there is a dropdown list via data validation with a series of numbers. The numbers are repeated due to being starting points that can be assigned to a skill.

On the sheet Data, starting with A7 is the list of all the starting points; B7 is the formula to determine whether an entry has been used; and C7 through C16 is the revised dropdown list for choosing numbers.

The problem I'm having is when I choose a number with multiples, it removes all of that, not just a single one. So if I choose a 3, both 3s disappear rather than only one. I would definitely appreciate help remedying this problem.

Thanks in advaance!