Exclude cells containing "_" from range given to dropdown list

[Jsmithachoo29665]

Hi! Here's an easy one for you guys!
I have this table row of which I want a range including only cells without the underscore char.
The underscores appear in no particular order.

Example:
Apple|Orange|Apple_Nutrients|Orange_Nutrients|Banana|Banana_Nutrients|Banana_Count

Desired range: Apple|Orange|Banana

Thanks a bunch!

 

[Aladin Akyurek]

What does | mean here?

 

[Jsmithachoo29665]

Hi Aladin, thanks for replying!

You're right I wasn't clear enough.
The source is a table row so "|" means another cell/column

I'm trying to use the desired range in a dropdown list via data validation.

So the Desired range is : {Apple,Orange,Banana}

 

[Aladin Akyurek]

Sheet1

Row\Col	A​	B​
1​		3​
2​	Apple	Apple
3​	Orange	Orange
4​	Apple_Nutrients	Banana
5​	Orange_Nutrients
6​	Banana
7​	Banana_Nutrients
8​	Banana_Count

In B1 just enter:

=COUNTIFS(A2:A8,"?*")-COUNTIFS(A2:A8,"*_*")

Name B1 Size via the Name Box.

In B2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$2:B2)>Size,"",INDEX($A$2:$A$8,SMALL(IF(ISERROR(FIND("_",$A$2:$A$8)),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($B$2:B2))))

Next, define DVlist in Formulas | Name Manager as referring to:

=OFFSET($B$2,0,0,Size)

Now you can use DVlist to data validate a cell where it is needed.

 

[Rick Rothstein]

Sheet1

Row\Col	A​	B​
1​		3​
2​	Apple	Apple
3​	Orange	Orange
4​	Apple_Nutrients	Banana
5​	Orange_Nutrients
6​	Banana
7​	Banana_Nutrients
8​	Banana_Count

<tbody>
</tbody>

The OP said "The source is a table row so "|" means another cell/column"

 

[Rick Rothstein]

Here is a VBA macro solution. First, though, you were not exactly clear where your row of values were located at, so I assumed they started at cell C3. Also, you did not say where the Data Validation List should go, so I assumed cell A3. Change these assumptions to match your actual locations.

[table="width: 500"]
[tr]
	[td]Sub GetNonUnderscoreCells()
  Dim Addr As String, StartCell As Range
  Set StartCell = Range("[B][COLOR="#FF0000"]C3[/COLOR][/B]")
  Addr = StartCell.Address & ":" & StartCell.End(xlToRight).Address
  With Range("[B][COLOR="#0000FF"]A3[/COLOR][/B]").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(Split(Replace(Replace(Application.Trim(Join(Application.Index(Evaluate("IF(NOT(ISNUMBER(FIND(""_""," & Addr & "))),SUBSTITUTE(" & Addr & ","" "",""_""),"""")"), 1, 0))), " ", "|"), "_", " "), "|"), ",")
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
  End With
End Sub[/td]
[/tr]
[/table]

 

[Aladin Akyurek]

The OP said "The source is a table row so "|" means another cell/column"

Great. That cell was apparently a sufficient hint to me... Thanks for catching that Rick.

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​
2​	Apple	Orange	Apple_Nutrients	Orange_Nutrients	Banana	Banana_Nutrients	Banana_Count
3​	3​	Apple	Orange	Banana
4​

<tbody>
</tbody>

B3, jusn enter:

=COUNTIFS(A2:G2,"?*")-COUNTIFS(A2:G2,"*_*")

Name A3 Size thru the Name Box.

In B3 control+shift+enter, not just enter, and copy across:

=IF(COLUMNS($B$2:B2)>Size,"",INDEX($A$2:$G$2,SMALL(IF(ISERROR(FIND("_",$A$2:$G$2)),
     COLUMN($A$2:$G$2)-COLUMN($A$2)+1),COLUMNS($B$3:B3))))

Next, define DVlist in Formulas | Name Manager as referring to:
​

=OFFSET(Sheet1!$B$3,0,0,,Size)

Adjust the sheet name to suit.

DVlist is now ready for using as Source in data validation cells.

 

[Jsmithachoo29665]

Thank you very much guys! Your help is much appreciated.
I used Aladins solution because I haven't reached vba skills just yet

 

[Rick Rothstein]

I used Aladins solution because I haven't reached vba skills just yet

If you wanted to try the macro out on a copy of your workbook just to see how it worked, here is the instructions on how to implement the code I posted in Message #6 ...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code (from Message #6 ) into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (GetNonUnderscoreCells) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro. Alternately, you can add a Forms CommandButton to the worksheet and assign this macro to it, then you would only have to click the button to run the macro.

 

[Aladin Akyurek]

Thank you very much guys! Your help is much appreciated.
I used Aladins solution because I haven't reached vba skills just yet

You are very welcome. Thanks for the update.