Conditional concatenate cell content across rows

Question

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 1

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:

Answer 2

I would start by optimizing your O, P, and Q formulas.  You currently have

=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")

IFERROR is a great function for displaying a sanitized version of a computed value that might be an error code; I use it, and recommend it in answers on Super User, frequently.  As you probably know,

• IFERROR(calculated_value, default_value)

is short for

• IF(ISERROR(calculated_value), default_value, calculated_value)

But using IFERROR to create a sanitized version of some value and then testing that value to do something conditionally is an unnecessarily awkward way of using IFERROR.  The above formula can be simplified to

=IF(ISERROR(SEARCH("Brand is not valid", M42)), "", "Brand")

And, as I’m sure you know, SEARCH("Brand is not valid", M42) tests to see whether M42 contains Brand is not valid.  But, as long as Column M can contain only your three error strings, this can be shortened to

=IF(ISERROR(SEARCH("Brand", M42)), "", "Brand")

or simplified to

=IF(M42 = "Brand is not valid", "Brand", "")

---

OK, now I’ll make the O, P, and Q formulas a little more complicated:

• O42 → =IF($A42=$A41, O41, "") & IF(ISERROR(SEARCH("Brand", $M42)), "", "Brand")
• P42 → =IF($A42=$A41, P41, "") & IF(ISERROR(SEARCH("Product", $M42)), "", "Product")
• Q42 → =IF($A42=$A41, Q41, "") & IF(ISERROR(SEARCH("OEM", $M42)), "", "OEM")

The formula for O42 says,

If this is the second or third row for this ID (Column A), look at the cell above this one (i.e., the Column O cell for the previous row) to see whether we’ve already established that this thing has an invalid brand.  Also, look at Column M for this row to see whether it is Brand is not valid.  Then concatenate the results.

Since a unique ID will never be listed twice with the same error (right?), these two sub-results will never both be non-empty, so this is essentially doing an “OR”:

Show a value of Brand if this row, OR one of the previous row(s) for this ID, contains the invalid brand error.

This has the effect or dragging the O, P, and Q values down to the last row for each ID:

Note that rows 41, 44, 47, and 49 each shows the short forms of all errors that apply to their respective IDs in Columns O, P, and Q.

I’ve defined Column R the same way you did.  See Generate a comma-separated list of cell contents, excluding blanks for techniques to eliminate the unwanted slashes from this.

If having the desired concatenation only in rows 41, 44, 47, and 49 is good enough, you’re done.  Otherwise, define N42 as

=IF($A22=$A23, N23, R22)

or

=IF($A22<>$A23, R22, N23)

This is almost exactly the same trick I used in Columns O, P, and Q, but going in the opposite direction:

If this is the last row for this ID (i.e., if this is row 41, 44, 47, or 49), use the concatenation of the values from this row (which is the complete collection of error codes for this ID).  Otherwise, look at the cell below this one (i.e., the Column N cell for the next row), which will have the correct answer.

In other words, the desired values percolate up to the first row for each ID.