Formula to identify duplicates

[Shawn09]

I have an issue I need some help with. Our payroll files are weird and apparently there is no way to break out week 1 and week 2 once week 2 has ended. I get week 1 by itself and then the next file is week 1 and week 2 combined but I need to see just the payroll that came on week 2. I'm able to combine the files so I can identify the data that came week 1 and what came on the week 2 file. In this example, anything in the Pay File column indicates whether the data came from the week 1 file or the combined week 2 file. So in column E, I would like a formula to identify the pay week. So in this example, the yellow highlighted cells would be the only ones that came in week 2. Hope that makes sense.

Thanks for any help available!

Shawn

 

[Shawn09]

This shows why XL2BB helps, I got tired of typing in the data before I got to that case. Also, since the amounts don't necessarily come in order, we need to look at the whole column, not just the values above the current row. How about:

Book2
	A	B	C	D	E	F
1	Personnel Number	Employee Name	Amount	Pay File	Pay Week
2	10015551	Marlana Lee	$ 35.23	1	1	1
3	10015551	Marlana Lee	$ 70.46	1	1	1
4	10015551	Marlana Lee	$ 206.31	1	1	1
5	10015551	Marlana Lee	$ 35.23	2	1	1
6	10015551	Marlana Lee	$ 70.46	2	1	1
7	10015551	Marlana Lee	$ 206.31	2	1	1
8	10015551	Marlana Lee	$ 47.84	2	2	2
9	10015551	Marlana Lee	$ 71.63	2	2	2
10	10015552	Nora Payne	$ 283.56	1	1	1
11	10015552	Nora Payne	$ 283.56	2	1	1
12	10015553	Jose Morales	$ 370.08	1	1	1
13	10015553	Jose Morales	$ 44.32	1	1	1
14	10015553	Jose Morales	$ 370.08	2	1	1
15	10015553	Jose Morales	$ 398.88	2	2	2
16	10015553	Jose Morales	$ 44.32	2	1	1
17	10015553	Jose Morales	$ 94.40	2	2	2
18	10097658	Michelle Harper	$ 46.60	1	1	1
19	10097658	Michelle Harper	$ 167.67	1	1	1
20	10097658	Michelle Harper	$ 167.67	1	1	1
21	10097658	Michelle Harper	$ 507.85	1	1	1
22	10097658	Michelle Harper	$ 46.60	2	1	1
23	10097658	Michelle Harper	$ 167.67	2	1	1
24	10097658	Michelle Harper	$ 167.67	2	1	1
25	10097658	Michelle Harper	$ 507.85	2	1	1
26	10097658	Michelle Harper	$ 177.92	2	2	2
27	10097658	Michelle Harper	$ 177.92	2	2	1
Sheet1

Cell Formulas
Range	Formula
E2:E27	E2	=IF(D2=1,1,IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2,D$2:D2,2)>COUNTIFS(A:A,A2,C:C,C2,D:D,1),2,1))
F2:F27	F2	=IF(D2=1,1,IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2)>1,1,2))

I put Fluff's formula in column F. It seems to work fine, except for the case where the second week has 2 matching values not in the first week (I added row 27 to show that).

Yeah it's sooo close. The issue appears to be when there is a duplicate dollar in week 2, it's resulting in a 1. Unfortunately this will happen a lot since employees will work exactly 8 hours a day on the same pay rate so you'll have two or more days with the same dollar amount. I really appreciate you guys trying!

 

[Eric W]

Which formula did you try? The E2 formula from post 10 handles that situation.

 

[Shawn09]

Which formula did you try? The E2 formula from post 10 handles that situation.

You are correct! I totally missed that formula! Thank you all for your help! This will work great for what I'm trying to do!