Index/Match Array for Data Validation- can't have both Text and Numbers in same Column

[lhogan]

Hi,

I am hoping to use Data Validation to create a drop-down list from a dynamic column that contains a mix of text cells and number cells. To make it more complicated, the list needs basically every convenience feature known to man (or that I can think of): it needs to be dynamic, alphabetized, ignore duplicates, and be able to deal with blanks (I don't ask for much do I?).

I've worked through a bunch of different permutations of my formula and have gotten to one that can do almost everything I need.

Assuming my data starts in C4 and will never get longer than C50 and is being relisted down from E4 (alphabetized and ignoring duplicates/blanks):

{=IFERROR(INDEX($C$4:$C$50,MATCH(0,COUNTIF($C$4:$C$50,"<"&$C$4:$C$50)-SUM(COUNTIF($C$4:$C$50,E$3:E3)),0)),"")}

I then take this list, give it a name in the Name Manager of 'Department2' via this formula:

=Sheet1!$E$4:INDEX(Sheet1!$E$4:$E$50,COUNT(Sheet1!$E$4:$E$50))

Get a dynamic Unique Count of my Department 2 list via:

=SUMPRODUCT((Department2<>"")/COUNTIF(Department2,Department2&""))
(this is put into cell G4)

And get the dynamic formula for the final Data Validation list using:

=$E$4:INDEX($E$4:$E$50,$G$4)
(again, E4:E50 is where my first formula dumped my list and G4 is where my dynamic Unique Count lives)

Call it Department3 and throw it into my Data Validation drop down and all is well and good.​

After all that- my problem is that my formulas, specifically the first one, cannot handle a column that contains both text (banana, apple, mouse) and straight numbers (1, 2, 754). I tried forcing the cells to format everything as text, but when I put in more than one number in a text column, the text stops coming over (If I take out the IFERROR, I get #N/As after the first cell).

I've honestly never used Excel in any capacity other than extremely basic graphs/charts before (seriously, I had never written a formula in it before this) and so it is very possible there is a much easier way to do what I am trying to do. I've also never posted here, so if there was protocol I failed to follow, please let me know and I will repost following it.

I would be extremely grateful if anyone can help me out with this. Even if you can't, I really appreciate that people read posts and try to help out - I would never have gotten even close without this forum.

Thanks,

LHogan

 

[Weazel]

Ok, so trying to follow with what you are trying to accomplish perhaps a modification to your first formula along the lines of

=IFERROR(INDEX($C$4:$C$50,MATCH(0,COUNTIF($E$3:E3,$C$4:$C$50&""),0)),"") CTRL+SHIFT+ENTER

may get you closer.

 

[lhogan]

Thank you- your formula did help get both text and numbers in the list, it is still dynamic and ignores blanks and duplicates.

The only problem is it is no longer alphabetized. I tried using some large/small functions to see if they could do it but they would not handle the text/number mix.

Any ideas??

 

[Weazel]

Hmmm...well, I'm not too proud to say I don't know, but, I don't know.

doing the google search thing I was reading up on Oscars site how you could possibly add in the sort but would require adding a named range and a few other things.

You can read about it here...Create a unique distinct sorted list containing both numbers text removing blanks in excel | Get Digital Help - Microsoft Excel resource

Hopefully that will get you closer to what you are trying to achieve or possibly someone will jump in who is a bit better at the large array formula's then I am.