Attempting to read a cell with multiple values and look up results on another sheet and return results to original sheet in a single cell.

[boredfool]

I shall attempt to be brief with this. Working on a project for myself and cannot figure out how to do this.
On sheet one I have a cell (C3) that I want to be able to input numbers like so, 3123, 4587, 9856 and so on. Nothing but groups of 4, sadly some of them may have a letter in it as well as the last digit like 017A. There isn't a limit to how many sets of numbers there could be in the cell, probably no more than 10 but to be safe maybe a max of 20 would be fine.
I have an inventory sheet (Sheet 2) that corresponds with these numbers (Column A), in cell D3 I would like to figure out how to have excel search for each set of 4 numbers and return the value from sheet 2 Column B and whatever row it finds the number in, into Sheet 1 D3 separated by commas or the & sign.
To make it more complicated, in cell E3 of sheet one I would like to have something be able to grab numbers from Column D on sheet 2 from the corresponding row that the group of 4 is found, and somehow have it add all of the numbers it finds there and return that value to E3 on sheet 1.
Basically sheet 1 would have Inventory numbers in C3, the names of the items in the inventory in D3 and in E3 total volume or weight of the combined items.
In my head it seems like this could work, but my slow learning of VBA and functions in Excel haven't yielded anything.
Any help would be nice, if this is impossible that would also be nice to know so I can quit tearing out my hair.
Thank you all for taking the time to read this.

 

[tygrrboi]

So I assume you would like to disregard "Earn Base" and "Profit" for the sake of this example... Ok give me a moment to try something for you.

 

[tygrrboi]

Does this help?
I have it loop through every row of the C column in Sheet 1 and stop when its blank... In case you have multiple lookup locations. It will work with just one tho.

Also I have it separating the items based on a comma and a space... so you would have input it as
3115, 3106, 2674 for example

Sub Fill_From_SKU()
Dim sNameList As String
Dim iNum As Integer, i As Integer
Dim iVolume As Double
Dim vItems As Variant
Dim sSearch As Worksheet, sTable As Worksheet


iNum = 3  '(starting in row C of Sheet 1)
Set sSearch = Sheets("Sheet1")
Set sTable = Sheets("Sheet2")


While sSearch.Range("C" & iNum).Value <> vbNullString
    sNameList = vbNullString
    iVolume = 0
    vItems = Split(sSearch.Range("C" & iNum).Value, ", ")
    For i = LBound(vItems) To UBound(vItems)
        If sNameList = vbNullString Then
            sNameList = sTable.Range("B" & Application.Match(vItems(i), sTable.Range("A:A"), 0)).Text
        Else
            sNameList = sNameList & ", " & sTable.Range("B" & Application.Match(vItems(i), sTable.Range("A:A"), 0)).Text
        End If
        iVolume = iVolume + sTable.Range("D" & Application.Match(vItems(i), sTable.Range("A:A"), 0)).Value
    Next i
    sSearch.Range("D" & iNum).Value = sNameList
    sSearch.Range("E" & iNum).Value = iVolume
    Set vItems = Nothing
    iNum = iNum + 1
Wend
End Sub

 

[boredfool]

I have no idea how you typed up something that quickly. Maybe with a lot more practice and as many tutorials as I can find I might be able to do that.
I have attempted to run it and I keep getting a Run time error 13, type mismatch at these ines

If sNameList = vbNullString Then
sNameList = sTable.Range("B" & Application.Match(vItems(i), sTable.Range("A:A"), 0)).Text
Else
When in debugging mode it is right before the Else statement that it pops up. I am unsure what is causing it but I will continue to research it.
I thank you for what you have done.

 

[tygrrboi]

I got that error too at first. I had to format the Product Numbers in Sheet 2 into Text for it to work.
(You'll know its formatted into text when you see a little triangle in the cell and when you click on the warning icon it it will say something like "Number Stored as Text"


On sheet 2. select your entire row A
Control C to copy,
Right click and choose "Clear Contents"
In the Number Format area in your ribbon choose "Text"
Control P to paste,
Then click the floating paste options icon and select "Match Destination Formatting"

 

[tygrrboi]

Also Unrelated... You'll get there. I first came to this board with nothing but a question and some ancient memory of high school Visual Basic class content.

While my solutions are probably not as succinct or fast as what the MVPs and moderators may come up with... I still love doing it. (I actually waste a lot of time at work on this board instead of doing my actual work... )
I believe if you have the motivation, you will get there too. Keep it up.