Compare and update function for excel

May 5, 2011 at 06:45:28
Specs: Windows 7
Hello,

I have a spreadsheet that I call "Master" and another one I call update. I want to be able to Update rows in the Master spreadsheet with rows from the Update spread sheet.

If the update spreadsheet has a row with part_number "123abc" in column "A", I want to update the row in the master that has the count in Column "G" where column "A" (update) matches column "A" (master)


Thank you so much!

Aaron


See More: Compare and update function for excel

Report •


#1
May 5, 2011 at 07:47:07
UPDATE VERSION OF QUESTION :)


I have 2 Excel files "Finished Goods" and "Finished Goods List2" that have the same columns in each. All i'm focusing on is columns A (part number) & G (quantity)

The Part#'s in the 1st sheet have outdated quantities.
In the 2nd file the Part#'s are same but the quantity has been updated.

I would like to run a script that will get the Part# from 2nd file and find it in 1st, if it matches then copy the new quantity to the 1st file.

Thank you! Thank you!


Report •

#2
May 5, 2011 at 08:50:52
Have you tried VLOOKUP in the "old" sheet to pull in the new values?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
May 5, 2011 at 13:50:19
I have researched the vlookup, i keep getting mixed opinions on if it will do the job or no. Current't i'm trying to get this to work:

Sub inventoryUpdate()
Dim master As Range, update As Range, item As Range, itemFound As Range

Set master = Sheets("master_sheet").Range("a2:a11")
Set update = Sheets("update_sheet").Range("a2:a11")

Application.ScreenUpdating = False

For Each item In master
Set itemFound = update.Find(item)
If Not itemFound Is Nothing Then item.Offset(, 1) = itemFound.Offset(, 1)
Next item

Application.ScreenUpdating = True
End Sub


Report •

Related Solutions

#4
May 5, 2011 at 17:59:50
Try this:

Assuming your Column A Parts Nmbr &
Assuming your Column G Quantity,
have a header row, we start at Row 2.

In your sheet Finished Goods
which has the part number, but the outdated quantity
in cell G2 enter the formula:

=VLOOKUP(A2,'Finished Goods List2'!$A$2:$G$30,7,FALSE)

It will access your sheet Finished Goods List2
and match the parts number in column A
and copy the quantity from the corresponding row in column G.

After you finish, do a Copy / Paste Special / Values
on column G.

The formula is currently set for 29 rows, from row 2 to row 30.
Modify to suit your needs.

See if that work.

MIKE

http://www.skeptic.com/


Report •

#5
May 6, 2011 at 05:43:26
I like that code much better! I have renamed the files and modified the code. On the two documents, one header spans the first 6 rows, the other uses the first three rows. Will that make a difference? I can make the end users change the way the headers work if need be.

Also there are multiple sheets on the master file. The sheet named radiators is the one i'm trying to link.

I get a value not avialable error on cell 4 when i paste the vlookup.

=VLOOKUP(A2,'FinishedGoodsList'!$A$4:$G$30,7,FALSE)

Here is an image of the files. Thank you very much!!

http://griffinrad.com/inventory.jpg


Report •

#6
May 6, 2011 at 07:47:00
On the two documents, one header spans the first 6 rows, the other uses the first three rows. Will that make a difference?

You must modify the formula to start at the different location.

I specified only one header row in my formula,

=VLOOKUP(A2,'Finished Goods List2'!$A$2:$G$30,7,FALSE)

So the modified formula should be something like:

=VLOOKUP(A4,FinishedGoodsLst!$A$7:$G$30,7,FALSE)

Note the differences.

See the single quote marks around the Finished Goods List name?
In my original formula, I used spaces between the words, thus the need for single quote marks.

And don't forget that after you finish, do a Copy / Paste Special / Values
on column G.

The formula is currently set from row 7 to row 30.

See if that work.

MIKE

http://www.skeptic.com/


Report •


Ask Question