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

%3CLINGO-SUB%20id%3D%22lingo-sub-2215963%22%20slang%3D%22en-US%22%3ENumber%20Duplicate%20Values%20(in%20Chronological%20Order)%20Based%20on%20DateTime%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2215963%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20first%20post!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20attempting%20to%20build%20a%20formula%20in%20Excel%20which%20will%20count%20the%20number%20of%20duplicates%20in%20the%20%5BLoad%20ID%5D%20column%20while%20respecting%20the%20chronological%20order%20in%20the%20%5BTender%20Request%20Creation%20Date%2FTime%5D%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20image%2C%20columns%20A%20through%20C%20represent%20my%20current%20results%20using%20a%20CountIF%20formula.%20Columns%20E%20through%20G%20represent%20my%20desired%20outcome.%20Pay%20special%20attention%20to%20the%20difference%20in%20order%20between%20columns%20B%20and%20F.%20This%20is%20an%20example%20of%20a%20single%20Load%20ID.%20There%20are%20almost%208500%20unique%20values%20and%201900%20duplicates%20in%20my%20attached%20file.%20In%20some%20instances%2C%20the%20Load%20ID%20can%20be%20duplicated%20up%20to%207%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20require%20any%20additional%20information.%20Thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Numbered%20Duplicates.png%22%20style%3D%22width%3A%20721px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F264665i0E2611ADA6587260%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Numbered%20Duplicates.png%22%20alt%3D%22Numbered%20Duplicates.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2215963%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2216185%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20Duplicate%20Values%20(in%20Chronological%20Order)%20Based%20on%20DateTime%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2216185%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F999739%22%20target%3D%22_blank%22%3E%40FlowEZ%3C%2FA%3E%26nbsp%3BAll%20you%20need%20to%20do%20is%20a%20custom%20sort.%20First%20on%20Load%20(ascending)%20then%20on%20Date%2FTime%20(ascending).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-03-17%20at%2005.50.41.png%22%20style%3D%22width%3A%20181px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F264710i51680054B513FDED%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-03-17%20at%2005.50.41.png%22%20alt%3D%22Screenshot%202021-03-17%20at%2005.50.41.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E(Screenshot%20taken%20in%20Excel%20for%20Mac%2C%20but%20it's%20similar%20in%20the%20Windows%20version.)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-03-17%20at%2005.46.39.png%22%20style%3D%22width%3A%20397px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F264709i3F830CCDE9AAB0CF%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-03-17%20at%2005.46.39.png%22%20alt%3D%22Screenshot%202021-03-17%20at%2005.46.39.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERE-attaching%20your%20file%20with%20the%20sorting%20in%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular 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