IF Condition: two cells with dates and concatenation of corresponding 2 text cells

Hello All,

I'm trying to automate an excel sheet. Sheet1 contains 4 columns and n rows.
4 Columns = A, B, C, D. Columns A and B have data(text) in them. Columns C and D have dates in them.
D cell can be empty without a date but C wont. Now the requirement goes this way:

If D has a date, Concatenate A and B and paste the data under Column of corresponding month(current year)
Else
C's date, consider it, concatenate A and B and paste the data under Column of the month(current year).

This is the requirement. Any help appreciated.

Regards,
D

A	B	C	D
Data with text	Data With Text	dd/mm/yy	dd/mm/yy(this column will be empty most of the times)

result:

Jan	Feb	Mar	Apr
Concatenated data of Cells A and B with their C/D dates falling under Jan/2013	Concatenated data of Cells A and B with their C/D dates falling under Feb/2013	Concatenated data of Cells A and B with their C/D dates falling under Mar/2013	Concatenated data of Cells A and B with their C/D dates falling under Apr/2013

Something like the attached, maybe?

That's almost close Andrew. The current code is only working for horizontal while I am trying to look at columnD and then ColumnC, once confirmed: adding A and B. Could it be possible to use Index to obtain the dates, match to match the current month and year and then concatenate A and B cells.
Please let me know, if it can be doable?!
Many Thanks

I am trying to use Index and match functions.. I have come closer to looking at the date from one row and concatenate data of corresponding cells. Stuck at having two cells dates to go through..

Can you post an example workbook, please (press the 'Go advanced' button and then the paperclip icon), because I'm really not sure what you're trying to achieve.

Concatenate by date.xlsx

Please find the attached file to this mail.
I have added months to the same file (to the right of the months you concatenate) you have sent me and have added data to the result set. Please do help, when you can.

Regards,
Krish

OK, I cheated and added a helper column (column E) with the following formula:

=IF(D2<>"",D2,C2)

Then, in the first row of the January column I went with:

=IF(ROW(A1)>SUMPRODUCT(--(TEXT($E$2:$E$15,"mmm")=T$1),--($E$2:$E$15<>"")),"",INDEX($A:$A,SMALL(IF(TEXT($E$2:$E$15,"mmm")=T$1,ROW($E$2:$E$15)),ROW(A1))) & INDEX($B:$B,SMALL(IF(TEXT($E$2:$E$15,"mmm")=T$1,ROW($E$2:$E$15)),ROW(A1))))

This is an array formula and must be entered with Ctrl-Shift-Enter, not just enter. Once entered it can be copied across and down.

Is that what you were after?

Hi Andrew, Thanks a lot for the help. The dates should be calculated for current year and if they are in any other year, the particular column be omitted. I've run the code and its working fine. I am now interested in keeping it to current year. Any help appreciated. Many Thanks.

Try changing the formula in column E to:

=IF(AND(D2<>"",(YEAR(D2)=YEAR(NOW())),D2,AND(C2<>"",(YEAR(C2)=YEAR(NOW())),C2,"")

Andrew, the formula in column E is coming up with errors.. did we miss any parenthesis?

Probably Give this one a go:

=IF(AND(D2<>"",YEAR(D2)=YEAR(NOW())),D2,IF(AND(C2<>"",YEAR(C2)=YEAR(NOW())),C2,""))