Search, Look Up, Find, Index from WBS

Hi,

Working a sizable project & have a sample Work Breakdown Structure (WBS) attached

Intent is to find Milestones but there are numerous instances with callout variations

For Example:

• ECR
• ECR FINISH
• ECR RELEASE

As you'll see through attachment, Table 2 represents unique variations of milestones extracted

In another Table 3 or new worksheet - Looking to search or find these various milestones & re-create list with task line items data

How to create function(s) that will do this well in Table 3 or new worksheet?

See Attachment...

Hi,

Requesting assistance please....

Hi Group,

I know this may be complex situation with the callout variations but interested to see prospective solutions

Thanks

Short of filtering Table 2 manually, copying and pasting the results here's a formula method. Try array entering this in cell I11.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Fill down and across column M until you get blanks.

Formula:

Please Login or Register  to view this content.

By the way. Please update your profile to show what version(s) of Excel you are using. It helps us when proposing solution. There are other formula ways to do this.

I failed to mention

Working a sizable project

means array formula solution(s) might not be practical.

I don't know why I didn't think of this before. With a helper column in G of Table 2 containing this formula

Formula:

Please Login or Register  to view this content.

you can use this non array formula in cell I11 filled down and across as before.

Formula:

Please Login or Register  to view this content.

Hi FlameRetired

This works great!!

If you could assist me one more time

I forgot to add or mention a Project # column - Column A

In my example, I have 2 projects showing - Project 1223 & 1223 A

How can I factor this in your function?

I attached a revised example

Select cell I11. Click the fill handle and drag to the left. Fill down. You can do the same with the helper column version if you prefer.

Awesome FlameRetired!!!

Thank you for the assistance!!!

You are welcome. Thank you for the feedback, added rep and marking this thread Solved.

Hi FlameRetired & Others

I thought this resolved & for the most part it works well, but not consistently

Initially, I submitted with 1 search criteria that has numerous callout variations. I want to make sure that these callouts are also associated with the applicable Project IDs & as copy down function in my master file but while reviewing, it's consistently associating with applicable Project ID.

For example - Looking up variations of "ECR" & want to make sure all instances are found associated to Project ID: 1223

How to change this function to also look up, search, find, Project ID, "1223" in addition to the variations of "ECR"

=IFERROR(INDEX(B$55:B$343,SMALL(IF(ISNUMBER(FIND("ECR",$C$55:$C$343)),ROW($C$55:$C$343)-MIN(ROW($C$55:$C$343))+1),ROWS($I$11:$I11))),"")

See attached

Is it possible to have a multiple index, search, find or look ups within column or table ranges?

I think that's what I need here - Find 2 criteria with at least one having variations of text or callouts


Thanks for your expertise with providing solutions

=IFERROR(INDEX(B$55:B$343,SMALL(IF(ISNUMBER(FIND("ECR",$C$55:$C$343)),ROW($C$55:$C$343)-MIN(ROW($C$55:$C$343))+1),ROWS($I$11:$I11))),"")

Hi

I attached a slightly modified table for reference & assistance request

Thank you

If there can be multiple criteria and they vary I recommend referencing those in a helper cell. It makes the formula more flexible and avoids having to chronically edit the formula every time there is a change.

In the attached find ECR in cell I32 and 1223 in J32.

Then array enter this formula in cell I37 of New Table 3. Fill down and across as before.

Formula:

Please Login or Register  to view this content.

FlameRetired!!!

Once again to the rescue!!

Questions:

What is this doing?

=AGGREGATE(15,6,SEARCH({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},[@[Task Name]]),1)

As for the new table, that's exactly what I have in mind in other tables created for various filtering

In cell 32, where "ECR" is currently showing, I made it a drop down list from Table 1 - Milestone Callout Variations

Cell J32 - Made drop down list - 1223 & 1223 A

If I have different inputs in these cells, then should't the table also be updating?

