comparing cells in column A 1

[dingleberry]

Hi,

I've got a spreadsheet consisting of about 10000 rows of data. On each row is something like this.

Companyname Contact Phone number email
3M jon doe 763-555-5555 jdoe@mmm.com
3M bill fre 763-555-5556 bfree@mmm.com
Medtronic sam jan 612-555-5557 sjan@medtronic.com
Medtronic art smart 952-999-5556 asmart@medtronic.com
Medtronic vicky lake 987-888-4456 vlake@medtronic.com

And sometimes these companies have like 20 entries. There is probably a total of 6000 companies in this spreadsheet. All I'm trying to do is a comparison on column A. Wondering if there is a way to have vb store A2 as a variable. The compare that against the next cell A3. If there is a match, compare it against A4. In this case A3 would not match and so the application would reset variable and do nothing. But if the match occured more than twice, every row that the application matched on including the originating row would be Activecell.entirerow.delete

Any ideas? I'm a pretty fluent perl,php guy and I know compare functions in those languages use a "like" funtion. I think the same applies here but I just don't know how to make vb look at a cell, compare it, and loop until an event.

Thanks,
dan

 

[SkipVought]

Hi dingleberry,

What are you trying to do here -- delete duplicate rows or generate a list of non-duplicate companies? Apparently the data in column 1 is the only thing that you're interested in.

So why not use a PivotTable to generate the list of companies? No programming required!

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[dingleberry]

Hey Skip,

Thanks for the prompt response. I am trying to delete all rows of data where there is more than two of the exact same companyname. You see, I'm trying to narrow down a database to only small companies, and the only unique identifier of small companies is the fact that they will have on average less than or equal to 2 entries in the database. Consequently, I'm trying to write a script that will parse the data in column a and if it determines there is more than two entries with the same companyname, it will delete those rows and every row with that same text.

Does this make sense?

 

[SkipVought]

Well then if you use Pivottable and 'Count of Company', that will give you the answer that you ar looking for.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[dingleberry]

Okay, that makes sense. But can you take the data in the pivot worksheet and have if deleted would remove the data in the source worksheet? Forgive my question if it's an easy one, I'm much more of a programmer than a user...

Thanks,
dan

 

[SkipVought]

I am trying to get at what's the point. Is the point to just delete rows with duplicate companies or is there some other point (business objective).

Here's a simple approch that takes no programming.

1. sort by company

2. enter a formula in the first empty column that countscompany occurrences (assuming that data starts in row 1, enter in row 1) & copy down

=COUNTIF($A$1:$A$20,A1)
in next column (assuming its C, then C1=1 nad C2 has following formula...)
=IF(A1=A2,C1+1,1)

3. copy those columns and paste special-values
4. sort by first new column
5. delete rows 3 and greater in first new column
6. sort by second new column
7. delete rows 2s in second new column




Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[dingleberry]

Skip,

I am going to be informing our customers of a new legislative ruling that was recently passed in America that basically enables small companies to have accelerated depreciation on assets. It's an attempt to boost economy in Manufacturing sector. So if a small company (spends less that 400K /year on assets) buys a machine worth 200K, they can get a tax break of 50% of value of asset first year + (assuming it's on a standard 7 year deprecitation) 14% of remainder. Where before this occured it was only 14 percent first year. Anyway, this is how I'm reducing our list of companies to the small ones. Does this make sense? So that is why I'm only trying to eliminate the large companies (or companies that have more than 2 contacts in our database.

dan

 

[SkipVought]

Could get that answer with a pivot table (as described above), a replicated column of the company counts and auto filter on that column in 30 seconds.

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[dingleberry]

Thanks Skip!