Alternative to Pivot Tables?

[Cordury2]

I have a list where the unique identifying number repeats several times because of multiple items (different sku numbers). This data also contains an inventory column, sku count column, and variance column.

Other than a pivot table, is there another way where I could rank the variance of each sku separately on the same sheet?

Basically, I want to return the top 25 stores with the worst variance for each SKU

The Sheet looks like:

A B C D E

Store# SKU Inventory Count Variance

 

[Cordury2]

I guess I am just looking for suggestions of another way to approach this than using a pivot table. Ideally, I want to set up one sheet that will pull the worst variances by the sku column.

 

[SkipVought]

Hi,

Of course, there are other ways. It's like saying, "Is there another way to get from New Your to California, other than using I80? You bet! But, y'know what? I80 'll get you there alot faster under most conditions.

You can get the results you want in several seconds via a PivotTable. or you can write a program that will take you, depending on your level of expertise, up to several hours. Then, when you want something changed, you can spend even more time figguring that out.

Or you can use MS Query to get the unique list of ROW values, create another Query to get the COLUMN values (transposeed) and then write s complex SUMPRODUCT function perhaps, to get the aggregate values in the DATA area. I've done this before under certain circumstances. But I would not recommed it unless you are quite proficient Excel user.

What's the AVERSION to PivotTables?

Skip,
[sub]
[red][/red]
[/sub]

 

[Cordury2]

No real issue with pivots myself, I was just wondering if I could use the choose formula with a lookup or something like that.

VBA is out for me since I am not at that level.

 

[SkipVought]

As I said, any DATA area cell may have one or more ROW values and one or more COLUMN values. Those values can be used as criteria in a SUMPRODUCT function, for instance.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

but that's basically replicating what a pivot table does in the background but much less efficiently !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Take the INTERSTATE or muck around on secondary roads.

it's a CHOICE!

Skip,
[sub]
[red][/red]
[/sub]

 

[DirkStruan]

Hi Cordury2

I doubt you'll find anyone to disagree with Skip's suggestion, and I say heed the master.

However, if you want to stay away from pivot tables and you just want to identify the largest 25 sku variances, it sounds like you could use the Rank function.

D

 

[Cordury2]

Thank you all for the tips. I may try the sumproduct function... Right now I have created a unigue identifier with the concatenate function using the store&date&sku as well as using the rank formula with each sku.