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

rouzacct

Board Regular
Joined
Aug 31, 2010
Messages
65
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"
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
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:
<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

Board Regular
Joined
Aug 31, 2010
Messages
65
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

Board Regular
Joined
Aug 31, 2010
Messages
65
Code:
=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")))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,597
Messages
5,597,091
Members
414,122
Latest member
eazyyexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top