krish asks:

I've a query in case of two columns of dates and two columns of data.
Calling columns A and B as data(text), C and D as Dates(dd/mm/yy),
IF column D's date is not empty and matches to the range, concatenate A & B and paste in in different sheet,
ELSE column C's date(column C will always have a date unlike column D) should be taken for range calculation and then concatenate A & B cells and paste in a cell.
Please do help in regards to this query

Answer:

The two pictures below are from the same worksheet.

Data

Sort cell values in corresponding columns

Sorting

Sort cell values in corresponding columns2

Array formula in cell G1:

=IFERROR(IFERROR(INDEX($A$2:$A$4&$B$2:$B$4, SMALL(IF(($D$2:$D$4<>"")*(TEXT($D$2:$D$4, "mmm")=G$1), MATCH(ROW($D$2:$D$4), ROW($D$2:$D$4)), ""), ROW(A1))), INDEX($A$2:$A$4&$B$2:$B$4, SMALL(IF(($D$2:$D$4="")*(TEXT($C$2:$C$4, "mmm")=G$1), MATCH(ROW($D$2:$D$4), ROW($D$2:$D$4)), ""), ROW(A1)-SUMPRODUCT(($D$2:$D$4<>"")*(TEXT($D$2:$D$4, "mmm")=G$1))))), "")

How to enter an array formula

  1. Select cell G1
  2. Click in formula bar
  3. Paste array formula to formula bar
    formula bar
  4. Press and hold CTRL + SHIFT
  5. Press Enter

How to copy the array formula

  1. Copy cell G2
  2. Paste to cell range H2:R2
  3. Copy cell range G2:R2
  4. Paste to cell range G3:R10

Download excel *.xlsx file

Concatenate by date.xlsx