Short script needed for invoice quantitys 1

[thereddwarf]

Senario
I am currently doing a project for a friend who is the owner of a company that builds playgrounds. The program I am using is Microsoft Excess 2007. Using VBA, I am creating macro script for an easy-to-use userinterface.

Problem
There is one piece of coding I am currently having trouble with:
He has asked if I can use a script to;
Find duplicate records
Delete extra records found
Change Quantity to +1 (for each record found)

Example
Here is an invoice page with 'Sling Swing' showing twice although both with quantity '1'. Although, the script I need has to be able to find the duplicates and change the quantity to the first copy as '2' or '3', depending on how many times the record shows up.


Thank you for your time,
thereddwarf

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stella740pl]

What do you mean by Microsoft Excess 2007, Excel or Access? It's not really clear from your post .

It seems that a database (like Access) is more suitable application for the job, while Excel is not exactly what you need.

Everything you are asking for is quite easily done in a database (say, Access) with some knowledge of SQL. By creating just one Access query using GROUP clause you can do all of the three tasks you want - merge all of the duplicates, eliminate extra records, and total up the quantities for the newly merged records.

I would suggest that you look up the Help in Access on how to create queries - or, if you need more then that, pick up a book on the topic. If you have questions or problems creating them, forum701 might be more appropriate.

 

[thereddwarf]

Yea sorry I meant Excel as the users preference, which is why I wasn't doing it on Access. I'll see if the entire program is suitable on Access, although there is a lot more to this preogram than simply that small piece of coding which is why I asked for an VBA script for Excel.

Attached is an visual example.

 

[Stella740pl]

Yes, what you are showing, absolutely belongs to a database - but then, I am partial, as I am a database programmer - not Access, though.

If you absolutely must do it in Excell, I am not sure if you can query Excel data the way you would query an Access table, but you probably can try out pivot tables for your invoice and see if it helps (Data -> PivotTable and PivotChart Report).

 

[SkipVought]

Hi,

There is no Microsoft Application named Excess.

There is Access, a database application and there is Excel, a spreadsheet application.

Please clarify.

Secondly, I have some questions. Your friend's database ought to be designed to prevent the possibility of entering duplicate data is that is a requirement.

Third, why are you assuming that the QUANTITY ought to be mdoified by Quantity to +1. It would be a better solution if your logic were Quantity = Quantity + Deleted Quantity

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}