Ahh, thanks a lot. Works perfectly.Thanks a million!
Just another thing: I see line 4 has the data needed in d4 instead, is it possible to add the D column to the formula as well? I could of course just change that line, but would be great, if it's possible to do the search on both columns.
Thanks,
Thomas.
You can use IFERROR() to run a second formula if the first results in an error:
=IFERROR(LOOKUP(2, 1/(--(ISNUMBER(SEARCH(Codes, $C4)))), Codes), LOOKUP(2, 1/(--(ISNUMBER(SEARCH(Codes, $D4)))), Codes))
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Excellent help JB!
Lot's of questions from me, and I highly appreciate you taking the time to help a noob like me. Let me know if you need some help with Photoshop or designing something, I'll be happy to help you back.
Thomas.
Put the codes in the order you want them to be checked in that table. You would want the shorter codes above the "longer" versions and it will always return the longest matching code it finds. Just assemble your column H codes, then sort the table ascending, that should do it.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
That worked perfectly.
In regards to the second part of my previous post: My boss told me that the rows with more than one of the codes needs to be inserted in a separate row below the existing one. (To allow for our webshop import script to work correctly) The optimal thing would be if all the data from the row is copied to the row below. Of course only for the rows that has more than one of the codes. Is this possible?
Thomas.
Everything is possible. This technique is formulaic and will give you one code from the list.Originally Posted by vikingim
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hehe, yeah. The problem is how to do it.Do you know what tecnhnique I need to use to figure out this new challenge?
Thomas.
This new little addon takes your project up to a new level of complexity. Formulas cannot "insert rows", obviously, they can only display a value. So, this would require a complete specialized VBA approach.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
That was what I was afraid of. :P Maybe it's just easier to edit the rows with more than one option manually. Hehe.
Thank you for all your help JB, I really appreciate it.
Thomas.
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks