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

INPUT DATA
Item	Date		Time/AM-PM
Apple	4/5/2019		AM
Banana	4/5/2019		AM
Carrot	4/5/2019		AM
WaterMelon	4/5/2019		AM
Pineapple	4/5/2019		AM
Apricot	4/5/2019		AM
Apple	4/5/2019		AM
Apple	4/6/2019		AM
Banana	4/6/2019		AM
Carrot	4/6/2019		AM
WaterMelon	4/6/2019		AM
Pineapple	4/6/2019		AM
Banana	4/6/2019		AM
Apple	4/6/2019		AM
Carrot	4/6/2019		AM
WaterMelon	4/5/2019		PM
Apricot	4/5/2019		PM
Banana	4/5/2019		PM
WaterMelon	4/5/2019		PM

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Column G Column H Column I

RESULT DATA AUTOMATICALLY ADD WITH NEXT CELL
Date	Dup Items Count	Time
4/5/2019	2	AM
4/5/2019	1	PM
4/6/2019	3	AM

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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)

Item	Date	Time/AM-PM	Dup Item Count
Apple	4/5/2019	AM	0.5
Apple	4/5/2019	AM	0.5
Apple	4/6/2019	AM	0.5
Apple	4/6/2019	AM	0.5
Apricot	4/5/2019	AM	0
Apricot	4/5/2019	PM	0
Banana	4/5/2019	AM	0
Banana	4/5/2019	PM	0
Banana	4/6/2019	AM	0.5
Banana	4/6/2019	AM	0.5
Carrot	4/5/2019	AM	0
Carrot	4/6/2019	AM	0.5
Carrot	4/6/2019	AM	0.5
Pineapple	4/5/2019	AM	0
Pineapple	4/6/2019	AM	0
WaterMelon	4/5/2019	AM	0
WaterMelon	4/5/2019	PM	0.5
WaterMelon	4/5/2019	PM	0.5
WaterMelon	4/6/2019	AM	0

<tbody>
</tbody>

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

Date	Time/AM-PM	Total
4/5/2019	AM	1
4/5/2019	PM	1
4/6/2019	AM	3

<tbody>
</tbody>