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]

Some questions:

In Sheet 1 your numbers are separated by commas? comma and a space? or just a space?

After that.. you've lost me on what happens in Sheet 2.

Based on what you typed Sheet1!$C$3 you want to make a list of individual values in Sheet2!A:A (column A)?

What happens to Sheet 2 column B? Is that manually input or does the data come from some other cell?

So you want to type one of the values into Sheet2!$D$3 and show the result of its corresponding B-column value.

And at this point I fear I'm completely at a loss for what you want to achieve. Would it be possible to upload a sample file to google drive or something similar with annotations as to what result (even if its just "Here I want C3 values seperated with commas") so we can see what the structure is to better understand what you're looking for?

 

[skywriter]

Some sample data and showing us what you want based on that sample would be nice.

Excel Jeanie

 

[boredfool]

And just figured out can't post pictures.

 

[boredfool]

Excel mishap - Album on Imgur

Sheet one is the one with the SKU and Product name
Sheet two is the one that is an inventory list.
Sorry was hoping I would have explained it well.
The numbers would be in the SKU column. Separated by whatever would be easier, a comma, a comma and a space, or just the & sign.
Sheet 2 has the inventory list.
So I am wondering if something could read the data from C3 (the SKU Column) match it to Column A on sheet 2, then read the data in column B on sheet 2 and populate column D on sheet one, with all of that separated by whatever is easier as well. Honestly, that step could be skipped, I don't need the product names on this sheet, I put it there to make it read a bit easier so I would know I put in the proper SKU.
The column for volume on the first picture is where I would desire something to match the SKU number on sheet 2, grab the value in column D, do so for each SKU number and add them together to get a total volume number.
Hopefully the pictures helped.

I am sorry I did not explain it well enough the first time. I had debated putting the pictures up at first but decided against it for some foolish reason.
Thank you again for taking the time to read and respond. As I said if it can't be done, it can't be done. My skills in <acronym title="visual basic for applications">VBA</acronym> and such are rather lacking and I apparently bit off more than I can chew for this personal project.

I already reported my mishap with the failing to post pictures in a reply. Hopefully they remove my eyesore that I caused soon. Sorry about that.

 

[skywriter]

And just figured out can't post pictures.

I gave you a link to where you can get an add-in that will create html that can be pasted into this board giving us a sample formatted to look like what you see below.

Notice it shows us what cells the data is in, what the data consists of and what the sheet name is.

It can also be copied and pasted into a blank workbook.


Sheet1

*	A	B
1	Header One	Header Two
2	1	4
3	2	5
4	3	6

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[boredfool]

Hmmm... Using excel 2016, can't get this add in to work.. attempting to figure it out.

 

[boredfool]

Excel 2016 (Windows) 64 bit

	A​	B​	C​	D​	E​	F​	G​
1​	Month:		Goal:			Total Profit:
2​	Date	Client	SKU	Product Name	Volume	Earn Base	Profit
3​			017A	3-Day Trial Pack	100.5​	$113.70​	$59.75​
4​

<tbody>
</tbody>

Sheet: Retail Tracker

<tbody>
</tbody>

Excel 2016 (Windows) 64 bit

	A​	B​	C​	D​
2​	3123​	Formula 3 Cell Activator	60 capsules	21.95​
3​	3115​	Formula 2 Multivitamin Complex	Original 90 Tablets	19.95​
4​	3106​	Formula 1 Healthy Meal Nutritional Shake mix	French Vanilla 750g	32.75​
5​	2674​	Formula 1 Healthy Meal Nutritional Shake mix	Dulce de Leche 750g	32.75​
6​	1164​	Formula 1 Healthy Meal Nutritional Shake mix	Orange Cream 750g	32.75​
7​	1213​	Formula 1 Healthy Meal Nutritional Shake mix	Vanilla non-GM 750g	32.75​

<tbody>
</tbody>

Sheet: Product List

<tbody>
</tbody>

The forum tools add in by Rory on the guidelines page works well in 2016. Sorry it took so long.

 

[tygrrboi]

Where do you get the volume from? Is it Sheet2ColumnD minus the flavor? (eg. French Vanilla 750g would mean volume is just 750g?)
And are the tablets' volume just "60 tablets/capsules"? Furthermore... what happens when the order is "3123, 3106" One is 60 capsules, the other is 740g... what number do you want for "volume" in this case?

This would be very easy if the volume of each item had its own column in sheet 2... is that possible, or are the number of items too great to make this a worthwhile task? As a general rule, when making a reference chart, its good practice to keep all variables in separate columns for ease in separating them. It becomes more challenging when flavor and quantity is mixed together into one and you have to ask the macro to try and distinguish which one is which...

 

[boredfool]

Huh, the table headers didn't copy, sorry about that. The flavor and names and such are not as needed as explained in a prior post. The volume would be the far right number.

Excel 2016 (Windows) 64 bit

	A​	B​	C​	D​
1​	SKU	Product name	Product/Type	Volume Points
2​	3123​	Formula 3 Cell Activator	60 capsules	21.95​
3​	3115​	Formula 2 Multivitamin Complex	Original 90 Tablets	19.95​
4​	3106​	Formula 1 Healthy Meal Nutritional Shake mix	French Vanilla 750g	32.75​
5​	2674​	Formula 1 Healthy Meal Nutritional Shake mix	Dulce de Leche 750g	32.75​
6​	1164​	Formula 1 Healthy Meal Nutritional Shake mix	Orange Cream 750g	32.75 ​
7​	1213​	Formula 1 Healthy Meal Nutritional Shake mix	Vanilla non-GM 750g	32.75​

<tbody>
</tbody>

Sheet: Product List

<tbody>
</tbody>

Sadly with the inventory list nearing 200 items at this point leaving it as it is would be better.
the 4 digit number is the SKU, basically telling Excel the what row to grab data from once it matches it.

It would be if there were multiple SKU numbers in the cell C3 in the sheet listed as retail tracker it would add up all of the volume points and produce just the total amount on the retail tracker sheet.