Hi,

I have a dynamic drop down validation List, which comes from another
spreadsheet list and have a few random blank cells in it. I need to
drop these blank values from the List without making any change in the
parent list. I may also have some duplicate values in the List and need
to drop them too.

Please help.

Thanks,

Shail

"shail" skrev i en meddelelse
oups.com...
Hi,

I have a dynamic drop down validation List, which comes from another
spreadsheet list and have a few random blank cells in it. I need to
drop these blank values from the List without making any change in the
parent list. I may also have some duplicate values in the List and need
to drop them too.

Please help.

Thanks,

Shail


Hi Shail

Here's one way to do it:

Assumptions:
Name of the range of your current validation list: OldList

1. Make a new list (named NewList), with a number of cells
which will never be reached in ordinary use. I have made
the list in C3:C24.
2. C2 must be empty or must not contain data found in OldList.
The list cannot start in row 1 with the formula used.
3. In C3 enter this array formula:

=INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1)

The formula must be entered with <Shift<Ctrl<Enter,
also if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in braces {}. Don't enter these
yourself. It's Excel's way of telling, that the formula is an
array formula.

4. Copy C3 down to C4:C24 with the fill handle (the little
square in the lower right corner of the cell).

NewList will contain all elements from OldList except
duplicates and empty cells. The rest of the range is
filled with #VALUE!

NewList will be used as your new validation list.

Enter this formula in the validation source field:

=OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0))

The validation box will now contain, what you were looking for.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Hi Leo,

It worked just perfectly.

Many thanks to you.

Shail


Leo Heuser wrote:
"shail" skrev i en meddelelse
oups.com...
Hi,

I have a dynamic drop down validation List, which comes from another
spreadsheet list and have a few random blank cells in it. I need to
drop these blank values from the List without making any change in the
parent list. I may also have some duplicate values in the List and need
to drop them too.

Please help.

Thanks,

Shail


Hi Shail

Here's one way to do it:

Assumptions:
Name of the range of your current validation list: OldList

1. Make a new list (named NewList), with a number of cells
which will never be reached in ordinary use. I have made
the list in C3:C24.
2. C2 must be empty or must not contain data found in OldList.
The list cannot start in row 1 with the formula used.
3. In C3 enter this array formula:

=INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1)

The formula must be entered with <Shift<Ctrl<Enter,
also if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in braces {}. Don't enter these
yourself. It's Excel's way of telling, that the formula is an
array formula.

4. Copy C3 down to C4:C24 with the fill handle (the little
square in the lower right corner of the cell).

NewList will contain all elements from OldList except
duplicates and empty cells. The rest of the range is
filled with #VALUE!

NewList will be used as your new validation list.

Enter this formula in the validation source field:

=OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0))

The validation box will now contain, what you were looking for.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

You're welcome, Shail, and thanks for the feedback :-)

Leo Heuser



"shail" skrev i en meddelelse
oups.com...
Hi Leo,

It worked just perfectly.

Many thanks to you.

Shail


Leo Heuser wrote:
"shail" skrev i en meddelelse
oups.com...
Hi,

I have a dynamic drop down validation List, which comes from another
spreadsheet list and have a few random blank cells in it. I need to
drop these blank values from the List without making any change in the
parent list. I may also have some duplicate values in the List and need
to drop them too.

Please help.

Thanks,

Shail


Hi Shail

Here's one way to do it:

Assumptions:
Name of the range of your current validation list: OldList

1. Make a new list (named NewList), with a number of cells
which will never be reached in ordinary use. I have made
the list in C3:C24.
2. C2 must be empty or must not contain data found in OldList.
The list cannot start in row 1 with the formula used.
3. In C3 enter this array formula:

=INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1)

The formula must be entered with <Shift<Ctrl<Enter,
also if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in braces {}. Don't enter these
yourself. It's Excel's way of telling, that the formula is an
array formula.

