Need help writing a macro for excel

I don't even know if it's a macro I want or if it's possible to do this.

We have a large spreadsheet (2000+ rows and still adding).

The purpose of the spreadsheet is...how do I explain it. We process "searches" for a client for which they fax us consent forms for individuals. I will do a "batch" of requests every two to three days, consisting of 10-40 people per batch.

We have the various basic columns across the top (first name, last name, address, results, etc), however, each batch is separated by 2 rows, one blank at the end of the previous batch, and one with the next batch # in the first column/cell, then it will be a bunch of rows, one for each person in the batch.

Our problem is, sometimes we duplicate a search because we process so many requests, and don't have time to check back with the previous batches to see if we have done that search already. There are two places that fax us the consent forms, and sometimes they forget they already faxed it.

You know if you enter data in excel, and similar data is above it, it will start to auto-fill the cell? It doesn't work in this case because of the blank rows between each batch.

Is there a way I could set up a macro to search for duplicate names in the spreadsheet? Not necesarrily as I type, but I button I could hit that will search it all and highlight duplicate last names?

 

raingirlIT

Do you have both the Batch # and First Name in column A? If so the easiest thing to do is to let AugoComplete do its work as a macro for this would be very complicated. Do the following:

Make column A for batch # only.
Make column B for first name
Delete all blank rows.

If you have any questions, please reply.

 

A macro will work but...

I agree with Anne,

The design of the table needs to be changed. You'll need to copy the batch# into column A of each row for that batch but that's a one-time task - onerous, but only one time. Then, removing the blank rows is pretty easy: just highlight all the data and SORT by NAME.

That does two things: all the blank rows (and the original batch number rows) will be in one place for easy deletion and then the list is sorted by name so you can scan down the list and look for duplicates.

A simple macro would, most likely, do the same sort and then, for example, highlight any duplicate names it finds. You would still have to enter the duplicate data and manually scan the list for the highlighted names.

A more comprehensive macro would have you entering the name on a separate worksheet and search the main list as you exit the Name field. The macro would then interrupt you if it found a match or, when the entry for that person is done, add the new entry to the list.

Don't know if it's a drawback, but sortuing the entire list this way - macro or not - will "ungroup" the batches in order to scan for duplicates. You'll need to sort again (on BATCH and NAME) to return the list close to its original order.

Hopefully this adds to your knowledge rather than you confusion...


Raevn

 

Thanks for the replies. Unforunately, the format cannot change at all (no sorting allowed) because this data is uplinked to another system, and it reads directly from the spreadsheet in this order.

Oh well...thanks anyway.

 

Have you considered what you will do when you hit the row limit for an Excel spreadsheet? This is crying out for a database.

Have you thought of shooting the person who designed your systems? It may be the most merciful solution.

 

RE: Shooting the designer...

Fortunately, that won't happen for a while. If I remember correctly, the row limit is up around 65,360 for Excel 2000 and later versions.

Don't shoot the designer <whining overtone>. You don't shoot designers for the same reasons that we don't shoot politicians and Direct TV vendors -- they've legislated too many silly consequences to such efforts. Just make sure no one hires them again. Designers can't design bad systems if they have no jobs! You wanna PUNISH the guy? Make him MANAGE the system he designed -- for LIFE!!!

But seriously, RainGirlIT, one could build a macro that works as you describe but the output would have to be handled carefully. The user would highlight the names she wrote from the latest fax and click the macro button. The macro would search each name in the selected cells against the rest of the worksheet just as if you'd use the FIND function (CTRL-F). If it found a match for that name (or rather, a "first instance" match) it could highlight the cell and, maybe, write the row number of its find in the next cell to the right. Like this:

NAME........................ADDRESS
John Jones
Paul Simon .................Row 1384
George Smith III............Row 715
Ringo Starr

If only a few names come up as matches, you can probably conclude that the match was a coincidence and continue adding the addresses, etc. However, if ALL or most of the names are matched and the rows are relatively close together, you can probably conclude that the batch from this fax has already been entered.

Advantages

• We don't have to sort or redesign the lists
• The output is limited to the rows you input for the latest batch
• The user doesn't have to finish adding the address, etc. if the macro indicates the batch is already added.

Disadvantages

• We only get probable results, results that need additional interpretation to be actionable.
• The user then needs to delete the row references and highlighting, and possibly the entire name list, before they can continue working.

The costs of developing a working macro may not be that high but... the user may actually spend MORE time because he/she still needs to enter most or all the names of a potentially bad batch in order to find out if the effort was worth it.

And that's a question I can't answer without knowing a whole lot more about your work process.


Raevn

 

I don't know who designed it, but the "process" has changed since it was designed. Before the process changed, there was no worry about duplicates, because the head office was the only person sending faxes to us, and they had already double checked them. Now, anyone in the company (province wide) can send us the faxed forms, so it gets tricky.

I'm not too worried anymore since I recently resigned and it isn't a worry anymore. It's going to be fun to train the new person on it though.

 

You Lucky Lady

You luck lady!!! I've heard that mail room workers are only allowed to process Zip codes for 2 hours a day. The bad news is that they must maintain a processing speed of one Zip code every 2-5 SECONDS!!!!

But I digress...

Besides creating a macro with all the attendant hassle of working with a programmer and the specs and costs and all; the cheap approach would be to have the data entry person just use the FIND function (ALT-F) to look up each name she enters.

Tedious, I know but the price is right and if it's a duplicate fax, the user will know by the 3rd or 4th name. It would not reduce the total task of typing, in fact, it would require entering almost all names at least twice. The macro described above would only require the names to be entered once but duplicates - and duplicate faxes - would have to be totally entered before the user could be sure of a duplication.

Please let me know if I can be of further help.


Raven

 

Hey!

Use filters... I will give you all the information you want
data>filter>autofilter

this wont modify the workbook you are working on that is linked to another sheet.

or you can create another spreadsheet and link the tables so you can edit the new table as you want.

 

Great Idea but...

Hello Cesaoes:

RainGirlIT has a fixed format for the list that introduces two lines of data between each batch of name, one with the batch number in col A and a blank line. Since she's not allowed to modify that format, the filter won't work. That's where we had the discussion of whether or not to shoot the designer.

Hadn't thought of that fix. It would work too. What she was looking for, I think, is a solution that would find a duplicated name or batch of names with a minimum of extra work - something a basic data entry person could handle. Rebuilding the datalist as a linked table and then maintaining it as new batches of names are added would not be easy work but it would introduce the users to another, more useful, format that we've all been hoping the owner would be willing to change to.


Raevn