Populate end spreedsheet with values from two others

[Cisito]

Hi!

It has been a while since I have programed macros or anything else for that mater

I have searched the forums for something similar to what I need and decided to post my own questions because all I found is quite different from what I need.

Quick explanation: I am in the process of replacing an old system at work. I need to compare invoice values between the new and the old systems. So, ABS is my new system, and NW is my old system.

I need to populate a final spreadsheet with the invoice values from ABS and NW in other to compare them and make sure they are – or will be - the same.


These are my spreadsheets:

ABS invoices - a list of clients identified with a numeric code in column A; clients' name on column B; and an invoice value in column C

NW invoices - a list of clients identified with an alphanumeric code in column A; net invoice value in column B; and gross invoice value in column C

Final invoice comparison (this is to be my result spreadsheet and includes all my clients) - a list of numeric codes in column A; a list alphanumeric codes in column B; clients name on column C; columns D, E, and F are empty at the moment and need to be populated (D - NW net invoice value; E - NW gross invoice value; F - ABS invoice value).


I have prepared a small algorithm of what I need: (please forgive the loose use of my own algorithm language)

Open "ABS invoices"
Open "NW invoices"
Open "final invoice comparison"

for each row in "ABS invoices"
Locate "ABS Code" (client code in column A) in "final invoices"
Copy "abs value" (invoice value in column C) to "final invoice comparison" – into column F

for each row in "NW invoices"
Locate "nw code" (client code in column A) in "final invoices"
Copy "nw net" (invoice value in column B) to "final invoices" – into column D
Copy "nw gross" (invoice value in column C) to "final invoices" – into column E

Close "ABS invoices", "NW invoices", and "final invoice comparison"

I will end up with a file containing all clients and what amounts they have been invoiced from each system

Notes: because not all clients are invoices every month, "ABS invoices" and "NW invoices" will have slightly less clients than "final invoice comparison". That wont be a problem, because "final invoice comparison" has all the clients anyway. Thus all clients in ABS invoices and NW invoices are findable in final invoice comparison

I have attached samples of the three spreadsheets.

Any help on this will be greatly appreciated!

Kind regards,
Cisito

[davegugg]

If you already have columns A, B, and C populate, can't you just use Vlookups to populate the rest?

[Cisito]

Hi David,

Thank you for taking time to reply to my thread.

I have used vlookup to solve this before (I can attach a file with this solution). But I want to solve it with a macro.

1)I can export my 3 files easily from the different systems.

2)With macros I don't have to collate all the data into one file and then add the formulas (each of the files will have more than 2500 entries a month)

3)Plus I will add more modules to this macro so I can add more functionalities to it in the future.

Any help will be much appreciated

[davegugg]

Well, if you really want to use a macro instead, here is one that will work:

Please Login or Register  to view this content.

[Cisito]

Hi Dave,

Thank you for your hekp with this.

I was on leave - thus the late reply!

Brilliant help with the code there!

Cheers!

C.