Help with multiple IF ISBLANK statements

Murz

Hi,

I am trying to list values, comma delimited, from a number of columns based on if the previous column is blank. So for example, based on this table:

Object Code 1a	Object Code 1b	Object Code 1c	Object Code 1d	Object Code 2a	Object Code 2b	Object Code 2c	Object Code 2d	Object Code 3a	Object Code 3b	Object Code 3c	Object Code 3d
Object Code 1a	Test 1	Object Code 1b	Test 2	Object Code 1c		Object Code 1d		Object Code 2a	Test 3	Object Code 2b		Object Code 2c		Object Code 2d		Object Code 3a	Test 4	Object Code 3b	Test 5	Object Code 3c	Test 6	Object Code 3d

Object Code 1a will always have a value, as there must be at least this field filled out for the workflow to move forward. Object Code 1b-1d can be blank but might not be. Object Code 2a might have a value after the lasts populated cell in Object Code 1a-1d, so if Object Code 1b is the last filled out field, then Object Code 2a might have a value. If Object Code 2a has a value, then Object Code 2b might have a value or might be blank. A previous column with the same numerical value will always have a value so there would never be a scenario where 1a and 1c both have values but 1b is blank. And so on.

Then, when all the values are listed, I want to separate them with a comma.

I tried to just do a simple formula like =[Object Code 1a]@row +" , " + [Object Code 1b]@row +" , " + [Object Code 1c]@row+ " , " + [Object Code 1d]@row +" , " but I get: Test 1, , , and I don't want the row of commas to show up. I thought of using "ISBLANK" and I can make it work for one set of formulas but I can't figure out how to join them. I get an #UNPARSEABLE error.

So I want to start with saying, "If Object Code 1b is blank, then show Object Code 1a". Then "If Object Code 1c is blank then show Object Code 1a and Object Code 1b with a comma separating them. Then, if "Object Code 1d is blank, then show Object Code 1a, Object Code 1b, Object Code 1c" with commas separating. and so on.

Not sure if its is possible, but it would be really nice if the formula could check for blanks in 1b, 1c, 1d and move to 2a if so. But that might be too much.

My formula is this: (i am using "abc" and "def" for my true value so I can see if it is working. I'd take them out in the final formula.

=IF(OR(ISBLANK([Object Code 1b]@row), "abc", [Object Code 1a]@row) + IF(OR(ISBLANK([Object Code 1c]@row), "def", ([Object Code 1a]@row + " , " + [Object Code 1b]@row) + (IF(OR(ISBLANK([Object Code 1d]@row), "efg", ([Object Code 1a]@row + " , " + [Object Code 1b]@row + " , " + [Object Code 1c]@row + " , " + [Object Code 1d]@row)))))))

Without OR

=IF(ISBLANK([Object Code 1b]@row), "abc", [Object Code 1a]@row) + IF(ISBLANK([Object Code 1c]@row), "def", ([Object Code 1a]@row + " , " + [Object Code 1b]@row) + (IF(ISBLANK([Object Code 1d]@row), "efg", ([Object Code 1a]@row + " , " + [Object Code 1b]@row + " , " + [Object Code 1c]@row + " , " + [Object Code 1d]@row)))))))

With this formula, I get an "INCORRECT ARGUMENT SET" error and when I take out the "OR", I get just the value for "Object Code 1a". In my sheet, Object Code 1a and 1b are filled in so I should be getting those two values.

Can someone help? Thank you so much.

prime_nathaniel

@Murz

=JOIN(COLLECT([Object Code 1a]@row:[Object Code 3d]@row, [Object Code 1a]@row:[Object Code 3d]@row, <>""), ", ")

Collect the items in the row that are NOT blank into a collection. Join the collection with ", "

prime_nathaniel

@Murz

=JOIN(COLLECT([Object Code 1a]@row:[Object Code 3d]@row, [Object Code 1a]@row:[Object Code 3d]@row, <>""), ", ")

Collect the items in the row that are NOT blank into a collection. Join the collection with ", "

Murz

Ok, thank you! I think this is going in the right direction. However, my columns are set up with other values in between the Object Codes, and when I use this formula, it outputs all the column values between Object Code 1a and Object Code 3d.

So I tried to list all the Object Codes in your formula, but it returns a blank cell.

=JOIN(COLLECT([Object Code 1a]@row, [Object Code 1b]@row, [Object Code 1c]@row, [Object Code 1d]@row, [Object Code 2a]@row, [Object Code 2b]@row, [Object Code 2c]@row, [Object Code 2d]@row, <>""), ",")

What am I doing wrong? I think this will work I'm just not quite there yet. Thank you so much!