Newbie to the forum with an advanced excel problem

[Guitarmageddon]

Hello everyone, this is my first post. I have a situation that is causing me a lot of anxiety, and involves a job evaluation

I was given this assignment as part of an evaluation at my job, and Im clueless on how to figure it out.

I have a worksheet with 2 tabs in it. It contains sales numbers for my corporation, in a particular product department (i.e. sales segment. In this case the "sports nutrition" segment)

One of the tabs is a report of sales/about a dozen item attributes. It has these for thousands of items. The items are repeated on this particular tab, because many of them are available at multiple stores.

On the other tab, I need to pull two different columns (item attributes) from the first tab i mentioned above and insert them. I presume because it is beneficial for sales analysis, although the first report, entitled "replenishment report" does not come with this item info on it (duh...stupid )

How the heck do I do this without spending DAYS of my life with all of these items?

Here is the link to the file on my google.
https://drive.google.com/file/d/0B2z6HIm4YiM_eC1NZ1RsU1M1M1k/view?usp=sharing (you may have to download)

I need to do the following:

On the tab entitled "auto replenishment report" I need to create 3 new columns for each vendor- some vendors are really short lists, a couple are really long. The new columns I need are "class", "subclass", and "last recieved date". As youll see on the tab "sales by style" that the information I need is there, and can be sorted any which way. I just need to go to one tab, locate the info, and move it to the first tab. I can manually do it, but it would take DAYS! Also, youll see on the first tab that the items are constantly repeated because they are carried at many different "sites" (stores). How do I compensate for this in whatever formula or method excel uses??



What the heck do I do? I hope I explained it simple enough but I can elaborate on any detail needed.

 

[Smitty]

Thanks. I figured the solution may lie somehwhere with vlookup. Cant wrap my head around it quite yet...

See: VLOOKUP & INDEX/MATCH.

 

[Guitarmageddon]

Sure, that's exactly what a Pivot Table does. You could certainly try Burrgogl's suggestion of using VLOOKUP or INDEX/MATCH, but they're not going to give you much flexibility with regards to returning dynamic rows and/or columns, whereas pivot tables don't care. And I absolutely disagree that Pivot Tables are going to be harder than a formula approach. To me, Pivot Tables are by far the most underutilized tool in Excel.

This should get you started: Pivot Table Intro and Resources

So Vlookup is the method that I can get excel to just search out data, copy, and paste to where it corresponds on another tab? A pivot table actually modifies the existing table/creates a new one, right? Thats NOT what I am after. I just need to take some data from an existing tab, and move it into the other existing table...

Am I going about this wrong?

Would I create an "IF" statement to tell it "if you are looking at this style number (see my spreadsheet) then find the style number on the other tab and return class/subclass data"?

 

[AkaTrouble]

you are both right and wrong

vlookup will search your original data set and copy the results to a new location

pivot chart is a new location that goes to your original data set to fill its fields on update

both rely on your original data as a source

 

[Smitty]

A pivot table actually modifies the existing table/creates a new one, right? Thats NOT what I am after. I just need to take some data from an existing tab, and move it into the other existing table...

That's one of the big misconceptions about Pivot Tables. A Pivot Table works on a snapshot of your source data (called the cache), and it in no way modifies the data. It's simply a quick and powerful way to represent your source data in a consolidated manner and allows you to quickly switch between items, like Style Number.

Would I create an "IF" statement to tell it "if you are looking at this style number (see my spreadsheet) then find the style number on the other tab and return class/subclass data"?

Nope, that would be VLOOKUP. Or if you need to sum data for a specific item with multiple transaction rows you could also use SUMIF.

An if statement in its simplest form simply says IF something is true then do something, otherwise do something else. E.G. =IF(A1=1,1,0) which says IF A1 = 1 then return 1, otherwise return 0.

 

[Guitarmageddon]

So maybe this will help clarify what Im needing. Im also thinking index/match may be what I need? But the tutorials so far have blown my mind.

I have two tabs in my workbook.

Here is the first tab. It is the tab I need to pull the data into.


Here is the second tab, the source of the data. I need to pull the "class", "subclass", and "last recvd date" from this large source file into the file above. (There are more columns or erroneous data to me, I have just scrolled over)


The trouble is, I have to say, "when your site number is xxx, and your style number is xxx, then give me your class" the same applies to needing the subclass and last recvd date as well. As you can see on the first picture, there are many repeated style numbers because an item is carried at many different sites. Does this help bring it into perspective?

Here is the same tab as the above one (the data source tab) I have just sorted it like the data destination tab is sorted, by the "style number"

 

[Guitarmageddon]

OK folks...so I think I got it...

The key was in index/match and I had to actually copy my other tab over to the working tab to get all the formulas to mesh. I was probably screwing something up in the process of trying to have the formula access the two different tabs. When I copied the second tab onto the first one, just off to the side, it actually worked. I then learned how to add "iferror" to make it say what I wanted to say like "never recieved at site" or something.