4. Copy C3 down to C4:C24 with the fill handle (the little
square in the lower right corner of the cell).

NewList will contain all elements from OldList except
duplicates and empty cells. The rest of the range is
filled with #VALUE!

NewList will be used as your new validation list.

Enter this formula in the validation source field:

=OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0))

The validation box will now contain, what you were looking for.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Hi Leo,
That was a great technique. Could the dropdown listed in alphabetical
order?

Thanks in advance,
Ricky



Leo Heuser Wrote:
You're welcome, Shail, and thanks for the feedback :-)

Leo Heuser



"shail" skrev i en meddelelse
oups.com...
Hi Leo,

It worked just perfectly.

Many thanks to you.

Shail


Leo Heuser wrote:
"shail" skrev i en meddelelse
oups.com...
Hi,

I have a dynamic drop down validation List, which comes from
another
spreadsheet list and have a few random blank cells in it. I need
to
drop these blank values from the List without making any change in
the
parent list. I may also have some duplicate values in the List and
need
to drop them too.

Please help.

Thanks,

Shail


Hi Shail

Here's one way to do it:

Assumptions:
Name of the range of your current validation list: OldList

1. Make a new list (named NewList), with a number of cells
which will never be reached in ordinary use. I have made
the list in C3:C24.
2. C2 must be empty or must not contain data found in OldList.
The list cannot start in row 1 with the formula used.
3. In C3 enter this array formula:


=INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1)

The formula must be entered with <Shift<Ctrl<Enter,
also if edited later. If done correctly, Excel will display the
formula
in the formula bar enclosed in braces {}. Don't enter these
yourself. It's Excel's way of telling, that the formula is an
array formula.

4. Copy C3 down to C4:C24 with the fill handle (the little
square in the lower right corner of the cell).

NewList will contain all elements from OldList except
duplicates and empty cells. The rest of the range is
filled with #VALUE!

NewList will be used as your new validation list.

Enter this formula in the validation source field:

=OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0))

The validation box will now contain, what you were looking for.

--
Best regards
Leo Heuser

Followup to newsgroup only please.



--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059
View this thread: http://www.excelforum.com/showthread...hreadid=564718

Hi Ricky

I have made a complete description including how to make
the list sorted with formulae only.

Please see below.


"ExcelQuestion"
skrev i en meddelelse
news:ExcelQuestion.2cjut6_1155591912.4299@excelfor um-nospam.com...

Hi Leo,
That was a great technique. Could the dropdown listed in alphabetical
order?

Thanks in advance,
Ricky


Leo Heuser wrote:
"shail" skrev i en meddelelse
oups.com...
Hi,

I have a dynamic drop down validation List, which comes from
another
spreadsheet list and have a few random blank cells in it. I need
to
drop these blank values from the List without making any change in
the
parent list. I may also have some duplicate values in the List and
need
to drop them too.

Please help.

Thanks,

Shail



Here's one way to do it:

Assumptions:
Name of the range of your current validation list: OldList

1. Make a new list (named NewList), with a number of cells,
which will never be reached under normal conditions.
I have made the list in C3:C24.
2. C2 must be empty or must not contain data found in OldList.
The list cannot start in row 1 with the formula used.
3. In C3 enter this array formula:

=INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1)

The formula must be entered with <Shift<Ctrl<Enter,
also if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in braces {}. Don't enter these
yourself. It's Excel's way of telling, that the formula is an
array formula.

4. Copy C3 down to C4:C24 with the fill handle (the little
square in the lower right corner of the cell).

NewList contains all elements from OldList except
duplicates and empty cells. The rest of the range is
filled with #VALUE!


To get the new list in alphabetical order use this setup:

5. Make another list (named SortList), which has the same size as
NewList.
I have made it in E3:E24.
6. In E3 enter this array formula:

