Concatenate & Nested if statements??

[TotallyLost]

I have two columns of formula's that I then concatenate the results into a third formula. Is there any way to combine these formula's into one?

Formula 1: =IF(G6="","",IF(F6="",C5,F6))
Formula 2: =IF(G6="","",IF(E6="",B5,E6))
Formula 3: =CONCATENATE(B6," ",C6)

Thank you to anyone that can help me out.

[bonny24tycoon]

That would mean the final output should either be Off premise or on premise, right?




If I was able to help – PLEASE DO NOT FORGET to Click the small star icon at the bottom left of my post.

[TotallyLost]

Yes for the first formula. The second formula will then show the Capt Moregan Rum Blk Spiced and the third formula would put the three together. However I apologize I failed to add that the second formula will have multiple results like On and Off Premise. There could be dozens of different products and those products could be sold on or off premise.

[bonny24tycoon]

Hi,

Try this in A6

Please Login or Register  to view this content.

And then drag it further..




If I was able to help – PLEASE DO NOT FORGET to Click the small star icon at the bottom left of my post.

[TotallyLost]

I think we are close, but that is not quite it. I wanted to eliminate the other two columns altogether for the formula 1 & 2.

[TotallyLost]

I've redone the spreadsheet to show multiple products.

My goal is to show, in column A the name of the product from column E and the premise from column F. However I want to eliminate Column B & C so that Column A has the entire formula of the three currently.

Formula 1: =IF(G6="","",IF(F6="",C5,F6))
Formula 2: =IF(G6="","",IF(E6="",B5,E6))
Formula 3: =CONCATENATE(B6," ",C6)

[micope21]

Try this

=IF(G6="","",IF(E6="",B5,E6&" "&IF(F6="",C5,F6)))

[TotallyLost]

Sorry that didn't work. It worked only on the first line product where the name was in column E6 but not in the lines below with nothing in E7,8,9, etc.

I put your formula in A6. When I delete column B&C it makes everything below A6 a zero value.Concatenate & If Statement.xlsx

[micope21]

This one =IF(G6="","",IF(E6="",B5,E6&" "&IF(F6="",C5,F6))) work on my computer.

To short it. Put in A6 =IF(E6="","",IF(C6="",A5,C6&" "&D6)) Then copy down

[TotallyLost]

Concatenate & If Statement.xlsxOk I altered slightly to take away all the columns between the formula and my spreadsheet. The only problem now is that it does not change the "Premise" second part of the formula. It is changing the product name which is the first part of the formula however. Any chance we can get the second half to work?

Attached is the updated file.

[micope21]

Can you try to explain to me what you trying to say about Off and on Premise?

[TotallyLost]

Concatenate & If Statement.xlsxIn Cell C6 is the word Off Premise which I need in the formula. Everything below C6 until you get to C22 will be Off Premise but then in C22 it changes to On Premise for C22-C23 & C24.

Your formula will change the value in column B when the product name changes, but it does not change Column C when it changes.