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]

Welcome to the Board!

Have you considered Pivot Tables? Add some slicers and you've got a robust reporting tool.

 

[Guitarmageddon]

Welcome to the Board!

Have you considered Pivot Tables? Add some slicers and you've got a robust reporting tool.

I looked into those but I kind of need to maintain the look/integrity of these organization reports because it is the "standard" we go by. Is pivot table something I can use to at least isolate the data I need?

 

[Smitty]

It shouldn't be too difficult to get close to the existing format. Pivot Tables will certainly isolate data based on different filters, whether you place them in the filter field (which would look like your current output), or use Slicers to make them more "self-service".

 

[Guitarmageddon]

It shouldn't be too difficult to get close to the existing format. Pivot Tables will certainly isolate data based on different filters, whether you place them in the filter field (which would look like your current output), or use Slicers to make them more "self-service".

So is there no way to basically say, "excel, go fetch this info from this tab and put it here"?

 

[AkaTrouble]

that is how the slicers work think of them a filter selections
it reads your data set and filters to only the info you want from it and displays that in the pivot table

the pivot table can be formatted to appear basically the same as any other excel sheet

 

[Burrgogi]

So is there no way to basically say, "excel, go fetch this info from this tab and put it here"?

That's is not what Smitty is saying. I could be wrong but i think he's saying you can take your existing workbook and insert a pivot table. For someone who is not familiar with Excel's capabilities, in my opinion it would be far easier to use the VLOOKUP or INDEX functions instead of trying to figure out how to create a new pivot table. I guess it also depends on the version of Excel. In 2003, I thought the Pivot table wizard was far more user friendly compared to the latest iteration in 2010 version. But I digress. Look up some examples of VLOOKUP or INDEX functions - it should point you in the right direction.

 

[Guitarmageddon]

That's is not what Smitty is saying. I could be wrong but i think he's saying you can take your existing workbook and insert a pivot table. For someone who is not familiar with Excel's capabilities, in my opinion it would be far easier to use the VLOOKUP or INDEX functions instead of trying to figure out how to create a new pivot table. I guess it also depends on the version of Excel. In 2003, I thought the Pivot table wizard was far more user friendly compared to the latest iteration in 2010 version. But I digress. Look up some examples of VLOOKUP or INDEX functions - it should point you in the right direction.

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

 

[AkaTrouble]

there is value in the Vlookup and index functions for older tech setups

however having used the power pivot and slicers in the newer office setups i find they are like

using visual programming language versus writing lots of line of machine code

this is digressing from your issue at hand ..

if your happy with the syntax for Vlookup and index you should be fine after a few swear words when they return wrong results first time

maybe reply here and state this is the option you wish to run with and peeps can start offering possible solution based on your data set

 

[Smitty]

So is there no way to basically say, "excel, go fetch this info from this tab and put it here"?

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