I have up to about 5000 rows of data on a sheet, it is made up of original
and updated information about customers.

Column A is an ID number. What I want to do, by code, is to delete the
single entry customers (those who have no updated information).

I have sorted by column A to bring both original and updated together but am
now stuck.

I have tried inserting a column (A) and putting the following in to identify
the ones I want to keep but cannot figure out how to delete the remainder:

=IF(COUNTIF($B:$B,B2)1,2,1)

Thanks in advance.

Gareth

click in B1
Data=filter=Autofilter

this will put a dropdown in all the columns of your data. Select the
dropdown in the column that has your formula

Select 1
now only rows with a 1 will be visible.

Select all these rows by using the left row labels. Select everything but
row 1, which I assume is the header row.

Do Edit=Delete

this will delete only the visible data.

Do Data=filter=Autofilter to remove the filter

You should now have only the rows that had multiple records for the ID.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
I have up to about 5000 rows of data on a sheet, it is made up of original
and updated information about customers.

Column A is an ID number. What I want to do, by code, is to delete the
single entry customers (those who have no updated information).

I have sorted by column A to bring both original and updated together but
am
now stuck.

I have tried inserting a column (A) and putting the following in to
identify
the ones I want to keep but cannot figure out how to delete the remainder:

=IF(COUNTIF($B:$B,B2)1,2,1)

Thanks in advance.

Gareth

Tom
I can manage this 'manually' as you describe, but as I said I want to do it
using code.
Gareth

"Tom Ogilvy" wrote in message
...
click in B1
Data=filter=Autofilter

this will put a dropdown in all the columns of your data. Select the
dropdown in the column that has your formula

Select 1
now only rows with a 1 will be visible.

Select all these rows by using the left row labels. Select everything but
row 1, which I assume is the header row.

Do Edit=Delete

this will delete only the visible data.

Do Data=filter=Autofilter to remove the filter

You should now have only the rows that had multiple records for the ID.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
I have up to about 5000 rows of data on a sheet, it is made up of
original
and updated information about customers.

Column A is an ID number. What I want to do, by code, is to delete the
single entry customers (those who have no updated information).

I have sorted by column A to bring both original and updated together
but
am
now stuck.

I have tried inserting a column (A) and putting the following in to
identify
the ones I want to keep but cannot figure out how to delete the
remainder:

=IF(COUNTIF($B:$B,B2)1,2,1)

Thanks in advance.

Gareth

Gareth,

Do it once the manual way. Record a macro while you are doing it.

Massage the finished code and you have it always.

steve

"Gareth" wrote in message
...
Tom
I can manage this 'manually' as you describe, but as I said I want to do
it
using code.
Gareth

"Tom Ogilvy" wrote in message
...
click in B1
Data=filter=Autofilter

this will put a dropdown in all the columns of your data. Select the
dropdown in the column that has your formula

Select 1
now only rows with a 1 will be visible.

Select all these rows by using the left row labels. Select everything
but
row 1, which I assume is the header row.

Do Edit=Delete

this will delete only the visible data.

Do Data=filter=Autofilter to remove the filter

You should now have only the rows that had multiple records for the ID.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
I have up to about 5000 rows of data on a sheet, it is made up of
original
and updated information about customers.

Column A is an ID number. What I want to do, by code, is to delete
the
single entry customers (those who have no updated information).

I have sorted by column A to bring both original and updated together
but
am
now stuck.

I have tried inserting a column (A) and putting the following in to
identify
the ones I want to keep but cannot figure out how to delete the
remainder:

=IF(COUNTIF($B:$B,B2)1,2,1)

Thanks in advance.

Gareth

My problem is that there are not always the same number of records so I
don't know what range to delete.

Any suggestions?

"steve" wrote in message
...
Gareth,

Do it once the manual way. Record a macro while you are doing it.

Massage the finished code and you have it always.

steve

"Gareth" wrote in message
...
Tom
I can manage this 'manually' as you describe, but as I said I want to do
it
using code.
Gareth

"Tom Ogilvy" wrote in message
...
click in B1
Data=filter=Autofilter

this will put a dropdown in all the columns of your data. Select the
dropdown in the column that has your formula

Select 1
now only rows with a 1 will be visible.

Select all these rows by using the left row labels. Select everything
but
row 1, which I assume is the header row.

Do Edit=Delete

this will delete only the visible data.

Do Data=filter=Autofilter to remove the filter

You should now have only the rows that had multiple records for the
ID.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
I have up to about 5000 rows of data on a sheet, it is made up of
original
and updated information about customers.

Column A is an ID number. What I want to do, by code, is to delete
the
single entry customers (those who have no updated information).

I have sorted by column A to bring both original and updated
together
but
am
now stuck.

I have tried inserting a column (A) and putting the following in to
identify
the ones I want to keep but cannot figure out how to delete the
remainder:

=IF(COUNTIF($B:$B,B2)1,2,1)

Thanks in advance.

Gareth