Excel Filter: IF Statements and Concatenate

August 7, 2014 1:35 PM

I'm trying to test if a a series of cells exist, and then concatenate the values that do exist with a paragraph I have.

So far I'm using a very ridiculous amount of nested IF statements to accomplish this, but I know there's a better way.
This is my example:

=IF(ISTEXT(W2),(
CONCATENATE("This week I worked with ",A2,".""On Monday we did their ",C2,", ",D2,", and ",E2,". ""On Tuesday we did their ",F2,", ",G2,", and ",H2,". ""On Wednesday we did their ",I2,", ",J2,", and ",K2,". ""On Thursday we did their ",L2,", ",M2,", and ",N2,". ""On Friday we did their ",O2,", ",P2,", and ",Q2,". ""On Saturday we did their ",R2,", ",S2,", and ",T2,". ""On Sunday we did their ",U2,", ",V2,", and ",W2,". "))

I know there's VBA but I don't really want to commit to learning that with my time constraints. This is for a project at work and I'm trying to get it done as soon as possible. My goals are to have the paragraph change it's formatting based on the values of the cells, just so I don't have something like "Today I sat with Blank and did and and. And and," etc. I also need it be something built into excel that people wouldn't have to import, because I'm going to distribute it to my coworkers and they aren't all technically incline.

I need to be pointed in the right direction. Maybe there's a simple way to do this in VBA that won't take a year. Maybe there's a totally different way to do this. Maybe I'm asking too much.

posted by trogdole to Technology (10 answers total)

I'm sorry if I'm being dense, but can you explain what you're trying to accomplish a little more clearly? Maybe an example? If you're trying to change formatting based on something, can you just use conditional formatting? VBA for something you're going to distribute to non-tech-savvy people is a bad idea, because their Excel is probably set up to not run macros unless they approve it and people will flip out.
posted by brainmouse at 1:40 PM on August 7, 2014

I think I get it. You only want the cell to produce the sentence if all the values are present as required.

What I'd do to avoid nested IFs (which, yes, avoid!!) is to make a testing cell, where you check to see that ALL values are present as required (e.g., =AND(ISTEXT(A2),ISTEXT(C2),...,ISTEXT(W2)), then you just make sure that the testing cell is TRUE with a single IF.
posted by So You're Saying These Are Pants? at 1:44 PM on August 7, 2014

And yes, agreed, no VBA!
posted by So You're Saying These Are Pants? at 1:45 PM on August 7, 2014

Does the end result need to be in Excel? It looks like the concatenation part would work a lot better as a merge in Word, if you can first use the IF statement to identify and filter which lines you want to include.
posted by something something at 1:49 PM on August 7, 2014

One clumsy option is to make a separate sheet, and completely fill that with =istext(sheet1!a1)
Then you can just do =if (and(istext(sheet2!a1:w1)), (Concatenate(...))

It is a bit messy but it keeps your formulas neat.

Except it doesn't work if your template doesn't use B
posted by aubilenon at 1:55 PM on August 7, 2014

Best answer: Since you're in a hurry and you're already most of the way there with nested IFs, I would break out your sentence into multiple cells to make your formulas a little saner. For instance:

="On Monday, we did " & A3 & IF(ISTEXT(C3), ", " & B3 & ", and " & C3, IF(ISTEXT(B3), " and " & B3, "")) & "."

"On Monday, we did a, b, and c." or "On Monday, we did a and b." or "On Monday, we did a." This holds it down to only two levels of nesting on the IFs. Build each sentence up in its own cell that way then mash 'em together into a final paragraph when you're happy with each one. This is a technique that helps with pretty much any complex Excel formula - be generous with intermediate cells and just hide them rather than packing your whole formula into one giant cell.
posted by pocams at 2:05 PM on August 7, 2014 [2 favorites]

Response by poster: Brainmouse,

I'm trying to piece the pieces together into a paragraph, but concatenate them so that I'm not inputting extra and's and commas, etc.

So Columns B-G, for example, will have a drop down where the managers can select what they did with that associate. I have three columns per day in case we do more than one thing. So I want the final paragraph to include the cells they did do, for example 2 Monday, one Tuesday, nothing Wednesday and Thursday, etc.

If I have Column B = Updated Notes and Column C = Coaching, and Column E = Notes, I would want it to read "This week I sat with Blank and Update Notes and Coaching and Notes.

Whereas if I did not have a column C, I would want it to say "This week I sat with Blank and Updated Notes and Notes." Instead of "This week I sat with Blank and Updated Notes and and Notes". The second 'and' is dependent on Column C's existence.

I hope that makes more sense.
posted by trogdole at 2:25 PM on August 7, 2014

Aaahhh yes that makes sense.

In that case, and given that you're distributing this, I definitely second what pocams said. Ifs are not the most efficient thing in the world, but with a limited set like this, just make 5 new columns and do one of them for each day, and then concatenate at the end, and hide your intermediate columns before you distribute. It's the simplest way to get out in one piece.
posted by brainmouse at 2:30 PM on August 7, 2014

Response by poster: I'm still trying to wrap my head around the formula, and I've never seen the ampersands before, but Pocam's method works out absolutely perfectly. Thank you so much.
posted by trogdole at 2:44 PM on August 7, 2014

Best answer: You can use ampersands instead of the "concatenate" formula, so basically:

=CONCATENATE(A1,B1,C1)

is exactly the same as

=A1&B1&C1

Usage of one over the other is functionally just a matter of preference, depending on what else is going on in your formula. They make more sense in what pocams posted than concatenate does because he has other formulas nested in there.
posted by brainmouse at 2:47 PM on August 7, 2014 [1 favorite]