Excel Issue

[Audicab]

Hopefully somebody can help me as I am going mad.

I am trying to de-dupe a list in Excel of our prospects. I have a spreadsheet of 8000 rows which includes multiple contacts at the same company.

I then have a list of 1000 companies that I need to remove all contacts from the spreadsheet.

I can't just de-dupe it as it will remove all companies, not just those on the list of 1000, unless they have just 1 contact.

The only way I have thought of is to manually go through and match them up but this is obviously time consuming and open to errors.

I hope I have explained that and if anybody has any help that would be great.

Thanks

[anonymous-user]

You may need to explain better as the above just looks like a vlookup to your 1000 company remove list to add a column with 'delete', then simple filter by 'delete', delete, which then removes the 1000 companies

[Audicab]

wsurfa said:

You may need to explain better as the above just looks like a vlookup to your 1000 company remove list to add a column with 'delete', then simple filter by 'delete', delete, which then removes the 1000 companies

I am hoping it is very simple, would a vlookup delete all instances where my main database matches any on the 1000.

To explain a little, my prospect spreadsheet contains 8000 peoples names at 2000 companies. I then have a list I am trying to match which just contains 1000 company names.

I just need to identify all contacts from the prospect list at the 1000 new companies.

[djfaulkner]

Which version of Excel are you using?

Could try something like this:

Select the prospect sheet range and name it prospectnames
Then select the companyname column in the other sheet (eg E), Choose conditional formatting, formula is/based on formula

=CountIF(prospectnames, E1)>0

Copy it down.....
In thoery it will highlight the duplicate company names

Edited by djfaulkner on Wednesday 27th August 13:32

[Audicab]

2010

[paul.deitch]

What is the unique identifier for the 1000 new prospect companies?

[mrmr96]

Have I got this right:

Sheet 1 contains 8000 rows, with more than one row per company (one for each contact).
Sheet 2 contains 1000 rows, with one row per company (?) of lost customers.

You need to remove all entries on Sheet 1 if the company exists on sheet 2?

[Audicab]

mrmr96 said:

Have I got this right:

Sheet 1 contains 8000 rows, with more than one row per company (one for each contact).
Sheet 2 contains 1000 rows, with one row per company (?) of lost customers.

You need to remove all entries on Sheet 1 if the company exists on sheet 2?

Yes exactly this, thank you.

In answer to the question above by Paul, the unique identifier is the company name

[davepoth]

Put the following (I think; I don't have excel at home to test) in a column to the right of the data in the 8000 field sheet.

=if(isna(vlookup([cell in 8000 field sheet],[area in 1000 field sheet], [relative column number in area in 1000 field sheet],false)),"DELETE","")

Filter the sheet by that column, anything that has "DELETE" in it should be deleted.

[mrmr96]

Audicab said:

mrmr96 said:

Have I got this right:

Sheet 1 contains 8000 rows, with more than one row per company (one for each contact).
Sheet 2 contains 1000 rows, with one row per company (?) of lost customers.

You need to remove all entries on Sheet 1 if the company exists on sheet 2?

Yes exactly this, thank you.

OK, I'll assume that the company name (i.e. the unique identifier) is in Column A on both sheets. And that data starts on row 2 of Sheet 1.

On sheet 1, put the following in the first free column to the right of your data, on row 2 (i.e. next to the first company)

=COUNTIF(Sheet2!A:A,A2)

Drag that down the full 8000 rows on Sheet1. Then apply a filter to sheet 1 to only show rows where the count is not zero. (If it's zero then the company name wasn't found on the list on sheet 2.) So with the non zeros showing you can select the rows and delete them.

Alternatively you could sort the companies on Sheet1 by the new formula I gave you, and all the ones with zero will appear at the top, and all the non zeros (i.e. the lost clients) will be at the bottom, so you can delete them as a block.

Nice and easy.

Edited by mrmr96 on Wednesday 27th August 23:25

[Audicab]

That is fantastic thank you. I have a few of these to do over the next few days and you have saved me time and from going mad.