remove nested key word search and make formula pickup any key words from list instead.

[rouzacct]

Hi everyone

I am trying to modify the formula below but if u can think of a better approach, your input will be appreciated


<code>=IF(A2=0,"",IF(ISERROR(FIND("extra",LOWER(J2))),IF(ISERROR(OFFSET(PROJECTS[[#Headers],[PROJECT NAME]],MATCH(MID(H2,FIND("project",LOWER(H2)),FIND(" ",H2,FIND("project",LOWER(H2)))-FIND("project",LOWER(H2))),PROJECTS!B$2:B$2000,0),0)),"Regular",OFFSET(PROJECTS[[#Headers],[PROJECT NAME]],MATCH(MID(H2,FIND("project",LOWER(H2)),FIND(" ",H2,FIND("project",LOWER(H2)))-FIND("project",LOWER(H2))),PROJECTS!B$2:B$2000,0),0)),"Extra"))


to remove the nested find for the word "Project"
</code>
Would it be possible to have this formula work without looking for a project name containing "project" all projects are differently named and some even have commas in them can this formula be adapted to pickup any strings written in column B of "projects" sheet ?
and searching through Column H's Various strings
and see if any of the projects are found and return the name of that project

The projects are written on a sheet called "Projects"
Column A has an Sorting order number to help sort put the projects in order not to get false positives for incomplete matches.
Column B has the project IDs
Column C has the project names


and the formula will go in Column R of a sheet called "LIVE ALL"

also
if A=0 should just return "" (blank)
if Column J of "LIVE ALL" has the word "EXTRA" written in it it should just return "Extra"
and if no projects found in H it should return "Regular"

 

[Tyron]

Hi everyone

I am trying to modify the formula below but if u can think of a better approach, your input will be appreciated

<code>=IF(A2=0,"",IF(ISERROR(FIND("extra",LOWER(J2))),IF(ISERROR(OFFSET(PROJECTS[[#Headers],[PROJECT NAME]],MATCH(MID(H2,FIND("project",LOWER(H2)),FIND(" ",H2,FIND("project",LOWER(H2)))-FIND("project",LOWER(H2))),PROJECTS!B$2:B$2000,0),0)),"Regular",OFFSET(PROJECTS[[#Headers],[PROJECT NAME]],MATCH(MID(H2,FIND("project",LOWER(H2)),FIND(" ",H2,FIND("project",LOWER(H2)))-FIND("project",LOWER(H2))),PROJECTS!B$2:B$2000,0),0)),"Extra"))

to remove the nested find for the word "Project"
</code>
Would it be possible to have this formula work without looking for a project name containing "project" all projects are differently named and some even have commas in them can this formula be adapted to pickup any strings written in column B of "projects" sheet ?
and searching through Column H's Various strings
and see if any of the projects are found and return the name of that project

The projects are written on a sheet called "Projects"
Column A has an Sorting order number to help sort put the projects in order not to get false positives for incomplete matches.
Column B has the project IDs
Column C has the project names


and the formula will go in Column R of a sheet called "LIVE ALL"

also
if A=0 should just return "" (blank)
if Column J of "LIVE ALL" has the word "EXTRA" written in it it should just return "Extra"
and if no projects found in H it should return "Regular"

Hey rouzacct,

Wow! That is some long formula. Or is it part of some VBA?

It would probably help if you included a visual representation, using pretend data, of the before and after of some of the situations you would experience if the formula worked exactly as you wanted.

later

Ty

 

[rouzacct]

Hi all

The formula will be written in Column R of Sheet LIVE ALL
I will pull it all the way down manually for all rows of data


The formula needs to search in the Entire string (what ever text with or without commas) of Column H and see if it contains one of the project IDs and return the project name

Basically i tried this with VBA the code was heavy and took 8+Minutes to run as Rows of Data were growing

SO since Excel can calculate Formulas all at once I have decided to remove that heavy VBA code and I am trying to find a way to have a formula that will simply see if any project ID is found anywhere in that string of Column H and return the Project name

The idea is to have users add and remove projects and not need to touch the formula as it will just adjust to the table on Projects sheet




SO the "projects" sheet has this table

SORTING ID <tbody> </tbody>	PROJECT ID <tbody> </tbody>	PROJECT NAME <tbody> </tbody>
E1 <tbody> </tbody>	APPLE617,222 <tbody> </tbody>	222 <tbody> </tbody>
E <tbody> </tbody>	APPLE617 <tbody> </tbody>	APPLE <tbody> </tbody>
D <tbody> </tbody>	SPECIAL1,009 <tbody> </tbody>	PROJECT 1-1 <tbody> </tbody>
C <tbody> </tbody>	SPECIAL1 <tbody> </tbody>	PROJECT 1 <tbody> </tbody>
B <tbody> </tbody>	BANANA <tbody> </tbody>	BANANAS <tbody> </tbody>
A1 <tbody> </tbody>	FFSB16-011,GRAPES33 <tbody> </tbody>	GRAPES <tbody> </tbody>
A <tbody> </tbody>	FFSB16-011,GRAPES <tbody> </tbody>	FFSB16-011 <tbody> </tbody>

<tbody>
</tbody>

The sorting ID is just to put them in order so the most unique variation of each project is at the bottom so excel wont give partial "false" positives

I will do that manually


and the Live All sheet

I'm sorry for my lack of imagination with the project naming the idea is that they can be any word ! and some may have partial matching names but have something different between them and they need to be picked up by the formula to be maybe found in a Column H of Live All sheet


The formula has two more arguments but i can always add them later I am really stuck on this search part of it

 

[rouzacct]

=if(a2="","",if(n2="extra","extra",iferror(index(projects!c:c,match(lookup(2^15,search(projects!$b$2:$b$20,'live all'!h2),projects!$b$2:$b$20),projects!b:b,0)),"regular")))

 

[Aladin Akyurek]

Care to post a sample example along with the desired results?