Inventory list pricing

[Ibanez]

Hi everyone - I have a list of 3,000+ store inventory items (i.e. Windex 451ml) that I need to price out. Typically we look each item up 1 at a time and paste the price in which takes us days/weeks to do. We don't have a large budget to hire a developer to build a custom database for us and was wondering if excel was powerful enough to do this. We do have a master price list of 25,000+ items with pricing for each. so my question is this; is it possible to for to be given a list of 1,000s of items and have excel automatically search the master list of 25,000 items for a match and populate the price for the entire list, as 1 operation?

Thanks for you help and

 

[My Aswer Is This]

Yes. This could be done with Vba and possible with formula.

Tell me the sheet name and column number where the 25, 000 items are listed and the column number with the prices.

And then tell me on what sheet and in what column are the items that you want the prices for.


For example you may have the 25,000 items in Sheet ("Master") column "A" and the price in the adjacent column "B"


And now you enter a list of items you want prices for in sheet "Prices" column "A"

Our script could put the price for these items in sheet "Prices" column "B"

Just give me those details and let me see what I can do for you.

There may be others here at Mr. Excel which may have a formula solution

 

[My Aswer Is This]

If my previous assumptions are correct try using this script:

Sub Prices_For()
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets("Prices").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        
        For b = 1 To Lastrowa
            If Sheets("Master").Cells(i, 1).Value = Sheets("Prices").Cells(b, 1).Value Then
                Sheets("Prices").Cells(b, 2).Value = Sheets("Master").Cells(i, 2).Value: Exit For
            End If
        Next
    Next
Application.ScreenUpdating = True
End Sub

OK

 

[Ibanez]

The Sheet name is MasterPriceList and with Column B for product name and size/volume (i.e. Windex 421mL) and column C is the price. The sheet we're pricing out is called Inventory Schedule

 

[My Aswer Is This]

So did you look at my script?

Please explain what "pricing out" is

I do not understand the term "pricing out"

I assume you have a list of value in column "A" on sheet named "Inventory Schedule" and you want the price for these items put into column "B of that sheet.

Is this correct?

If not explain more please.

 

[Ibanez]

I'm not sure how to test your script. I'm thinking I would need to save it in the developer tab and save/run the script? I'm not a developer and only have simpler coding like HTML under my belt. Based on what you've provided below I think all I would need to do is save the script and change the names of the sheets. I appreciate your help and your patience with this!

You are correct, Pricing out is the process of putting the correct price next to the item.

 

[My Aswer Is This]

OK. Now that I know more. Wait 15 minutes and I should have a new script for you.
I will be back.

 

[My Aswer Is This]

Try this:
To install the code:
Right click on any sheet tab
Choose "View Code"
In upper left portion of window choose "Insert"
Choose "Module"
Paste the code in the module and close those windows

To run the script
From the Ribbon choose "View"
Choose Macros
Choose View Macros
Choose the macro named "Prices_For"
Choose Run

Sub Prices_For()
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("MasterPriceList").Cells(Rows.Count, "B").End(xlUp).Row
Lastrowa = Sheets("Inventory Schedule").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        
        For b = 1 To Lastrowa
            If Sheets("MasterPriceList").Cells(i, 2).Value = Sheets("Inventory Schedule").Cells(b, 1).Value Then
                Sheets("Inventory Schedule").Cells(b, 2).Value = Sheets("MasterPriceList").Cells(i, 3).Value: Exit For
            End If
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[Ibanez]

it works!! many many thanks! Is it possible to modify this to include searching column B, and Column C of the MasterPriceList? For example we a version of hte MasterPriceList where we have the item description in column B, the volume/size in column C, and the price in column D. This would mean that the script would have to find all of the matches in column B, and then find the correct volume in Column C to assign to correct price in column D. I guess i'm asking if it's possible for the script to search 2 columns before returning the correct price in colmn D?

 

[My Aswer Is This]

So your wanting to search the "MasterPriceList"

Look in column "B" for the value in Column "A" in sheet "Inventory Schedule"

And also look in column "C" for the value in Column "B" in sheet "Inventory Schedule"
And when found enter the value from "D" into column "C" of the Inventory schedule"

Is that what your wanting.

So in sheet inventory we will have Item name in column A

volume/size in column "B"
Price in column "C" is that correct?