Number Duplicate Values (in Chronological Order) Based on DateTime Column

Visitor

My first post!

 

I am attempting to build a formula in Excel which will count the number of duplicates in the [Load ID] column while respecting the chronological order in the [Tender Request Creation Date/Time] column.

 

In the attached image, columns A through C represent my current results using a CountIF formula. Columns E through G represent my desired outcome. Pay special attention to the difference in order between columns B and F. This is an example of a single Load ID. There are almost 8500 unique values and 1900 duplicates in my attached file. In some instances, the Load ID can be duplicated up to 7 times.

 

Let me know if you require any additional information. Thanks!

 

 

Numbered Duplicates.png

3 Replies

@FlowEZ All you need to do is a custom sort. First on Load (ascending) then on Date/Time (ascending).

Screenshot 2021-03-17 at 05.50.41.png

(Screenshot taken in Excel for Mac, but it's similar in the Windows version.)

 

Screenshot 2021-03-17 at 05.46.39.png

RE-attaching your file with the sorting in it.

 

@FlowEZ 

 

I would like to suggest two different formula, one is finding the duplicates & another shows the frequency of the duplicates.

 

=IF(COUNTIF(A:A,A2)>1,"Duplicate",COUNTIF(A:A,A2))

 

=COUNTIF($A$2:$A$10236,A2)

 

  • Adjust cell references in the formula as needed.

@FlowEZ 

As variant, to have filtered result as

image.png

formulas are

in E2:
=SORTBY( Tbl_TenderData[[Load ID]:[Tender Request Creation Date/Time]],
         Tbl_TenderData[Load ID],1,
         Tbl_TenderData[Tender Request Creation Date/Time],1
)

in G2:
=ROW(E2#)-ROW($E$1)-XMATCH(INDEX($E$2#,0,1),INDEX($E$2#,0,1))+1