Remove Duplicate Addresses

**Sbova0226** · 07-06-2009, 12:05 PM

Hello,

I have 2 issues i am trying to work through, i have a spreadsheet with 3 data sheet tabs, the first issue i have is in Data sheet 1 I have a lot of duplicate addresses, I was wondering if there is a way to filter out the duplicate addresses so that only one of each address is showing.

Issue 2 that i have is a lot more complicated, In data sheet 1 i have a list of medical providers that reimburst at 110% or more, each address on that list needs to get 1 letter mailed to them, but i have to keep track of how many are going to each region, i.e. Columbus, OH region, Cincinnati, OH Region, Toledo, OH Region, and Cleveland, OH region. So what i want to do is create a 3rd data sheet that keeps track of total letters sent by region, but was wondering if it was possible for this info to be automatically transfered from data sheet 1 to data sheet 3. I don't need all the information in data sheet 1 to tranfer to data sheet 3. All i need is for data sheet 3 to read off the City field in data sheet 1, and calculate 1 letter sent to that region. I dont even know if this is possible, but it is way beyond my realm of excel knowledge which isn't much.

Any help would be appreciated. I am trying to attach the spreadsheet, however it isn't allowing me to attach it, i keep getting a database error, when i upload it. It is under the max size limit too. Not sure what's going on.

Thank you,

SETH

**shg** · 07-06-2009, 12:18 PM

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

**Sbova0226** · 07-06-2009, 12:27 PM

Sorry about that. I amended it.

Thanks,

Seth

**zbor** · 07-06-2009, 12:28 PM

Can you upload an example?

It doesn't have to be real data, write dummy data taht would help better understending problem.

**Sbova0226** · 07-06-2009, 01:08 PM

Originally Posted by zbor

Can you upload an example?

It doesn't have to be real data, write dummy data taht would help better understending problem.

Here it is, i think it attached. I just put basic info in sheet 3 because i have no idea really where to start. Sheet 2 should just be left alone, as it is a list of everything under 110%, they will not be getting letters. The info attached is not sensitive.

Thank you,

Seth

**JBeaucaire** · 07-06-2009, 02:28 PM

Issue 1 - put this formula in R2 and copy down the whole dataset:
=COUNTIF($L$2:L2,L2)

Then click on DATA > AUTOFILTER, then click on R1 and select 1. Now you only see the first instance of each address. The others are hidden, still there...but hidden.

======
Issue 2, where is Sheet3 supposed to collect the information about how many letters have been sent to each location?

**Sbova0226** · 07-06-2009, 03:26 PM

Originally Posted by JBeaucaire

Issue 1 - put this formula in R2 and copy down the whole dataset:
=COUNTIF($L$2:L2,L2)

Then click on DATA > AUTOFILTER, then click on R1 and select 1. Now you only see the first instance of each address. The others are hidden, still there...but hidden.

======
Issue 2, where is Sheet3 supposed to collect the information about how many letters have been sent to each location?

Thanks! that worked perfectly.

As far as issue 2 goes, i am not sure exactly how to start it. But, from the one i included, i just wanted all the columbus, oh addresses in sheet 1 to basically count up under the columbus, OH region(A5) in worksheet 3, so if there are 100 columbus, oh addresses in worksheet 1 then the number 100 would automatically appear under the columbus, OH region(A5) in worksheet 3, and the same for Cincinnati, and Cleveland etc. then if i add another columbus address into worksheet 1 the number would automatically go up 1 under columbus in worksheet 3. I hope that makes sense. i dont know if it is possible to do that or not. If you need further clarification i can try to explain it better, just let me know.

Thanks so much,

Seth

**JBeaucaire** · 07-06-2009, 03:33 PM

That's just a COUNTIF() formula, right?

On the last sheet, change the value in A4 to just say COLUMBUS and put OH in B4. Now a COUNTIF() formula in A5 like so:
=COUNTIF('< Than 110%'!N:N,A4)

Repeat for other cities.

**Sbova0226** · 07-06-2009, 03:45 PM

Originally Posted by JBeaucaire

That's just a COUNTIF() formula, right?

On the last sheet, change the value in A4 to just say COLUMBUS and put OH in B4. Now a COUNTIF() formula in A5 like so:
=COUNTIF('< Than 110%'!N:N,A4)

Repeat for other cities.

GENIUS! You have no idea how much you've helped me out! Thank you so much! I greatly appreciate it!

Seth

**JBeaucaire** · 07-06-2009, 04:15 PM

Hehe, glad to hear it! Ya'll come back now, hear!?

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)