Pulling information from one spreadsheet to another

[ft13]

so i have this assignment and im having a brain fart...

i have this spreadsheet that has 2 tabs titled "master" and "unique".

"master" has a list of ingredients (8 each), a number associated with it and a store number associated with it. for example (basil-136-r5)

now, each store number has a different number associated to the ingredient. for example (basil-136-r5, basil-235-r6).

on the "unique" tab/spreadsheet, im supposed to create a formula that calculates the number of times (ex: basil) is used and what number is associated with it to the store number.

here is a pic:

<img src="http://img.photobucket.com/albums/v105/SeaDonkey/pic1.jpg">

does that make sense?

if so, please let me know how to do so!

i think its a lookup?

-Shane

[zbor]

1. Write name of the title according to rules
(Make your thread name be precise in explaining your problem)

2. Try to upload your example not picture of it. If you have important data make just an example with dummy data that would represent your true data.

3. I've try to do something here... but I keep getting error.
Please, can someone see why =SUMPRODUCT(--(LEFT(Master!$A$2:$A$31;LEN($A2))=$A2);--(RIGHT(Master!$A$2:$A$31;2)=C$1);MID(Master!$A$2:$A$31;LEN(Unique!$A2)+2;LOOKUP(9,99999999999999E+307;CHOOSE({1;2};0;FIND("-";Master!$A$2:$A$31;LEN($A2)+2)))-LEN($A2)-2)) don't return number...

Attachment 44628

If I put 1* infront of MID(...) I get #VALUE error

4. after you get your result please make your thread [solved] (in first post->edit->go advanced -> choose from drop down menu [solved], left of where you change thread name

[DonkeyOte]

Not really - per my first note you should not use phrases such as "Need Help" in your title - it bears no relevance to the subject matter.

I have revised on this occasion and will undelete zbor's initial reply - please make the time to read the forum rules prior to reposting.

[ft13]

ok. sorry.

im attaching the spreadsheet to this post and here is the exact description of what i need to do:

"Our company happens to own eight restaurants. Each of our eight restaurants use the same exact selection of 15 ingredients for all dishes. To keep track where everything is going, we've assigned a unique item number to each ingredient at each restaurant. For example: Basil is #159 at Restaurant 1 and #219 at Restaurant 2. We have a giant list of all the unique items but what we really need is a nice table with the Ingredient Name on the Y-Axis and Item # for each restaurant on the X-Axis. It'd be great if the table could also count the number of occurrences of each Ingredient Name to make sure there are no misspellings anywhere in our database that would affect keyword search results.

The "Master" sheet is the giant list I referred to.
The "Unique" sheet is a list of each ingredient we use in a table as I've described.

Your task is to complete the table by creating formulas to automatically fill in the Ingredient Name Count (B2:B16), and to automatically fill in the Item Numbers for each Restaurant (C2:J16), based on the data available in the Master sheet. See example row (without formulas): B2:J2"

thanks again for any help!

-Sin