IF, Wildcards and Search Oh-My

[Dregun]

Hello all,

I'm looking for a little assistance in getting a formula that I thought would be fairly easy to get working.

Here is the formula and the explanation of what I'm trying to do below in case it doesn't make any sense.

=IF(SEARCH("Basic*",H18),"Basic", IF(SEARCH("Finish*",H18),"Finish", IF(SEARCH("TP*",H18),"CMM", IF(SEARCH("Runout*",H18),"CMM",""))))

Basically what I'm trying to do here is remove the redundancy of my work by taking data I have already input into one sheet and placing it into another and have excel place this information in the correct cells. Examples.

"22.5 TP" (TP = True Position) that is measured on a CMM
"Runout 6.5" That is also measured on the CMM
"126.4 Basic" This is also measured on the CMM
".0025 Concentricity" Again CMM
"1.6 Finish" Measured with Profilometer
"0.4 Finish" Measured with Profilometer
"3.2 Finish" Measured with Profilometer

The data I'm trying to simplify looks like this:

Operation (10A,10B,10C or 10D)
Feature (Dimension)
Measuring Device Used
Frequencey of Checks

The problem is I have my features already, "3.2 Finish"; now I want in the "Measuring Device Used" cell to notice that the word "Finish" has been used and place the word "Profilometer" into its cell. However if it said "13.5 Basic" I want it to put "CMM" into that cell instead; and so on and so forth. The only argument that works is the first, all the other IF statments bring back #Value. I have so many formulas going accross multiple pages to reduce 1 full days worth of work down to just 30 minutes but if I cant figure this part out I will only have cut my work load in half, not complaining but really who doesnt want 4 more hours of their life back every other day.

Dregun

[arthurbr]

Hi,
would Ctrl+H then Find and Replace be an option?

Cheers

[Dregun]

Arthur,

Unfortunatly not, as the data is in multiple cells and needs to stay in the cells. What I mean is that the layout of the page looks like this.

[ 1 ][3.2 Finish][Profilometer][100%][100%]

Im using the IF function to input the text "Profilometer" anytime the cell next to it says "Finish" ect ect. I would love to change the format of the page so I dont have to do it that way but its a customer requirement for that format.

[arthurbr]

Could you post a small sample of your data and of what you are trying to do?

[Dregun]

Arthur,

Ill give it a shot.

A1 = 3.2 Finish
A2 = 1.6 Finish
A3 = 0.4 Finish
A4 = 25.4 Basic
A5 = TP 13.2
A6 = Runout 12.4

Column "B" needs to have the corresponding measuring method used for Column "A"

B1 = Profilometer
B2 = Profilometer
B3 = Profilometer
B4 = CMM
B5 = CMM
B6 = CMM

So when I put "0.4 Finish" in Column A it puts "Profilometer" in column B. Since the data is never going to be the same for A1 or any other column I really want Excel to make the changes depending on the data that was input in the previous cell. The "Finishes" are easy enough because we generally only have 3-4 specifications so thats not that big of a problem, its the other dimensions that have an infinite possibility but still use the same terms (Basic, TP, Runout etc) that I need to weed out. So when I have "Runout 1.6" and "Runout 1.2", "Runout 2.3" that it only cares that the word "Runout" was used and designates "CMM" for that next cell.

Does that help?

[daddylonglegs]

Try this formula in B1

=IF(ISNUMBER(SEARCH("Finish",A1)),"Profilometer","CMM")

although that would mean you'd get "CMM" for any cell which doesn't contain "Finish". To be more specific try

=IF(ISNUMBER(SEARCH("Finish",A1)),"Profilometer",IF(COUNT(SEARCH({"Runout","TP","Basic"},A1)),"CMM",""))

[Dregun]

The clouds have parted and now I can see the light!

Thank you so much; I would have never figured that one out, it's much cleaner then the formula I was writing.

Thanks again!!!


Dregun

[shg]

Here's a similar way:

=INDEX({"Profilometer","CMM","CMM","CMM","?"}, MATCH( TRUE, ISNUMBER(SEARCH({"Finish","Runout","TP","Basic","*"}, A1) ), 0) )

[Dregun]

I really appreciated all the help, unfortunatly I overlooked one very important function that really limits this.

I've included a section of the book I'm working on.

I can and will have at most times 400 dimensions.
I need to create a Control plan from those 400 dimensions

The control plan has my processes split between operations, we call them 10A, 10B, 10C, 10D. Not every dimension on the print is in order with our processes. Meaning I can have 20 dimensions in a row that are done in 10A and then the next 30 are in 10C and then 14 dimensions following that are in 10B. I know its a little confusing.

I thought about doing a lookup table, but not sure if it can do what I'm looking for. Also I need to have the different operations "Grouped". Meaning I might have 120 dimensions that fall under 10A and all of those need to be in the same group even if the numbers are scattered around. Then further down after 10A is 10B.

The pages I have uploaded in the "Control Plan" I have op 10A through 10B very VERY close together, in reality they will be about 100 rows apart from another.

Anyone want to take a crack at this and see what you can come up with?

Thanks,


Brad