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?
niccrossCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think you forgot to attach.
niccrossCEOAuthor Commented:
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

tomfarrarCommented:
Is this what you are trying to do?
EE.xlsx
niccrossCEOAuthor Commented:
@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)
niccrossCEOAuthor Commented:
@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
tomfarrarCommented:
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
niccrossCEOAuthor Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
tomfarrarCommented:
The two columns I added re-created the price.  The final price is in the most right highlighted column.  The first highlighted column picked up the prices that matched your price-level criteria, and if there was no criteria, then it put "0" in the cell.  The right-most column then put the prices that matched in the cell, and if not matched "0", then put the original price.  This column could then be copy and pasted value in the original pricing.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.