PDA

View Full Version : [SOLVED] Creating Sequence Numbers



jamesg
11-05-2011, 11:01 PM
Can someone help with a formula please?

I have a pivot table of customers (biggest to smallest) and Products (biggest to smallest, within the customers)

I want to generate a sequence number I can Vlookup against that has 2 aspects to it. A whole number ranks the customer (eg 1, 2, 3, 4 etc) and decimal ranks a product (.1, .2, .3, .4 etc) within the customers sales. eg 1.2 = No 1 customers No. 2 product, 7.18 = 7th ranked Customers 18th ranked product etc, etc.

Many thanks, any help is appreciated.

James

mikerickson
11-06-2011, 11:20 AM
There is a problem with that system.

1.2 = 1.20, but the customer's No.2 product is probably not the same as their No.20 product.

Capungo
11-06-2011, 12:49 PM
Do you have to use the dot, which is the decimal separator at the same time, to separate the customer and the product number? Maybe onether sign like "-" works better...

mdmackillop
11-06-2011, 02:00 PM
You could use 2 or 3 decimal place coding, depending upon range of products i.e. 1.01 or 1.001

mikerickson
11-06-2011, 04:57 PM
... Maybe onether sign like "-" works better...
When I type 1-2, Excel thinks February 1, similar problems with /
1:2 is 1:02:00 AM

comma or semi-colon would be better delimiters, if this application won't be used with international settings.

Capungo
11-07-2011, 04:31 PM
I use the pipe (|) separator whenever I create an international project. Pipe works better in contrast with any other sign that causes a conflict with the other number formats...

mikerickson
11-08-2011, 08:53 AM
Alternalty, Excel works best when different data (e.g. Customer and ProductNumber) are kept in seperate columns.