Find duplicates as per Date and AM/PM

[atif_ar]

Dears,

Please help me on this. I want to find duplicates in Column A with Date in B and automatically enter date in Column F with Duplicate no in Column G and Time AM/PM in Column H. And then automatically move to next cell with same date if AM/PM and if not then new Date AM/PM with duplicate nos. I hope you understand

 

[CyrusTheVirus]

Hi atif,

It is almost always best to include a sample of your data both your starting point and some expected results so that the viewers know exactly what you are wanting.

With that said, can you please post an example of your data in columns A and B, and then show what you are expecting your results to be in columns G and H?

 

[atif_ar]

Thanks a lot for your reply. Dear please find data below

---

---

---

Column A Column B Column C

[TABLE="width: 280"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 4"]INPUT DATA[/TD]
[/TR]
[TR]
[TD]Item[/TD]
[TD] Date[/TD]
[TD][/TD]
[TD] Time/AM-PM[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD][/TD]
[TD] AM[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD][/TD]
[TD] AM[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD="align: right"]4/5/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]WaterMelon[/TD]
[TD="align: right"]4/5/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD="align: right"]4/5/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Apricot[/TD]
[TD="align: right"]4/5/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]4/5/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]4/6/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]4/6/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD="align: right"]4/6/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]WaterMelon[/TD]
[TD="align: right"]4/6/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD="align: right"]4/6/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]4/6/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]4/6/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD="align: right"]4/6/2019 [/TD]
[TD][/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]WaterMelon[/TD]
[TD="align: right"]4/5/2019 [/TD]
[TD][/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]Apricot[/TD]
[TD="align: right"]4/5/2019 [/TD]
[TD][/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]4/5/2019 [/TD]
[TD][/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD]WaterMelon[/TD]
[TD="align: right"]4/5/2019 [/TD]
[TD][/TD]
[TD]PM[/TD]
[/TR]
</tbody>[/TABLE]


Column G Column H Column I

[TABLE="width: 354"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]RESULT DATA AUTOMATICALLY ADD WITH NEXT CELL[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Dup Items Count[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD="align: right"]4/5/2019[/TD]
[TD] 2[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD="align: right"]4/5/2019[/TD]
[TD] 1[/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD="align: right"]4/6/2019[/TD]
[TD] 3[/TD]
[TD]AM[/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

Could try the below formula, and then pivot the data set afterwards.

D2 (copied down):

=IF(COUNTIFS($A$2:$A$65536,A2,$B$2:$B$65536,B2,$C$2:$C$65536,C2)<=1,0,0.5)

[TABLE="width: 337"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Date[/TD]
[TD]Time/AM-PM[/TD]
[TD]Dup Item Count[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]4/6/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]4/6/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Apricot[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Apricot[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]PM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]PM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]4/6/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]4/6/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD="align: right"]4/6/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD="align: right"]4/6/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD="align: right"]4/6/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]WaterMelon[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]WaterMelon[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]PM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]WaterMelon[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD]PM[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]WaterMelon[/TD]
[TD="align: right"]4/6/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Pivot Results (I didn't see 4/5 AM as having 2, nor did it come up as having 2 with the formula):

[TABLE="width: 262"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Time/AM-PM[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD="align: right"]4/5/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4/5/2019[/TD]
[TD]PM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4/6/2019[/TD]
[TD]AM[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]