I have a worksheet with thousands of rows which contain a unique ID A, and one 3 different error codes M. Each individual ID may have 1, 2 or 3 errors. They will always be in the same order.
I have typed by hand and in N what the desired output is. I have written a formula like this one in cols O, P, Q with the text of the error:
=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")
I tried something like this in R but it's not working correctly for obvious reasons. Is there a way to do this so that R looks like N?
=CONCATENATE(O42,"/", P42,"/",Q42)
If not possible to do with Excel formula, is there perhaps a VBA way?
Answer
OK, took some head scratching, but I've got it:
Columns O, P & Q as you have them, titled Brand, Product & OEM. Turn the whole thing into a table with Ctrl-T (not required, but handy, and my column R relies on it, but you can use column references if you want)
Column R:
=IF(LEN([Brand])>0,[Brand],IF(LEN([Product])>0,[Product],IF(LEN([OEM])>0,[OEM],"")))
Column S:
=IF(A2=A4,F2&"/"&F3&"/"&F4,IF(A2=A1,"",IF(A2=A3,F2&"/"&F3,F2)))
Unfortunately, it seems the only way to use table references to a different row is to use Offset, so to keep it simpler, I went back to cell references. That kinda negates the cool/handy factor of turning the whole thing into a table in the first place, but, whatever...
And... Here's a picture of what it looks like:
No comments:
Post a Comment