Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detecting particular BG Color in one cell and duplicate it in anot | Excel Discussion (Misc queries) | |||
Identify duplicate rows | Excel Discussion (Misc queries) | |||
Need to identify duplicate entries in a Table | Excel Worksheet Functions | |||
How do I identify duplicate numbers in Excel? | Excel Discussion (Misc queries) | |||
identify duplicate enteries | Excel Worksheet Functions |