# Inventory list pricing

#### Ibanez

##### New Member
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### My Aswer Is This

##### Well-known Member
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

##### Well-known Member
If my previous assumptions are correct try using this script:

Code:
``````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

##### New Member
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

##### Well-known Member
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?

Last edited:

#### Ibanez

##### New Member
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

##### Well-known Member
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

##### Well-known Member
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

Code:
``````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``````

Last edited:

#### Ibanez

##### New Member
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

##### Well-known Member
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"

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

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

Replies
4
Views
120
Replies
3
Views
78
Replies
4
Views
698
Replies
5
Views
617
Replies
15
Views
2K

1,191,707
Messages
5,988,192
Members
440,138
Latest member
yanaa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back