how to identify duplicate rows & staffs xfr frm one depart to anot
Hello,
I need urgent help for the following cases : - Staff No. Action/Reason Deptcode A1 0001 Pay chg 1234 A2 0001 Pay chg 1234 A3 0001 supr Chg 1234 A4 0001 Job chg 1239 A5 0005 Incentive chg 3456 A6 0005 Dept Chg 9888 A7 0005 Dept Chg 7865 1) Staff No.0001 has duplicate of Pay chg column 2) Both staffs have deptcode change row A4 for staff 0001 and row A6 & A7 for 0005 Please advice how to identify these. I have total of 6000+ rows to sort it out. Thanks in advanced |
how to identify duplicate rows & staffs xfr frm one depart to anot
One interp on your intents ..
Presume your table as posted in cols A to C, data from row2 down col A = Staff No. col B = Action/Reason col C = Deptcode Put in D2: =IF(COUNTA(A2:B2)<2,"",IF(SUMPRODUCT((A$2:A2=A2)*( B$2:B2=B2))1,"dup","")) Copy down as far as required to flag duplicates based on col A and col B. Freeze col D with an "in-place" copy n paste special as values, then apply autofilter on col D, choose: dup to filter out the duplicates -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Calculate Date range" wrote: Staff No. Action/Reason Deptcode A1 0001 Pay chg 1234 A2 0001 Pay chg 1234 A3 0001 supr Chg 1234 A4 0001 Job chg 1239 A5 0005 Incentive chg 3456 A6 0005 Dept Chg 9888 A7 0005 Dept Chg 7865 1) Staff No.0001 has duplicate of Pay chg column 2) Both staffs have deptcode change row A4 for staff 0001 and row A6 & A7 for 0005 Please advice how to identify these. I have total of 6000+ rows to sort it out. Thanks in advanced |
All times are GMT +1. The time now is 06:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com