=INDEX(NewList,MATCH(LARGE(MMULT(IF(ISERROR(NewLis t<=TRANSPOSE(NewList)),0,IF(NewList<=TRANSPOSE(New List),1,0)),ROW(NewList)^0),ROW()-ROW($E$3)+1),MMULT(IF(ISERROR(NewList<=TRANSPOSE(N ewList)),0,IF(NewList<=TRANSPOSE(NewList),1,0)),RO W(NewList)^0),0))

The formula must be entered with <Shift<Ctrl<Enter

7. Copy E3 down to E4:E24 with the fill handle.

SortList contains NewList sorted.


To use NewList as validation source enter this formula
in the validation source field:

=OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0))

To use SortList as validation source enter this formula
in the validation source field:

=OFFSET(SortList,,,SUM(NOT(ISERROR(SortList))+0))

Regards
Leo Heuser

Hi Leo,

Thanks again for the fantastic post. I was thinking of this but I
didn't asked as I thought sorting couldn't be possible using a
formulae.

thanks

Shail



Leo Heuser wrote:
Hi Ricky

I have made a complete description including how to make
the list sorted with formulae only.

Please see below.


"ExcelQuestion"
skrev i en meddelelse
news:ExcelQuestion.2cjut6_1155591912.4299@excelfor um-nospam.com...

Hi Leo,
That was a great technique. Could the dropdown listed in alphabetical
order?

Thanks in advance,
Ricky


Leo Heuser wrote:
"shail" skrev i en meddelelse
oups.com...
Hi,

I have a dynamic drop down validation List, which comes from
another
spreadsheet list and have a few random blank cells in it. I need
to
drop these blank values from the List without making any change in
the
parent list. I may also have some duplicate values in the List and
need
to drop them too.

Please help.

Thanks,

Shail



Here's one way to do it:

Assumptions:
Name of the range of your current validation list: OldList

1. Make a new list (named NewList), with a number of cells,
which will never be reached under normal conditions.
I have made the list in C3:C24.
2. C2 must be empty or must not contain data found in OldList.
The list cannot start in row 1 with the formula used.
3. In C3 enter this array formula:

=INDEX(OldList,MIN(IF((COUNTIF($C$2:C2,OldList)=0) *(OldList<""),ROW(OldList)))-MIN(ROW(OldList))+1)

The formula must be entered with <Shift<Ctrl<Enter,
also if edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in braces {}. Don't enter these
yourself. It's Excel's way of telling, that the formula is an
array formula.

4. Copy C3 down to C4:C24 with the fill handle (the little
square in the lower right corner of the cell).

NewList contains all elements from OldList except
duplicates and empty cells. The rest of the range is
filled with #VALUE!


To get the new list in alphabetical order use this setup:

5. Make another list (named SortList), which has the same size as
NewList.
I have made it in E3:E24.
6. In E3 enter this array formula:

=INDEX(NewList,MATCH(LARGE(MMULT(IF(ISERROR(NewLis t<=TRANSPOSE(NewList)),0,IF(NewList<=TRANSPOSE(New List),1,0)),ROW(NewList)^0),ROW()-ROW($E$3)+1),MMULT(IF(ISERROR(NewList<=TRANSPOSE(N ewList)),0,IF(NewList<=TRANSPOSE(NewList),1,0)),RO W(NewList)^0),0))

The formula must be entered with <Shift<Ctrl<Enter

7. Copy E3 down to E4:E24 with the fill handle.

SortList contains NewList sorted.


To use NewList as validation source enter this formula
in the validation source field:

=OFFSET(NewList,,,SUM(NOT(ISERROR(NewList))+0))

To use SortList as validation source enter this formula
in the validation source field:

=OFFSET(SortList,,,SUM(NOT(ISERROR(SortList))+0))

Regards
Leo Heuser

"shail" skrev i en meddelelse
oups.com...
Hi Leo,

Thanks again for the fantastic post. I was thinking of this but I
didn't asked as I thought sorting couldn't be possible using a
formulae.

thanks

Shail


Hi Shail

