Possible VB code to lookup and remove unwanted data
 

I've got around 53k rows of data in an excel worksheet, labelled Data. Each
row has a customer ID. I've put all the data into a pivot table on another
worksheet, which is how I want to display the results. However, I don't need
data for all customer ID's. In a separate worksheet, I've got a list of
unique customer ID's that I need information on.
Is there any way (perhaps VB code/macro?) that I can look in the datafile of
53k rows, find a customer ID, compare that with the unique list of ID's and
if it's in there then ignore it (not do anything with it), and if it isn't in
there, remove it from the datafile?

Possible VB code to lookup and remove unwanted data
 

One simple procedure using autofilter on a helper col
to effectively deal with this ..

Assume the unique customer IDs are in a sheet: x
running in A2 down

In sheet: Data, assume the cust IDs are running in A2 down
Insert a new col A
Put in A2:
=ISNUMBER(MATCH(B2,x!A:A,0))
Copy A2 down to the last row of data in col B
Col A will flag all matching ids as TRUE, FALSE otherwise

The rows with FALSE are the ones to be deleted
To do this, just apply autofilter on col A, filter out: FALSE
Then select all the filtered rows, Right-click Delete Row
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sarah (OGI)" wrote:
I've got around 53k rows of data in an excel worksheet, labelled Data. Each
row has a customer ID. I've put all the data into a pivot table on another
worksheet, which is how I want to display the results. However, I don't need
data for all customer ID's. In a separate worksheet, I've got a list of
unique customer ID's that I need information on.
Is there any way (perhaps VB code/macro?) that I can look in the datafile of
53k rows, find a customer ID, compare that with the unique list of ID's and
if it's in there then ignore it (not do anything with it), and if it isn't in
there, remove it from the datafile?

Possible VB code to lookup and remove unwanted data
 

Max

That's great, thank you so much for your help (and no vb code in sight!!)

Cheers
Sarah

"Max" wrote:

One simple procedure using autofilter on a helper col
to effectively deal with this ..

Assume the unique customer IDs are in a sheet: x
running in A2 down

In sheet: Data, assume the cust IDs are running in A2 down
Insert a new col A
Put in A2:
=ISNUMBER(MATCH(B2,x!A:A,0))
Copy A2 down to the last row of data in col B
Col A will flag all matching ids as TRUE, FALSE otherwise

The rows with FALSE are the ones to be deleted
To do this, just apply autofilter on col A, filter out: FALSE
Then select all the filtered rows, Right-click Delete Row
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sarah (OGI)" wrote:
I've got around 53k rows of data in an excel worksheet, labelled Data. Each
row has a customer ID. I've put all the data into a pivot table on another
worksheet, which is how I want to display the results. However, I don't need
data for all customer ID's. In a separate worksheet, I've got a list of
unique customer ID's that I need information on.
Is there any way (perhaps VB code/macro?) that I can look in the datafile of
53k rows, find a customer ID, compare that with the unique list of ID's and
if it's in there then ignore it (not do anything with it), and if it isn't in
there, remove it from the datafile?

Possible VB code to lookup and remove unwanted data
 

Welcome, Sarah.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sarah (OGI)" wrote in message
...
Max

That's great, thank you so much for your help (and no vb code in sight!!)

Cheers
Sarah