Removing multiple lines based on first field

[claver]

Hi

Can anyone help with a simple macro that will take 3 or 4 columns Excel files … and remove all the rows that has multiple first field. Example :

123, 34234, 2232,2233,
123, 43294, 3233, 32
124, 5442, 4445, 4332
124, 44432, 54322, 4333
124, 4345, 4344, 4332
125, 4343,43215, 4334,
125, 435, 3443, 4345

And return a file with only :
123, 34234, 2232,2233,
124, 5442, 4445, 4332
125, 4343,43215, 4334,

Etc….. I have files with 20000+ lines….

Much appreciated…

 

[pmover]

claver,

rather than write a macro (VBA code), how about using the Auto Filter capabilities within Excel. Include a label for each column (top row) and then select the Data, Filter, Auto Filter. You can then filter out repeated rows . . . perhaps writing a macro will be faster, but something for you to think about and attempt.

be sure all the data is continuous (no open rows between data) and the same for columns. If there are non-continuous rows/columns, then highlight all the data before executing the filter commands.

Good Luck!
-pmover

 

[claver]

Thanks pmover ... I will give that a go

 

[joerd]

Also, you may need a "helper" column with a formula like [tt]=COUNTIF(A:A,A1)[/tt] in every row, which will show you the number of occurences of each entry in column A. Then you can filter on the formula result being equal to 1 to remove duplicates. Or sort and throw away rows where it is larger than 1.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.