Create a list of duplicated numbers in a worksheet

**woodsonline** · 09-13-2007, 05:34 AM

Hi all, we are a mail order business and when we send out catalogues to people, we inevitably get some returned to us as undeliverable/moved/incorrect address etc. When this happens, we input the customer number onto an excel sheet (see attached).

When there is two entries for a customer number, we need that customer number to show on a list (preferably on a different worksheet) so that we can see who has had two or more 'fails' and stop sending catalogues to that customer.

I hope you can help,

Thanks in advance,

Steve Quinn
Woods Online
www.woods-online.co.uk

**antoka05** · 09-13-2007, 05:55 AM

See the attached file where I added macro 'Macro1'.

I hope it's what you need.

Regards,
Antonio

**woodsonline** · 09-13-2007, 06:31 AM

Nice one Antonio, we are very grateful for your help - it works perfectly.

Regards,

Steve Quinn
Woods Online
www.woods-online.co.uk

**broro183** · 09-13-2007, 07:19 AM

Hi Steve,

My below Conditional Formatting suggestion is not a complete answer (esp when compared to Antonio's) but it could be used in conjunction with Antonio's macro to provide a visual warning that the customer number is used twice.

1) select a region of cells that includes all your entries so far & the number of cells that you expect to use in the near future (not the entire worksheet though as the next step may slow /freeze your computer).

2)Open Conditional Formatting eg [alt + O + D], change the dropdown box to formula is & type the following (excluding the quotation marks) into the box on the right:
"=countif($a$1:$r$200,a1)>1"
where
$a$1:$r$200 can be changed to the range you want and
a1 is the address of the cell that is currently active.

3) Click format & choose an option that will catch the eye eg a red or orange background under the Pattern tab.

4) click ok until back at the normal excel window.

If using Antonio's macro approach I would add the below lines of code in just before the end of the macro to "free memory" (for any variables that have been "set") when the macro finishes ie...

Please Login or Register  to view this content.

and I would also add

Please Login or Register  to view this content.

but I'm not a 100% sure on this one because I'm not really sure on the impact on created objects or "scripting" code.

Antonio,
I'm curious, why don't you have option explicit at the top of your code to force the variable declarations?
Would this mess up the "myarray" somehow/would it be declared as a string?

hth
Rob

**antoka05** · 09-13-2007, 11:05 AM

Here's another attached file to follow broro183's suggestions.

In thi smacro I avoid to use scripting objects and added instructions to free memeory.

Regards,
Antonio

**woodsonline** · 09-14-2007, 08:46 AM

Hi all again, i have a further requirement with this worksheet (well, two actually but i will save the second one for a different post!)

Ok, once this sheet shows me who has had two failed deliverys, i remove those people from our mailout list.

I then need to delete the two entries from the first sheet, so that next time we input some nondelivereds and run the first macro, we get people who have not been taken off the list yet.

For your info, i have tried to do this a few ways without deleting the matched pairs, but, for example, if you add a new nondelivered to the list, and run the original macro, the new matched pair entry on sheet2 can appear in random places within the list so I cannot rely on a premise of say 'i have done up to row 74 so any new ones must be after that'

I also tried marking each entry on sheet2 with an x in the next column to show it had already been dealt with but on running the first macro again the x's dissapear.

Obviously if anyone can think of a better way to do this we are very grateful for your help.

Thanks in advance,

**antoka05** · 09-14-2007, 12:29 PM

See attached file.

You don't need to delete old customers because now macro will add new double customers Id at the bottom of sheet2 and will select new added customers with yellow backcolor.
In column 'B' of sheet2 I added also date/time (if you don't like dd/mm/yyyy format please change the 'format' instruction in macro1) so you can remember when you noticed that double customer Id.

Regards,
Antonio

**woodsonline** · 09-17-2007, 06:25 AM

Hi there, thanks very much for this solution which seems to be fine and suits our needs perfectly.

Steve

**antoka05** · 09-17-2007, 11:28 AM

Glad to know it helped.

Regards,
Antonio

Create a list of duplicated numbers in a worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Create a list of duplicated numbers in a worksheet

20 minutes = Awesome

Your all very kind - I now have another question :-)

excellent - thanks a lot

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions