Amending a Key formula

Hi all

I was previously helped with a formula that created a key in which I could reference to.

Previously the formula below would look for a “*” and then use that cell as a key.

Please Login or Register  to view this content.

It is best shown in the attached file as its hard for me to explain.

I now need to change the formula where so it searches for a double star “**”

e.g. previously with this data:
212018 Sales in Cnt to Proc
* Total Sales
** Gross Segmental Sales


It would be key’d as:
212018 Sales in Cnt to Proc* Total Sales

But I now need this to be key'd as
212018 Sales in Cnt to Proc** Gross Segmental Sales

Can this be done?

How about =IFERROR(C5&INDEX(C5:C$461,MATCH("~**"&"~**",C5:C$461,0)),"")

Excellent thanks Bob!

What does this mean "~"?

It is basically escaping the following character. In this case, it is stopping the * being treated as a wildcard.

Hi,

I have been testing this and it hasn’t resolved my problem (it’s nothing wrong with the formula supplied as that works)

I need to go back to using a single “*” rather than a double so I will need the original formula amended if what I need is even possible...

I need a formula to key the data like before where it finds the “*” unless the cell contains this “* SR Maintenance accounts only” or “* SR Contracted Out Services”. If it does find this then I need it to ignore it and move on to the next instance of a star. Is this even possible?

I have included an example and I have highlighted in yellow the problem cells.

Hi,
That didnt didnt work, i think this part is wrong "IF(ISNUMBER(SEARCH("SR ",C62)),C62,"

Rather than return what is in the cell can we get it to skip to the next instance of a "*"

Does that makes sense?

e.g.
This data (3 rows):
223596 Facilities Management
* SR Contracted Out Services
** Production Other Overhead


Would need to be written as
223596 Facilities Management** Production Other Overhead

Rather than

223596 Facilities Management* SR Contracted Out Services

For reference the "*" indicate a subtotal - they are all correct apart from the "SR Contracted out" and "SR Maintenance accounts only" - these two I need to ignore/skip

Try this array formula

=IFERROR(C5&INDEX(C5:C$329,MATCH("~**",IF(C5:C$329<>"* SR Maintenance accounts only",IF(C5:C$329<>"* SR Contracted Out Services",C5:C$329)),0)),"")

Hi,

Thanks for looking into this.

Unfortunately that didn’t work either. To better explain my problem I have re-uploaded the file and highlighted in yellow the incorrect keys and I have stated (in green) what I need it to be.

Can anyone help resolve this?

Any one got any ideas how to resolve this? I have searched online for similar problems but still cant find a solution

Paul,

My formula was perfect ... unfortunately the forum stripped out some of the spaces. In the two IF tests where it says <>"* SR, add 7 spaces after the * and array enter it, you should be fine.

Bob you were correct it was perfect thanks! I am going to test this fully this morning - THANK YOU!

Hi,
When testing it I found another condition I need it to ignore...

How can I amend this:
=TRIM(IFERROR(C140&INDEX(C140:C$329,MATCH("~**",IF(C140:C$329<>"* SR Maintenance accounts only",IF(C140:C$329<>"* SR Contracted Out Services",C140:C$329)),0)),""))

To include this: * P & M Other Overhead Costs

Like SR Maintenance and SR Contracted out services i would need the above ignored

Pretty straightforward really

=TRIM(IFERROR(C140&INDEX(C140:C$329,MATCH("~**",IF(C140:C$329<>"* SR Maintenance accounts only",IF(C140:C$329<>"* SR Contracted Out Services",C140:C$329),IF(C140:C$329<>"* P & M Other Overhead Costs"))),0)),""))

Don't forget the spaces.

Hi - I couldnt get it to work - is the third if function missing the value if true/false part?

I get the error - too few arguements

THanks Ace _XL