Hi,
Having trouble creating the right formula or VBA code to pull out the desired details from coloumn A in the example to look like coloumn B
I need to retrieve the function with the highest percentage as the dominant function in the string of text with the criteria being the highest percentage. I am not sure if there is a formula I can use or alternatively a VBA code. I hope I have articulated my problem well.
Please see the example sheet showing the desired outcome.
thank you
in B2
Please Login or Register to view this content.
=MaxPerc(A2)
Here's a formula alternative:
=FILTERXML("<A><B>"&SUBSTITUTE(A2,";","</B><B>")&"</B></A>","//B[contains(.,'"&MAX(IF(ISERROR(1*MID(A2,ROW($1:$1000),3)),0,1*MID(A2,ROW($1:$1000),3)))&"')]")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hey Glenn, thanks for the formula - any reason why it may be giving me an error code? VALUE!
Hi Glenn, realised why it returns a #VALUE! error - only happens when there is a single entry into the info cell
Umm. No. It works perfectly for that single example.
Are there any values that contain either
&
or
< ??
Thanks John
yes there is a & would this be causing the error?
got it working! Thanks!
Who are you talking to here?Originally Posted by BrianJS
My apologies, Glenn - After you mentioned the & sign I went and had a look and this solved my problem, thank you!
It can be built into the formula too. It's a BIT more complicated than normal in your case as I am using the ; as the delimiter to build up the XML string... However, this will safely allow the presence of & in your strings:
=FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,";","¦"),"&","#38;"),"¦","</B><B>")&"</B></A>","//B[contains(.,'"&MAX(IF(ISERROR(1*MID(A2,ROW($1:$1000),3)),0,1*MID(A2,ROW($1:$1000),3)))&"')]")
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks