Two Excel Workbooks
I have two excel spreadsheets. One is an exported SQL database into Excel.
In workbook one i have itemnmbr, prclevel and uomprice (the important fields). The UOMPRICE is the field that needs to be updated.
In workbook two I have Column A (item), Column D (1-10), Column E (11-50), Column F (51-100), and Column G (101=)
The identified from workbook one would be ITEMNMBR and PRCLEVEL ( the PRCLEVEL shows a text description like 1-10, 11-50, 51-100, and OVER 100)
I do n ot know an easy way to related it to update workbook one with the columns from workbook two.
I have attached a sample file showing the two workbooks; However due to experts exchange i had to combine the files. Worksheet 1 and Worksheet 2
Can anyone help?
In workbook one i have itemnmbr, prclevel and uomprice (the important fields). The UOMPRICE is the field that needs to be updated.
In workbook two I have Column A (item), Column D (1-10), Column E (11-50), Column F (51-100), and Column G (101=)
The identified from workbook one would be ITEMNMBR and PRCLEVEL ( the PRCLEVEL shows a text description like 1-10, 11-50, 51-100, and OVER 100)
I do n ot know an easy way to related it to update workbook one with the columns from workbook two.
I have attached a sample file showing the two workbooks; However due to experts exchange i had to combine the files. Worksheet 1 and Worksheet 2
Can anyone help?
Subodh Tiwari (Neeraj)
I think you forgot to attach.
ASKER
sorry
Workbook1.xlsx
Workbook1.xlsx
On Sheet2 (i.e. your workbook2) there are price levels of 1-10, 11-50, 51-100 and 101 + so how do you get the UOMPRICE for price levels WholeSale and Retail in col. G of Sheet1?
Or they don't need to be updated?
Or they don't need to be updated?
Is this what you are trying to do?
EE.xlsx
EE.xlsx
ASKER
@sktneer
I have a separate spreadsheet for the RETAIL pricing.
I believe the WHOLESALE line item is also indicated by the same criteria. As you can see that the FROMQTY and the TOQTY shows the 51-100, 11-50, 1-10 (columns E &F)
I have a separate spreadsheet for the RETAIL pricing.
I believe the WHOLESALE line item is also indicated by the same criteria. As you can see that the FROMQTY and the TOQTY shows the 51-100, 11-50, 1-10 (columns E &F)
ASKER
@tomfarrar
No because the pricelevel (column c on worksheet 1) dictates the price levels. the row headers on worksheet two dictate the price levels so it's hard to create that relationship
No because the pricelevel (column c on worksheet 1) dictates the price levels. the row headers on worksheet two dictate the price levels so it's hard to create that relationship
I thought I brought the two together in the example spreadsheet. If you were trying to pick up the pricing on sheet 2 to update sheet one for pricelevel/itemnumber, that is what I thought I did. Can you show us what you want the results to look like? - Tom
ASKER
I guess what I am trying to accomplish is a scan and replace. Take the item number from Worksheet two and find it in worksheet one. if found, find out if the price level is 1-10, 11-50,51-100, or 101+ .. if it meets the matching criteria it would replace it with the proper number in Worksheet two.
WORKSHEET 1
AA001 $5.00 (1-10) $4.75 (11-50) $4.50 (51-100) $4.25 (101+)
WORKSHEET 2
AA001 $5.50 (1-10) $5.25 (11-50) $5.00 (51-100) $4.75 (101+)
once completed worksheet 1 and worksheet 2 would be a mirror of each other. There are over 3k records, so manually would suck.
i tried to import into Access but that didn't work.
WORKSHEET 1
AA001 $5.00 (1-10) $4.75 (11-50) $4.50 (51-100) $4.25 (101+)
WORKSHEET 2
AA001 $5.50 (1-10) $5.25 (11-50) $5.00 (51-100) $4.75 (101+)
once completed worksheet 1 and worksheet 2 would be a mirror of each other. There are over 3k records, so manually would suck.
i tried to import into Access but that didn't work.
Though I am still not sure about your requirement completely, please find the attached workbooks 1) Imported Data and 2) Source Data.
The Imported Data.xlsm would be one which contains your imported data where you need to update the UOMPrice in col. G.
The Source Data.xlsx would be one which contains your items, price levels and price.
Place both these workbooks in the same folder.
Now open the Imported Data.xlsm workbook and click on the button "Update UOMPRICE" button to update col. G according to the item and price level from the Source Data.xlsx.
Note: While downloading and Saving the Source Data workbook, remove the default hyphen added to the workbook name and make sure that you name it exactly like Source Data.
Change the Name of the Source File in the code as per the requirement.
Imported-Data.xlsm
Source-Data.xlsx
The Imported Data.xlsm would be one which contains your imported data where you need to update the UOMPrice in col. G.
The Source Data.xlsx would be one which contains your items, price levels and price.
Place both these workbooks in the same folder.
Now open the Imported Data.xlsm workbook and click on the button "Update UOMPRICE" button to update col. G according to the item and price level from the Source Data.xlsx.
Note: While downloading and Saving the Source Data workbook, remove the default hyphen added to the workbook name and make sure that you name it exactly like Source Data.
Change the Name of the Source File in the code as per the requirement.
Imported-Data.xlsm
Source-Data.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.