Formula to identify duplicates

Shawn09

Board Regular
Joined
May 13, 2005
Messages
74
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
 

Attachments

  • Payroll Example.png
    Payroll Example.png
    77.7 KB · Views: 10

Shawn09

Board Regular
Joined
May 13, 2005
Messages
74
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
ABCDEF
1Personnel NumberEmployee Name Amount Pay FilePay Week
210015551Marlana Lee$ 35.23111
310015551Marlana Lee$ 70.46111
410015551Marlana Lee$ 206.31111
510015551Marlana Lee$ 35.23211
610015551Marlana Lee$ 70.46211
710015551Marlana Lee$ 206.31211
810015551Marlana Lee$ 47.84222
910015551Marlana Lee$ 71.63222
1010015552Nora Payne$ 283.56111
1110015552Nora Payne$ 283.56211
1210015553Jose Morales$ 370.08111
1310015553Jose Morales$ 44.32111
1410015553Jose Morales$ 370.08211
1510015553Jose Morales$ 398.88222
1610015553Jose Morales$ 44.32211
1710015553Jose Morales$ 94.40222
1810097658Michelle Harper$ 46.60111
1910097658Michelle Harper$ 167.67111
2010097658Michelle Harper$ 167.67111
2110097658Michelle Harper$ 507.85111
2210097658Michelle Harper$ 46.60211
2310097658Michelle Harper$ 167.67211
2410097658Michelle Harper$ 167.67211
2510097658Michelle Harper$ 507.85211
2610097658Michelle Harper$ 177.92222
2710097658Michelle Harper$ 177.92221
Sheet1
Cell Formulas
RangeFormula
E2:E27E2=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:F27F2=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!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,770
Which formula did you try? The E2 formula from post 10 handles that situation.
 

Shawn09

Board Regular
Joined
May 13, 2005
Messages
74
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top