Taking data from one table then finding and deleting in another

**lifes_cinders** · 10-02-2006, 06:26 AM

Hello,

This may be a simple macro answer but I have two tables.
One contains a user num which I must find and delete in another.
I have over 10,000 of these and do not believe I have to find and delete all individually.

Basically I'm wanting to find data from one table and deleting it from another.....
Anyone help me please?

**funkymonkUK** · 10-02-2006, 06:29 AM

is there one column with the value you are looking for that needs to be deleted?

ie

col1number - col2 value
1 - 4546
2 - 78998
1 - 5462

so what you want to do is find all the value 1 in col1 and delete it right? if so let me know there it will be quiet a simple macro which you would need to implement

**lifes_cinders** · 10-02-2006, 06:32 AM

Ok, i have 2 tables...

table 1---------------------------table 2
name.id.age----------------------name.id.age
bob.325.25-----------------------bob.325.25
fred.658.30 ---------------------- fred.658.30
-----------------------------------james.765.40

I wish to use all ID's from table 1 and find and delete in table 2

**funkymonkUK** · 10-02-2006, 06:41 AM

so in other words you would only have james.765.40 in table two right?

are both these tables in the same workbook?

**lifes_cinders** · 10-02-2006, 06:44 AM

They are not in the same work book, but i can make a separate sheet and put them in if needs be.

but yes i wish that all the users in one table be found and deleted in another table.

**funkymonkUK** · 10-02-2006, 06:48 AM

no need to create another sheet.

give me a few minutes. will have something ready for you

**lifes_cinders** · 10-02-2006, 06:54 AM

Thank you

**funkymonkUK** · 10-02-2006, 06:58 AM

do you need the results to be in table 2 or a separate table?

**lifes_cinders** · 10-02-2006, 06:59 AM

At the moment its a working progress but preferably table 2

**funkymonkUK** · 10-02-2006, 07:24 AM

how many rows do you normally have do you have large amount of rows?

**lifes_cinders** · 10-02-2006, 07:37 AM

I have over a thousand in table 1 to delete in table 2

**funkymonkUK** · 10-02-2006, 08:43 AM

as promised shout if you need help.

1. Extract all the workbooks
2. run compiler.xls
3. On sheet "menu", in cells b5 and b6 type in the sheet name that your table1 is on and then do the same for your table 2. For this example you leave it as is which is sheet1 in both cells only change it if you are using it with your own table 1 and table 2.
4. click on the button on the sheet "Menu"
5. Table 2 is now only items that were not on table 1 and the old table 2

have fun!!!

**lifes_cinders** · 10-02-2006, 08:56 AM

Ok, Thank you, just not quite following the instructions of this thing.

I dont want to seem like an idiot but i cant get where im supposed to put what....

**funkymonkUK** · 10-02-2006, 09:00 AM

ooops after reading it it did not make sense to me. I have revised it but where about are you stuck?

**lifes_cinders** · 10-02-2006, 09:03 AM

Just what names i am putting where or if im copying the info from my original tables into the new compiler table....

I'm sorry, really grateful for the help i just cant see what im doing

**lifes_cinders** · 10-02-2006, 09:05 AM

The button isnt actually a button either....

**funkymonkUK** · 10-02-2006, 09:13 AM

ok for an example.

click on the compiler

leave the sheet names

click on the button.

Now find where you have extracted the other two workbooks Table1.xls and table2.xls

click on table1.xls and press ok
click on table2.xls and press ok

you should get a message saying it is ready.

In summary, what this does is opens the two files which you have selected then it copys all figures in table1 on sheet1 then does the same for table 2. It then closes the workbooks down. Pastes the data so that it has a copy of your data and then it checks table 2 to see if it has anything table 1 has it then deletes the line.

If you want to use on your own data. then change the name of the sheets which the table 1 and table 2 are and then click the button then select your two files and should do the same thing as long as you are searching for something in column A else it would need to change the the column you would like to search

**funkymonkUK** · 10-02-2006, 09:14 AM

Originally Posted by lifes_cinders

The button isnt actually a button either....

there is only one button on the menu sheet which has the writting " select your two files....."

**lifes_cinders** · 10-02-2006, 09:21 AM

The button doesnt work as a button though. I have to double click and it takes me to the macro code...

**funkymonkUK** · 10-02-2006, 09:28 AM

oops its in design mode. did you enable macros?

click on view/toolsbars then click on Control toolbox. you should see something either on your toolbars or as a little floating window. you should see an icon which looks like a geomitry set (blue icon) that is pressed in click on it now try click on the button you should be able to single click on it.

**lifes_cinders** · 10-02-2006, 09:35 AM

I get a runtime error asking if i want to debug.....

**funkymonkUK** · 10-02-2006, 09:38 AM

Originally Posted by lifes_cinders

I get a runtime error asking if i want to debug.....

that is strange it works on my system what excel version you got? what is the exact error message can you click on debug you should see a yellow high lighted line can you copy and paste it here.

I have excel 2000.