Dependant Data Validation not sorted

[Guest]

Hi
I have a worksheet that contains legacy data that is being cleaned to
conform to specific formats and conventions. I have another worksheet that
uses this cleaned data to build a new database row. I want to put a drop
down list in my database row worksheet that lists only the cleaned data.

For Example
Column A is the data to be cleaned
Column B contains a formula that tells the end user that the data is OK or
what about the data needs to be cleaned.

ColA ColB
AA12 OK
aa13 Clean Letters
BB03 OK
CC4 Clean Numbers

So my drop down list must contain the content of ColA where the content of
ColB = OK i.e. AA12 and BB03.

I've tried using Debra Daglish's Dependant Drop Down Sorted Lists solution (
http://www.contextures.com/xlDataVal13.html ) with the formula changed so
that the column goes to the left by 1, but as you can see, ColA has the sort.
I get AA12 and aa13 in my drop down list.

I have considered using Debra Daglish's Create Dependant Lists solution
using the INDIRECT function, but if I understand it, every distinct
occurrence in ColB's would have to have it's own named range. I can see that
solution getting rather cumbersome.

Can anyone suggest a solution?

TIA

 

[Roger Govier]

Hi Sara

You don't need Data Validation for a drop down list in this scenario.
You need Autofilter.
Highlight cells A1 and B1, Data>Filter>Autofilter
Choose the dropdown on column B and select OK
This will give a filtered list of the rows where column A data is
correct.

If required, you can mark the filtered range of Rows, Copy and then
Paste to another location.

 

[Guest]

Thanks for the input Roger, but that's not going to work. The corrected data
becomes just one column in as a database row. The corrected data will not be
in the same order or even frequency.

The database worksheet will be more like this:
ColA ColB ColC ColD ColE... etc
Various data BB03 other cleansed data lists
Various data BB03 other cleansed data lists
Various data AA12 other cleansed data lists
Various data BB03 other cleansed data lists
etc...

 

[Roger Govier]

Hi Sara

I had mis-read your question, and thought you were trying to copy the
clean ("OK") rows from your sheet to another location.

You should be able to use Debra's technique, but your columns are
effectively reversed from Debra's layout in her sample file. Either move
your column on your Data so that the O.K.'s appear in column A, rather
than B, or change the validation for your drop down to the equivalent of

=OFFSET(RegionStart,MATCH(B4,RegionColumn,0)-1,0,COUNTIF(RegionColumn,B4),1)
having re-defined Region Start to $B$1 instead of $A$1

(Note. The above is what you would do if you moved column B on
Validation Lists of Debra's file, to column A, which is the effectively
the same situation as you have)

Hopefully from the above, you can work out what you need to do with your
file.