Extracting a column from form 1 and placing it in form 2

[vonneffdobermans]

I have 2 different forms that I need info from one, added to the other.

The reason for this is to update pricing from a new file, into an older file with the same product code for each product.

on form 1(the one I want to keep), column x is price(that I want to update from form 2 column L), and column B is the product code(sku)

Now on form 2 Column L is the The customer price(this is the data I need moved over to column X on form 1. and column I is the UPC 10(sku) that needs to match the same sku(product code) on form 1.

Gee this sounds confusing aFTER i TYPED IT.. i HOPE THIS MAKES SENSE. i WILL ALSO ADD THE 2 FILES, SO YOU CAN SEE WEHAT I am talking about.

Please help as I have about 30,000 items total, and would take way too long to update prices manually every 2 - 3 months.

Jason

[RobertMika]

Hi vonneffdobermans
Please see attached.

I hope that what you after.
Is simpler to put all worksheets in one spreadsheet and the use VLOOKUP.
I have done this only for the first part(match formpart1 with form2part1) but I hope you get the Idea

[vonneffdobermans]

The sku numbers must match each item that will be updated.

Column I on form 2 must match the column B on form 1. the SkU numbers must match before it updates the price.

I think what I see in example may have worked that way, But when I try to type same thing in for mine( =vlookup(B3,form2!$I$1:$L$4899,4)

but nothing happens. it gives me 1 total for all rows..and I have no idea what i typed even means, can you break it down for me real quick... Also I only have 2 pages(form1 and form2)

I have attached a screen shot

Please help...

Jason

[vonneffdobermans]

I tried again, but still same thing...

Please somebody help me... I have no idea how or what to do in this case, and need to update my prices badly, asd I am loosimng money.. lol

Jason

[T-J]

This is a solution without macros or formulae and uses MS Query (Data tab - From Other Sources - From Microsoft Query) to query the current workbook.

You need to have all the data in one workbook, one sheet for the main data and one for the new price data, however only columns UPC10 and Customer Price are necessary on this sheet. The first row of each sheet must contain column headings.

Save the workbook and on the third sheet, start MS Query, choose Excel Files as the data source and connect to the saved workbook.

In the query wizard, choose columns from each sheet (place PRICE and Customer Price next to each other to make comparison easy), link the common fields PRODUCTCODE and UPC10. Run the query and return the data to Excel.

[vonneffdobermans]

Thanks so much T-J... That worked perfectly.. and easy as well..

I do not know if this is the correct place but here goes anyway,

If there are sku numbers that are in the new price list, is there a way to add them to workbook? along with the category desriptions, etc... Is there a way in MS Query?

Please let me know..

Thanks so far...

[T-J]

If there are sku numbers that are in the new price list, is there a way to add them to workbook? along with the category desriptions, etc... Is there a way in MS Query?

Yes that is possible, in the query just choose the columns you want from each table.

[vonneffdobermans]

Yes that is possible, in the query just choose the columns you want from each table.

Unclear...

is there a way for me to add the sku numbers that are NOT already in sheet 1, plus all the other data in other columns?

What I get is a new list every 2 months or so, with new price updates, and new items as well. I would like to be able to update the pricing, and add new items as well.

I tried to do do the query thing, but got same info in all the rows.. see attached image. it seems it changed all the descriptions, sku numbers to all the same thing.


Trying to understand, new to this, but learning quick..

Jason

[T-J]

is there a way for me to add the sku numbers that are NOT already in sheet 1, plus all the other data in other columns?

What I get is a new list every 2 months or so, with new price updates, and new items as well. I would like to be able to update the pricing, and add new items as well.

You will have to edit the SQL statement in Microsoft Query.

[vonneffdobermans]

You will have to edit the SQL statement in Microsoft Query.

Not to sound stupid... But how do I do that...

Thanks in advance,

Jason