CODEWITHSUNDEEP
sqlexcellist
user2743642
user2743642

Reputation: 1

Ordering a list SQL or Excel

I have a simple task to do at work almost daily and I think it can be done easily with some help. There is a table with a single column "PC name". I have to divide the list of PC's into waves.

Wave 1 : 2%  
wave 2:  3%  
wave 3: 25%  
wave 4: 45%  
wave 5: 25%

So what I usually do is to copy the list of PC's into Excel and add a column named "wave assign". So for example if the list is 100 PC's first two PC's will be assign to Wave 1, three PCs towave 2, 25 PCs to wave 3 and so on.

I need a way to automate this since it takes me too long to do it manually. It doesn't matter if there is a small change in the % in order to round up the number of PCs in each wave.

Upvotes: 0

Views: 69

Answers (1)

pnuts
pnuts

Reputation: 59475

Assuming the list is in ColumnA starting in Row1: 

=VLOOKUP(ROWS(A$1:A1)/COUNTA(A:A),wArray,2)

in Row1 and copied down should work, provided a lookup array of the following kind is created:

SO18596249 example

and named wArray.

In case the list is shorter than 100 I have added .002 to the 'logical' breakpoints (cumulative proportions) so that it is not the minority waves that are rounded down such that, at say 50 items, Wave 1 does not feature (and hence stand out rather more than an approximation in a larger group).

Upvotes: 1

Related Questions