CODEWITHSUNDEEP
excelexcel-formula
Oleg0341
Oleg0341

Reputation: 275

How can I sort two fields in the Table, that one name corresponds to one date

How can I sort two fields in the Table, to remove double names for one date. I need formula. I know one way, is to convert date to the text type and then to concatenate these fields (A2&B2) and then remove duplicate with Button "Remove duplicates". But my data changes dynamically from csv and I don't want to remove duplicate manual every time. Thanks.

My example of file is here: https://1drv.ms/x/s!AgfvDQ65okq09H4ZfOCrSrNZPtXT?e=aQnZTU

enter image description here

Upvotes: 1

Views: 69

Answers (2)

Oleg0341
Oleg0341

Reputation: 275

As Hooded 0ne advised me I did the next:

  1. Formula for C2: =TEXT(A2;"dd.MM.yyyy")&B2 [to concatenate date we need to convert it to the text type]

  2. Formula for D2: =IF(COUNTIF($F$2:$F2; $F2)>1; "Duplicate"; "Unique")

  3. Sort D Column by text filter containing Unique

  4. And if you want your table updated dynamically in any changes with TextFilter add Macros for the Sheet:

    Private Sub Worksheet_Change(ByVal Target As Range) Sheets("SheetName").AutoFilter.ApplyFilter

    End Sub

Upvotes: 0

JMP
JMP

Reputation: 4467

You only need to use just UNIQUE to concatenate two or more ranges and remove duplicates:

=UNIQUE(A2:A10 & B2:B10)

(Example 3 in link)

Upvotes: 1

Related Questions