Again you're welcome, and thank you for your feedback :-)

Actually, I also thought, it wasn't possible to do a sort with
formulae only, but then I suddenly saw the light ;-)

Regards
Leo Heuser

Hi Leo,

If you please describe me how these formulas are working. I tried it
myself but not getting to the point.

Thanks

Shail


Leo Heuser wrote:
"shail" skrev i en meddelelse
oups.com...
Hi Leo,

Thanks again for the fantastic post. I was thinking of this but I
didn't asked as I thought sorting couldn't be possible using a
formulae.

thanks

Shail


Hi Shail

Again you're welcome, and thank you for your feedback :-)

Actually, I also thought, it wasn't possible to do a sort with
formulae only, but then I suddenly saw the light ;-)

Regards
Leo Heuser

Hi Leo,
That was an excellent post. This know-how is going to help many,
certainly you have me helped greatly. Good work.

Thanks again,
Ricky


Leo Heuser Wrote:
"shail" skrev i en meddelelse
oups.com...
Hi Leo,

Thanks again for the fantastic post. I was thinking of this but I
didn't asked as I thought sorting couldn't be possible using a
formulae.

thanks

Shail


Hi Shail

Again you're welcome, and thank you for your feedback :-)

Actually, I also thought, it wasn't possible to do a sort with
formulae only, but then I suddenly saw the light ;-)

Regards
Leo Heuser


--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059
View this thread: http://www.excelforum.com/showthread...hreadid=564718

It is actually the most interesting and fantastic post I ever saw in
this group

thanks again

Shail

Leo Heuser wrote:
"shail" skrev i en meddelelse
oups.com...
Hi Leo,

Thanks again for the fantastic post. I was thinking of this but I
didn't asked as I thought sorting couldn't be possible using a
formulae.

thanks

Shail


Hi Shail

Again you're welcome, and thank you for your feedback :-)

Actually, I also thought, it wasn't possible to do a sort with
formulae only, but then I suddenly saw the light ;-)

Regards
Leo Heuser

"ExcelQuestion"
skrev i en meddelelse
news:ExcelQuestion.2crd6h_1155942309.7232@excelfor um-nospam.com...

Hi Leo,
That was an excellent post. This know-how is going to help many,
certainly you have me helped greatly. Good work.

Thanks again,
Ricky


Hi Ricky

You're welcome.
Thanks for your kind feedback!

Regards
Leo Heuser

"shail" skrev i en meddelelse
oups.com...
It is actually the most interesting and fantastic post I ever saw in
this group

thanks again

Shail


<Blush<Blush :-)

I'm working on a shorter version. When it's
ready, I'll be back and include a bit of explaining.

Regards
Leo Heuser

Ok, that will be really great and helpful

thanks again

Shail



Leo Heuser wrote:
"shail" skrev i en meddelelse
oups.com...
It is actually the most interesting and fantastic post I ever saw in
this group

thanks again

Shail


<Blush<Blush :-)

I'm working on a shorter version. When it's
ready, I'll be back and include a bit of explaining.

Regards
Leo Heuser

Hi Shail

I'm sorry, but I don't know, when I'll be finishing
the new formula, so don't spoil your nights sitting
and waiting for it <bg

To get some information about array formulae, try
this article by Bob Umlas:

http://www.emailoffice.com/excel/arrays-bobumlas.html

--
Best regards
Leo Heuser

Hi Leo,

Thanks for the post. Yes, I have been waiting for the new formula by
you everyday. Also trying to get how the formula posted by you is
working (and literally, I am scratching my hairs.. hahaha)

Thanks again.

Shail

Leo Heuser wrote:
Hi Shail

I'm sorry, but I don't know, when I'll be finishing
the new formula, so don't spoil your nights sitting
and waiting for it <bg

To get some information about array formulae, try
this article by Bob Umlas:

http://www.emailoffice.com/excel/arrays-bobumlas.html

--
Best regards
Leo Heuser