IF and Concatenate

  • Thread starter Thread starter excelq
  • Start date Start date
E

excelq

Hi

I have a following chart which list out delivery dates arcross the top
with items and units on the body of the chart (dashes are spaces)
On the right column is the results that I need. What kind of formula
can I use to return such results?
I thought that I can use IF and Concatenate formula (IF, ordered units,
then seek out date....). Not even sure I can use IF, since in my real
chart, I have more than 20+ dates going across.
Any help would be much appreciated!

Style---7/30---8/13----8/14----8/15---8/17--------Wanted-Result
66106-------------------------------9--------5--------9 DUE 8/15, 5 DUE
8/17
66107-------------------------------30-------8-------30 DUE 8/15, 8 DUE
8/17
66111—44-----3--------------------------------------44 DUE 7/30, 3 DUE
8/13
66112—3-------9--------------------------------------3 DUE 7/30, 3 DUE
8/13
66113--------------------36---------------------------36 DUE 8/14
66114-----------------------------9--------------------9 DUE 8/15
 
Here are two alternatives:

1) concatenate each column in a nice way
Example: =IF(ISNUMBER(B2),B2&" Due
"&TEXT(B$1,"M/DD"),"")&IF(ISNUMBER(C2),IF(SUM($B2:B2)>0,", ","")&C2&"
Due "&TEXT(C$1,"M/DD"),"")

2) Use MCONCAT from http://xcell05.free.fr/english/
=LEFT(MCONCAT(IF(ISNUMBER(B2:F2),B2:F2&" Due "&TEXT(B$1:F$1,"M/DD")&",
","")),LEN(MCONCAT(IF(ISNUMBER(B2:F2),B2:F2&" Due
"&TEXT(B$1:F$1,"M/DD")&", ","")))-2*(SUM(B2:F2)>0))


Hope it can helped
Ola Sandström


Note:
For alternative 1) every column needs a separate formula, concatenated
- piece by piece - by the & sign. =Tedious work.
For alternative 2) First install the add-in. Then copy the above
formula. Note that the formula must be confirmed by holding down Ctrl
and Shift and then hit Enter (Array formula). This is initially more
complicated but easier in the long run.

Attached zip-file:
http://www.excelforum.com/attachment.php?attachmentid=3584&stc=1


+-------------------------------------------------------------------+
|Filename: Book5.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3584 |
+-------------------------------------------------------------------+
 
Ola,

Thank you sooo much for your thorough explanation.
I don't think alternative 1, is going to work b/c as you mentioned,
every column needs a separate formula and I have more than 30 columns
going across.

Alternative 2, I would need to take home and study b/c I don't
understand the formula 100%. The website for Add-In is blocked from
work, so i'm going to download it from home.

Hope you don't mind but I may need some help later if I fail to mater
this formula.

Thank you again!!

-young
 
Back
Top