Duplicate removal via macro
Posted by Pantus Monkeeto on January 02, 2002 8:57 PM
I gots me a sheet with many lines of data some of these lines contain multiple information which i need to remove.
does someone know the code that i would need to use to check rows H to N ie H,I,J,K,L,M,N and if any of the rows contain the exact same data in these coloumns then the whole row with the duplicate information is deleted leaving me with a sheet that has no duplicate information in rows H,I,J,K,L,M and N.
Cheers
Pantus
Posted by Paul N on January 03, 2002 7:14 AM
Its not actually a MACRO, however, I do this many times. First of all, if your data must stay in the order it is in, you first insert a blank column and then put the row number in that column with the formula =ROW(). Then you MUST copy that entire column, and then PASTE SPECIAL VALUES over it. This will replace the formula with the actual row value. Now to eliminate the duplicates, insert a new column and then code a simple if statement such as, =IF(OR(H2=I2,H2=J2,H2=K2,H2=L2,H2=M2,H2=N2),1,0)
If it is TRUE, display a 1, else display a 0.
Sort your spreadsheet on this new column which will gather all of the duplicates together. Then delete all of these rows. You can then remove the column and re-sort it back to its original order.
Hope it helps.
Paul
Posted by Paul N on January 03, 2002 7:30 AM
Sorry about that. I misread your question. But never fear... Here we go. Insert the row number as I said before with the ROW() and then paste special to remove the formula... Now, Sort your sheet on Column H... This will group all of your duplicates together. Insert a new column and code your if statement; =IF(H2=H1,1,0)
You don't specify in your request, however, this IF statement will leave ONE of the duplicates in your worksheet. If you don't want ANY of the duplicate rows left, you would code; =IF(OR(H2=H1,H1=H2),1,0)
What this means is, if A1=A, A2=B, A3=B, A4=C when you use the first option, your result will be 3 rows, A1,A2,A4... The second if statement will leave you with 2 rows, A1,A4
Once you get your if statement in, copy and paste special the values to eliminate the formula, then sort on that column to group all of the rows you want to delete together. Then delete them all. Then you'll have to do the same for the other rows.
Hope this helps.
Paul
