Finding/ adding/ ranking question

  • Thread starter Thread starter Mojo
  • Start date Start date
M

Mojo

1st sheet

Green couches Sku number Sales last month
Blue couches Sku number Sales last month
Purple couches Sku number Sales last month
Yellow couches Sku number Sales last month
Purple stripe couches Sku number Sales last month
Green stripe couches Sku number Sales last month

Hello there and thanks for helping out!

Here is the question related to the above info.

Every month there is a ranking report that I look at to determine ho
our "looks" are performing.

I need to be able to set up a sheet that will (for example) look up th
purple and the purple striped couches add the dollar figure unde
"sales last month" and rank it to everything else (yellow + yell
stripe couches etc.)

This way I don't have to sit there add up all the "similar" looks an
then rank them out.

This is a simple example but at work there are 60 plus such combos tha
I have to sift through then rank out....

HELP!!!!!!!!!!!!
 
Mojo

You can use the following formula in a seperate column to seperate out the first word in each description

=LEFT(A1,FIND(" ",A1)-1

Then you could use a pivot table or Data>Subtotals to get the summary information

Good Luck
Mark Graesse
(e-mail address removed)

----- Mojo > wrote: ----

1st sheet

Green couches Sku number Sales last mont
Blue couches Sku number Sales last mont
Purple couches Sku number Sales last mont
Yellow couches Sku number Sales last mont
Purple stripe couches Sku number Sales last mont
Green stripe couches Sku number Sales last mont

Hello there and thanks for helping out

Here is the question related to the above info

Every month there is a ranking report that I look at to determine ho
our "looks" are performing.

I need to be able to set up a sheet that will (for example) look up th
purple and the purple striped couches add the dollar figure unde
"sales last month" and rank it to everything else (yellow + yell
stripe couches etc.

This way I don't have to sit there add up all the "similar" looks an
then rank them out

This is a simple example but at work there are 60 plus such combos tha
I have to sift through then rank out...

HELP!!!!!!!!!!!!
 
I need to be able to set up a sheet that will (for example) look up the
purple and the purple striped couches add the dollar figure under
"sales last month" and rank it to everything else (yellow + yello
stripe couches etc.)
Click to expand...

Umm. I can't quite figure out what you're saying here. Could you please
expand on what you mean? Please be as specific as possible.

In particular I was confused by "yello stripe couches" as those don't
seem to be in the table above.

Does each line in the table represent one row in the spreadsheet, or
multiple rows?

How do you determine whether two looks are similar?

Have you considered using a real database tool for this, like Access? It
has a lot more reporting options prebuilt, as opposed to Excel where you
have to build everything from scratch.
 
Hey there,

You are right. I meant to say "green couches" at the bottom.

All the info above is in a separate field. One for the description
one for the sku and then the dollar figures.

What happens is the program that we have at work does not create
relationship between these/ add them and then rank them. It treat
them all separate skus.

I used the example of the couches to try to simplify the scenario bu
look at it this way (which is the real problem).

We sell bedrooms. Some of them have 2 colors (all with different sku
and vendor descriptions in the system) and are combinations o
different vendors (some items we match and import so we can get th
price point). Now, when trying to see how the bedroom is doing I hav
to take into consideration the alternate finishes and the differen
vendors. The software at work treats them all individually whe
ranking them so I'm having to sit there and add all the dollar figure
that go together and then rank the "real" numbers for the group.


Thank you all for your help with this.

Jo
 
All the info above is in a separate field. One for the description,
one for the sku and then the dollar figures.

What happens is the program that we have at work does not create a
relationship between these/ add them and then rank them. It treats
them all separate skus.

I used the example of the couches to try to simplify the scenario but
look at it this way (which is the real problem).

We sell bedrooms. Some of them have 2 colors (all with different skus
and vendor descriptions in the system) and are combinations of
different vendors (some items we match and import so we can get the
price point). Now, when trying to see how the bedroom is doing I have
to take into consideration the alternate finishes and the different
vendors. The software at work treats them all individually when
ranking them so I'm having to sit there and add all the dollar figures
that go together and then rank the "real" numbers for the group.
Click to expand...

Sounds like you need to create a lookup table that maps the SKU or the look
name to the group it's in. If you do that you can use VLOOKUP. For
instance

SKU Group
12345 Green
23456 Green
13579 Yellow
13243 Purple
etc...

Or of course you could use the description instead of the SKU.

I'd define a name for this table.

then you add an additional column like
=VLOOKUP( {SKU cell}, SKUTable, 2, FALSE)

then you can sort and subtotal on that column.
 
Back
Top