Search code examples
exceldateextractmergesortarray-merge

Extract and merge non duplicated data from 2 columns on excel with formula

I'm trying to merge 2 date columns (contains blanks cells) and extract the non-duplicates excluding the blank cells while sorted in ascending order.

However, I'm encountering error trying to do so. As excel took in the blank cells as data and extracted data in the end contains invalid info that were not found in either columns. The extracted data were not sort correctly too.

S1 Date S1 Count S2 Date S2 Count Overall Date Overall Count
01/10/2021 56 01/10/2021 127 01/10/2021 183
02/10/2021 98 02/10/2021 125 02/10/2021 223
03/10/2021 122 03/10/2021 51 03/10/2021 173
04/10/2021 45 04/10/2021 54 04/10/2021 99
05/10/2021 81 05/10/2021 64 05/10/2021 145
06/10/2021 21 06/10/2021 87 06/10/2021 108
07/10/2021 84 07/10/2021 63 07/10/2021 147
08/10/2021 12 08/10/2021 79 08/10/2021 91
09/10/2021 63 09/10/2021 45 09/10/2021 108
10/10/2021 79 10/10/2021 81 10/10/2021 160
11/10/2021 65 11/10/2021 21 11/10/2021 86
12/10/2021 81 12/10/2021 121 12/10/2021 202
20/10/2021 71 14/10/2021 54 20/10/2021 204
21/10/2021 83 15/10/2021 97 21/10/2021 151
22/10/2021 127 16/10/2021 67 22/10/2021 205
23/10/2021 84 17/10/2021 98 23/10/2021 137
24/10/2021 121 18/10/2021 54 24/10/2021 144
25/10/2021 54 19/10/2021 62 25/10/2021 54
26/10/2021 58 20/10/2021 133 26/10/2021 58
27/10/2021 87 21/10/2021 68 27/10/2021 87
28/10/2021 64 22/10/2021 78 28/10/2021 102
29/10/2021 34 23/10/2021 53 29/10/2021 109
30/10/2021 120 24/10/2021 23 30/10/2021 184
31/10/2021 78 28/10/2021 38 31/10/2021 78
29/10/2021 75 00/01/1900 0
30/10/2021 64 14/10/2021 54
15/10/2021 97
16/10/2021 67
17/10/2021 98
18/10/2021 54
19/10/2021 62

Excel image- Error highlighted in yellow

The formula used was =SORT(IFERROR(IFERROR(INDEX(DateS1,MATCH(0,COUNTIF($F$3:F3,DateS1),0)),INDEX(DateS2,MATCH(0,COUNTIF($F$3:F3,DateS2),0))),""))

Really appreciate if anyone could help.

Solution

  • If you have Windows Excel, I suggest a different formula:

    Data Range: G1:J100
L1: overallDate
L2: =SORT(UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,INDEX(G2:I100,SEQUENCE(99),{1,3}))&"</s></t>","//s")))
M2: =IF(L2="","", SUMIF($G$1:$G$100,L2,$H$1:$H$100)+SUMIF($I$1:$I$100,L2,$J$1:$J$100))

    Results from L2 will SPILL down as far as needed
    In L2 adjust the range reference and SEQUENCE count as appropriate for your data, or make them dynamic
    Formula in M2 will need to be copied down

    Edit: If you want an inclusive list of dates in the OverallDate column, consider:

    L2: =LET(dates,INDEX(G2:I100,SEQUENCE(99),{1,3}),MIN(dates)-1 + SEQUENCE(1+MAX(dates)-MIN(dates)))

    enter image description here