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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

GlennJ

New Member
Joined
Mar 26, 2018
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
Not sure if this is what your looking for, but you could add an extra column with this formula =IF(AND(E2>1,D2>1), C2, "") or =IF(AND(E2>1,D2>1), "WEEK 2", "") or you could select all the cells in column D and E and use conditional formatting with the same formula =IF(AND(E2>1,D2>1), C2, "") to highlight the number.
 
Last edited:

Shawn09

Board Regular
Joined
May 13, 2005
Messages
74
Not sure if this is what your looking for, but you could add an extra column with this formula =IF(AND(E2>1,D2>1), C2, "") or =IF(AND(E2>1,D2>1), "WEEK 2", "") or you could select all the cells in column D and E and use conditional formatting with the same formula to highlight the number.
Well column E is something I manually typed in to show the results I'm wanting from the formula. Thank you for the reply.
 

GlennJ

New Member
Joined
Mar 26, 2018
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
=IF(D2>1,2,1) or =IF(D2>1,2,"")
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,802

ADVERTISEMENT

How about:

Book2
ABCDE
1Personnel NumberEmployee Name Amount Pay FilePay Week
210015551Marlana Lee$ 35.2311
310015551Marlana Lee$ 70.4611
410015551Marlana Lee$ 206.3111
510015551Marlana Lee$ 35.2321
610015551Marlana Lee$ 70.4621
710015551Marlana Lee$ 206.3121
810015551Marlana Lee$ 47.8422
910015551Marlana Lee$ 71.6322
1010015552Nora Payne$ 283.5611
1110015552Nora Payne$ 283.5621
1210015553Jose Morales$ 370.0811
1310015553Jose Morales$ 44.3211
1410015553Jose Morales$ 370.0821
1510015553Jose Morales$ 398.8822
1610015553Jose Morales$ 44.3221
1710015553Jose Morales$ 94.4022
Sheet1
Cell Formulas
RangeFormula
E2:E17E2=IF(D2=1,1,IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2)=2,1,2))


Also, please consider using the XL2BB tool when posting a sample sheet. It's much easier for people to work with than looking at a picture and having to retype all the information manually. There's a link in my signature or in the reply box. It's quite easy to use.
 

Shawn09

Board Regular
Joined
May 13, 2005
Messages
74
=IF(D2>1,2,1) or =IF(D2>1,2,"")
Thanks for trying again but that formula just results in the same information that is in column D already. I'm assuming the only way to get this to work is to do an index/match combination looking at the personnel number, the amount and the pay file column. You're not going to be able to do it based on just column D. I just can't wrap my head around how yet.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,659
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

looking at your image, ignoring the yellow highlight , i cant see a way excel can do this
As D "Pay File" has 1 and 2 - BUT your column E does not have 2 where D has a 2
So how would excel know to ignore
 

Shawn09

Board Regular
Joined
May 13, 2005
Messages
74
How about:

Book2
ABCDE
1Personnel NumberEmployee Name Amount Pay FilePay Week
210015551Marlana Lee$ 35.2311
310015551Marlana Lee$ 70.4611
410015551Marlana Lee$ 206.3111
510015551Marlana Lee$ 35.2321
610015551Marlana Lee$ 70.4621
710015551Marlana Lee$ 206.3121
810015551Marlana Lee$ 47.8422
910015551Marlana Lee$ 71.6322
1010015552Nora Payne$ 283.5611
1110015552Nora Payne$ 283.5621
1210015553Jose Morales$ 370.0811
1310015553Jose Morales$ 44.3211
1410015553Jose Morales$ 370.0821
1510015553Jose Morales$ 398.8822
1610015553Jose Morales$ 44.3221
1710015553Jose Morales$ 94.4022
Sheet1
Cell Formulas
RangeFormula
E2:E17E2=IF(D2=1,1,IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2)=2,1,2))


Also, please consider using the XL2BB tool when posting a sample sheet. It's much easier for people to work with than looking at a picture and having to retype all the information manually. There's a link in my signature or in the reply box. It's quite easy to use.
Thank you. It's been a bit since I've been on here so wasn't aware of that feature. I will do so moving forward. Your formula is closer to what I need but not exactly as it doesn't recognize on Michelle Harper that the two $167.67's were on the week 1 file. I knew this was going to be a pain... not sure it's possible. But that was close and thanks so much for the reply!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,454
Office Version
  1. 365
Platform
  1. Windows
How about this tweak to Eric's formula
Excel Formula:
=IF(D2=1,1,IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2)>1,1,2))
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,802
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).
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,331
Messages
5,635,662
Members
416,871
Latest member
jbcpub

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