excel - Find duplicates and delet them while conserving the rest of the data and associated column -

i have excel file containing list of articles , references need work arranged ways:

matched:       article:  barcode:  code: aa01123        aa01123   23459544  4533   aa06789        kk01234   30493282  45643 bb20443        aa06789   12304123  453 cc30313        bb20443   12123434  646453 dd22890        cc30313   12341344  433245                jh01241   12312312  4324 /end           dd22890   34343442  22244                gf06789   12341434  24621                xy12306   12341213  2344                 /etc../ 

the matched column articles have been treated, , article contain complete list of both article have been treated (so ones in matched column) , 1 still need work on. i'd substrate 1 in matched complete list remains ones have treat.

i need function find duplicate between matched , article , delete them corresponding barcode , code. don't need sort other column barcode , code need keep them aligned corresponding articles.

it give this:

matched:     article:  barcode:  code:              kk01234   19341344  433245              jh01241   17312312  4324              xy12306   12341213  2344              gf06789   12341434  24621 

it delete 1st column since dont need put clarity.

my problem because matched , article dont have same lenght (about 700 , 1300 lines) cannot find duplicate through filtre, , wouldn't keep rest sorted in order.

i went far finds duplicate , doesn't delete corresponding data.

=if(isna(match(a1,c:c,0)),””,index(c:c,match(a1,c:c,0)))  

if show me how improve function or find 1 need that'd great!

something ideal situation possibly require vba, automate system , delete rows, not suggesting presume unfamiliar it. instead suggest use 2 data tables: 1 hosts of raw data, , 1 holds rows not yet 'dealt with'.

setting raw data table

first assume raw data table (starting 3 columns 'article' 'bar code' , 'code' in columns a, b, , c) on sheet1. assume list of 'matched' items somewhere else - let's type in manually on column of sheet2. ideally matched column should autopopulate based on have done, can't recommend there without knowing how 'deal with' each row.

next, add in new column right of raw data column - let's call column d. check whether article on each row has yet been 'dealt with'. put in d2 , copy down:

=match(a2, sheet2!a:a, 0) 

this show #n/a if item has not yet been matched, , otherwise show row number finding spot on sheet2 article number entered. can use create new ordered list skip items entered (ie: match function returns other-than #n/a). put in e2 , copy down (leave e1 blank):

=if(iserror(d2),e1+1,e1) 

setting results tab

now on results tab (let's sheet3), create index counts 1 total number of unresolved items, , use index pull columns sheet1 based on column e there. sheet3 column follows (enter in a2 , leave a1 blank):

=if(a1<max(sheet1!e:e,a+1,"") 

then create headers in b1, c1, , d1, pull in data sheet 1. index function pull in desired row of data, , match function find appropriate row pull from. put in b2, , drag down/to right many rows , columns have data:

=index(sheet1!a:a, match($a2, sheet1!$e:$e, 0)) 

because want point data exists, can wrap whole function in check determine whether column has yet run out of numbers:

=if($a2>0,index(sheet1!a:a, match($a2, sheet1!$e:$e, 0)),"") 

and that's it. have ordered list of items have not yet been matched, , have full listing of original raw data audit trail. step left make 'matched items' list in sheet2 autopopulate, depend on how determine when item matched - perhaps need manual [ie: if completing work outside of excel].