Not in all instances, but in some instances as inputs are changed, the table goes "blank"!!!

For example: PILOT / 1223

If going to Table 2 & manually filtering for these, results gives numerous instances as expected.

Is something not updating in your table?

For the most part, it works great!!!

Hi mycon73
FIND() is case-sensitive, use SEARCH() formula instead. With FIND(), "PILOT" and "pilot" are not the same.

You also can try non-CSE formula.

Please Login or Register  to view this content.

Fill right and down.

As congnt92 points out FIND is case sensitive which is one reason I chose it. Please recall that the initial criterion was "ECR". The desired output all contained that case specific string. Doing this circumvented returning anything else in lower case that would not qualify.

This also caught my eye.

Working a sizable project & have a sample Work Breakdown Structure (WBS) attached

My experience comparing FIND & SEARCH (although limited) have lead me to believe that FIND performs much faster than SEARCH. I prefer it when permitted.

Try replacing FIND with SEARCH. If that does the job then problem solved.

If you find your workbook is taking a performance hit and all other bottle necks have been addressed try a non case sensitive FIND (use two UPPER (or LOWER) function calls). These extra function calls seem to have little effect upon speed.

Hi FlameRetired & congnt92

Your functions works well in the given example work file

Still not sure what the AGGREGATE function is doing

Now, I'm trying to get it to work in my project file & have to reference another worksheet & having some difficulties duplicating

I would think it would work the same with the minor modification but for whatever reasons not working at the moment

Maybe it's because I don't have the AGGREGATE column in

Is it this or something else?

This leaves too much to the imagination. Without seeing a representative workbook sample I can't tell.

Hi congnt92 & FlameRetired

Overall, this function works well but it's not 100% working (within my master working file). There are instances with no results.


I37=IFERROR(INDEX(B$57:B$343,AGGREGATE(15,6,ROW($D$57:$D$343)/SEARCH($I$32,$D$57:$D$343)^0/($B$57:$B$343=$J$32)-ROW($D$56),ROW($A1))),"")


If I want to modify this to find 1 criteria instead of 2, how to change it up?

Thanks

Is it possible to search a group of word variations, such as: Gate, Stage, SG, Stage Gate

Would these variations have to be in own cells or can they be in one cell?

Originally Posted by mycon73

Hi congnt92 & FlameRetired

Overall, this function works well but it's not 100% working (within my master working file). There are instances with no results.


I37=IFERROR(INDEX(B$57:B$343,AGGREGATE(15,6,ROW($D$57:$D$343)/SEARCH($I$32,$D$57:$D$343)^0/($B$57:$B$343=$J$32)-ROW($D$56),ROW($A1))),"")


If I want to modify this to find 1 criteria instead of 2, how to change it up?

Thanks

After reviewing examples given for single criteria look up, I figured out on how to change up the function

Still working to verify that a 2 criteria look up works 100%.

Still interested to know if multiple look up variations can be utilized

For example:

Create preliminary BOM for Stage 1
Gate 0 presentation complete
Gate 0 Review
Gate 1 Review
Gate 2 Review
Gate 3 Review
Gate 4 Review
Prepare and present Gate 1 Review
Prepare and present Gate 2
Prepare and present Gate 3 Review
Prepare for Gate 4 Review
Provide variance from preliminary BOM from stage 1
Provide variance from preliminary BOM from Stage 2
SG I & II
SG III
SG IV
SG1
SG3
SG4
Stage 0 - Project Scope
Stage 1 - Concept Feasibility
Stage 2 - Concept Verification
Stage 3 - Design Verification
Stage 4 - Manufacturing Readiness
Work with Design Engineer to create a bridge from Stage 1 to Stage 2 of cost change
Work with Design Engineer to create a bridge from Stage 2 to Stage 3 of cost change


If I use search word criteria to find these variations, such as: Gate, Stage, SG, Stage Gate, would these words have to be in separate cells or can search with all of them in a single cell?