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]

If the answer to all my previous questions are yes then use this script:

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

 

[Ibanez]

In the MasterPriceList we a many columns populated i.e. column A = product description, column B = brand name, column C = volume ... for example a video game system would be listed as follows on the MasterPriceList: Col A = Game console, Column B = Nintendo, Column C = WiiU, Col D = $249.99. now when we go the the inventory schedule and we have listed a Nintendo WiiU in Column B is the script able to populate the price from the MasterPriceList?

 

[Ibanez]

Sorry - i forgot to mention what if we have more than 1 Nintendo WiiU? for example on the inventory Schedule what if we have a Nintendo WiiU at beginning of the list and another one further on down? Is the script able to price multiples of the same item?

 

[My Aswer Is This]

So are you saying my script in post # 11 did not work?

In the MasterPriceList we a many columns populated i.e. column A = product description, column B = brand name, column C = volume ... for example a video game system would be listed as follows on the MasterPriceList: Col A = Game console, Column B = Nintendo, Column C = WiiU, Col D = $249.99. now when we go the the inventory schedule and we have listed a Nintendo WiiU in Column B is the script able to populate the price from the MasterPriceList?

 

[My Aswer Is This]

I think I'm getting a little confused here. I posted an original answer and you said it worked perfect.

Then you asked if the script could look for a particular item and and also look for the size and I provided an answer which you never mentioned if it worked or not.'

Now I'm seeing more questions relating to if there is a duplicate etc.

Lets start back over and explain to me again what we are now attempting to do.

Lets solve one question at a time.

 

[My Aswer Is This]

You said "and we have listed a Nintendo WiiU in Column B"
What would be in column "A" ? Product description I assume.

In the MasterPriceList we a many columns populated i.e. column A = product description, column B = brand name, column C = volume ... for example a video game system would be listed as follows on the MasterPriceList: Col A = Game console, Column B = Nintendo, Column C = WiiU, Col D = $249.99. now when we go the the inventory schedule and we have listed a Nintendo WiiU in Column B is the script able to populate the price from the MasterPriceList?

 

[Ibanez]

Sorry for the confusion. I have an older MasterPriceList (MPL) which is in a different format to my original question, on this MPL we have the columns defined as follows:
Col A = description
Col B = brand name
Col C = size/volume/type
Col D = price

I was wondering if it was possible to utilize the script you provided earlier across multiple columns. As an example if I have a pair of Levis jeans, that would occupy col A and col B. If the inventory listed Levis jeans could the script search the columns and return the price. Also, what if there is more than 1 entry of levi's jeans. Often times our list have the same item listed in multiple locations.

 

[My Aswer Is This]

I'm assuming now both sheets have the same set up:
Col A = description
Col B = brand name
Col C = size/volume/type
Col D = price

You will need to provide all the data for columns A,B and C and the script will provide the data for column D on sheet named "Inventory Schedule"

Not sure what you want to happen when this happens:

Also, what if there is more than 1 entry of levi's jeans. Often times our list have the same item listed in multiple locations. And what does multiple locations mean. Do you mean multiple times in same column

Why would you have the same exact product name size description in the same sheet.

Most companies I would assume identify items buy a UPC or some other sort of code and not just by name.

Try this script to do what you asked for:

Sub Multible_Categories()
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim s1 As String
Dim s2 As String
s1 = "MasterPriceList"
s2 = "Inventory Schedule"
Lastrow = Sheets(s1).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(s2).Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        
        For b = 1 To Lastrowa
            If Sheets(s1).Cells(i, 1).Value = Sheets(s2).Cells(b, 1).Value And _
            Sheets(s1).Cells(i, 2).Value = Sheets(s2).Cells(b, 2).Value And _
            Sheets(s1).Cells(i, 3).Value = Sheets(s2).Cells(i, 3).Value Then
            Sheets(s2).Cells(b, 4).Value = Sheets(s1).Cells(i, 4).Value
            End If
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[Ibanez]

Thank you! it's working!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thank you! it's working!