Case Select guidence please

[dasher108]

OK so here I am again looking for help. (I really do not have alot of posts because I normally figure a solution by searching the forum)

The scenario

Here is what I started doing and then thought vba must be better

=IF(ISNUMBER(SEARCH("Red",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("Ron",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("cat",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("cat1",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("air",Data1!B2)),"P2",IF(ISNUMBER(SEARCH("Cog",Data1!B2)),"P2"))))))+IF(ISNUMBER(SEARCH("Sul",Data1!B2)),"P2")

Thank you for any help
Lee

 

[njimack]

=IF(ISNUMBER(MATCH({"Red";"Ron";"cat";"cat1";"air";"Cog";"Sul"},Data1!B2,0)),"P1","")

EDIT - it may be better to have the list of values you're searching for in a range (say A1:A7). Your formula would then be
=IF(ISNUMBER(MATCH(A1:A7,Data1!B2,0)),"P1","")

 

[dasher108]

=IF(ISNUMBER(MATCH({"Red";"Ron";"cat";"cat1";"air";"Cog";"Sul"},Data1!B2,0)),"P1","")

Looks simple, did not even think of that I will give it a try. Is there a limit to how many conditions I can use?

 

[njimack]

Looks simple, did not even think of that I will give it a try. Is there a limit to how many conditions I can use?

Don't think so, but if you have a long list, consider using a range of cells, per my edited post.

 

[texasalynn]

so here is a case statement

Select Case Range("B2").Value
    Case "Red", "Ron", "cat", "cat1"
        Range("C2") = "P1"
    Case "air", "Cog", "Sul"
        Range("C2") = "P2"
End Select

 

[dasher108]

Don't think so, but if you have a long list, consider using a range of cells, per my edited post.

The list of text referenced on Data1 is populated automatically from a different program. I do not think I will be able to create a range I can choose from.

I really thing vba case select would work well here. I am just having trouble finding reference posts.

Thank you
Lee

 

[texasalynn]

did you look at my post?

 

[dasher108]

I did thank you! You must have just beat me to the response.

 

[dasher108]

Ok I am really struggling with this.

A different program exports unformatted data to "Data 1" data sheet. I am using a different worksheet in the same work book to display the data in a format I like.

Might be easier if the attached is viewed

 

[njimack]

What you've posted bears no relation to your original question. It looks like all that's required is a simple MATCH function.

Excel Workbook
	A	B	C	D	E	F	G	H
5	ID	RANK	LOCATION	DATE	DESCRIPTION	*	*	List of Locations
6	632259	1	North	07-Mar	text description	*	*	North
7	632291	2	South	06-Mar	text description	*	*	South
8	632298	3	East	06-Mar	text description	*	*	East
9	632304	4	West	07-Mar	text description	*	*	West
10	632305	5	North West	07-Mar	text description	*	*	North West
11	632307	6	South East	07-Mar	text description	*	*	South East
12	632309	1	North	07-Mar	text description	*	*	*
previous