• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

I need a macro to Sort, Group and Rank styles by various criteria

I have a need to take some data in it's raw form and aplly a macro to it so it will group by DIvision, Class, style and colour and then rank them by highest to lowest by total's by style.

I have attached an excel sheet with 2 tabs.  The Raw Data and the Desired output.

The key here is that the data needs to be alphabetical for the first 2 columns on the left - DIVISION and CLASS.  Then the Styles will be in the order of Total units by Style which i need to be calculated and grouped as shown.  Then ranked at the Class level.  (Not Division Level)

THis would be a great help if i could get something to run like this so i can apply it to a much larger amount of Data.
Hi-Lo-Report.xlsx
0
Element_Can
Asked:
Element_Can
  • 18
  • 11
  • 9
2 Solutions
 
Arno KosterCommented:
if you add a column in between C & D, use the following formula for cell D2 and drag this down to generate formulae for all rows
"=COUNTIFS(A:A,A2,B:B,B2,C:C,C2)"

Open in new window


you will have a 'number of styles for this division and class' figure.

You than can sort on

1) division (alfabetically)
2) class (alfabetically)
3) number of styles for this division and class (small to big)

and hide the number of styles column.

as in
 Hi-Lo-Report.xlsx
0
 
Element_CanAuthor Commented:
I'm sorry but perhaps i wasn't clear in my example or explanation.  I need the info as presented in my results. tab.  I'm not looking to merely "count" the styles but take them, group the units by style and rank the largest units per style per class per division.  

This is a Hi-Lo report which sort by the highest volume of the style in each class of each division.  Your "countif" does not do any of that.  It doens't group the items by style, doesn't rate them based on the overall units by style and doesn't rank them because of the above criteria isn't met

In looking at your count if, it just counts the number of styles which is not what i was looking for.

Thanks for trying but not the correct solution.

(I am have attached a new file with some explanations on it.)


Hi-Lo-Report.xlsx
0
 
Arno KosterCommented:
The countifs formula was used to sort / rate lines on number of lines with that particular style.
If you are looking for the total amount of units for this particular style, indeed it does not deliver what you are looking for.

the 'total by style' indication should then be somehting like

"=SUMIFS(G:G; A:A,A2,B:B,B2,C:C,C2)"

but the rank by style proves to be a little harder to make. I'll need more time to dive into this, i'll keep in touch.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Element_CanAuthor Commented:
Thank you....and yes.  I am not looking for a meer count of lines but an actual ranks of totals by style while still showing the actual units by colour and sorting them HI to LO by Class.

It is not as easy as it initially looked.  It is of some importance to me as it aids in not having to do it manually.  See what you can do.
0
 
gowflowCommented:
Wasn't this question asked before ? I have a funny feeling I already saw your file on a previous querry. Is your issue resolved or not ? pls specify what you need or what is missing
gowlfow
0
 
Arno KosterCommented:
can you try this

 Hi-Lo-Report.xlsm
0
 
gowflowCommented:
ok here it is pls try this version and click on Rank Command in Raw Data and check the results they will be created into a new sheet
Pls let me know
gowflow
Hi-Lo-Report.xlsm
0
 
Arno KosterCommented:
gowflow,

I think element can has asked a new question because this time the ranking is performed per disctinct division-product class combination instead of page-wise.
0
 
gowflowCommented:
akoster
Sorry but I don' t understand your comment
gowflow
0
 
Arno KosterCommented:
in post 36991224 you indicate that the question seems familiar and ask for what is missing.
in post 36992371 you link the answer to the previous question
my comment gives you an answer in that the starting post indicates that the ranking is to be performed at class level, whereas the given answer to the previous question does not rank at class level but instead ranks at worksheet level.

the author has the name "element_can", this indeed is easily mixed up with something that an element can or cannot do.
0
 
gowflowCommented:
ok fine tks your clarification but my post is related to this question. Do you see anything wrong in my solution ?
gowflow
0
 
Arno KosterCommented:
Well, formatting / combining cells is not applied and the ranking is not as asked for
otherwise i do not see anything wrong.
0
 
gowflowCommented:
wow u demolished it royally !!!
ranking is not as asked for ???
This is the post:
=========
group by DIvision, Class, style and colour and then rank them by highest to lowest by total's by style.
=========

True it is not with ocmbined cells for rank and totals but the vlaues are there !!!!
Don't know if you noticed but col B is not always numbers and need some manipulation as some figures are text and when it comes to sorting it gives weired results !!!

Anyway let the Expert talk and will see
for me the Expert = the client = the asker at then end they know better !!!
gowflow
0
 
gowflowCommented:
woops on the post I forgot part 2
====
The key here is that the data needs to be alphabetical for the first 2 columns on the left - DIVISION and CLASS.  Then the Styles will be in the order of Total units by Style which i need to be calculated and grouped as shown.  Then ranked at the Class level.  (Not Division Level)
=====
If this is what I did not deliver then I'd like to know what (obviously beside the merge of columns that is not a big deal)
gowflow
0
 
Element_CanAuthor Commented:
Good day guys,

First let me say, thank you for addressing this.  Once we nail it, it will be a very useful tool to manage data.  It seems that my explanation had been vague in some ways.  We are 3/4 of the way there in both solutions.  

I will give a point form list of the key features which i need.  These are all components of the example data and i "thought" as part of my instructions.

- Sort Alpha by Division and Product Class.
- Combine Units of all colours of by Product Style or Description as the result is the same.  
- Sort these by Largest to Smallest (by Class) which gives me a Hi-Lo report at the class level.  What i mean was at the Division/Class level.  I assumed i meant that the styles would remain in the Division and Class but one of the solutions resoorted it at the class level thru the whole report.
- Then i need the report to be ranked 1 thru whatever at the class level. Again meaning to remain sorted by division and class and then just ranked Hi-lo based on total units per style (in each class) See example.  In another example from the Expert, they ranked it over the total document

It does appear that the solution from  AKOSTER on 10/19/11 07:33 AM, ID: 36992115 is the right solution but i am just taking enough time to test it before i award points.


0
 
gowflowCommented:
Your coorect you need to rank by style my mistake I ranked the whole styles. but pls pay attention at col B:
Don't know if you noticed but col B is not always numbers and need some manipulation as some figures are text and when it comes to sorting it gives weired results !!!

gowflow
0
 
Arno KosterCommented:
Gowflow is absolutely right, you should check this thoroughly !
0
 
Element_CanAuthor Commented:
So in some instances this doesn't work then?

Does abpha sorts not work with Numbers and Letters?
0
 
gowflowCommented:
nope like 1 2 7 10 15 21 27 in alpha will sort like this
1
10
15
2
21
27
7

My solution fix this if you want its piece of cake to just rank by style my mistake I did not pay attention when u posted the workbook. If you want I can fix my solution to rank by style just let me know
gowflow
0
 
gowflowCommented:
Here is the file with the changes sorry for late reply.
gowflow
Hi-Lo-Report.xlsm
0
 
Arno KosterCommented:
No, it does work and i am pretty confident that my solution also works as expected. I merely indicate that it is wise to verify this.
0
 
Element_CanAuthor Commented:
OK...i've reviewed the AKOSTER solution and it works good for my purposes.  I have a minor tweek to the request.  Should i publish that as a new request or can it be managed here?  

The request is to have the Units in the Column F to also be (largest to smallest) at the style level.  This just means i can see which colour of a style is best.   TO be clear...this impacts one style at a time and just sorts at the colour level.

This would put all the relevent data in largest to smallest so i may be able to judge best sellers at the style level, at the class level and at the division level.
0
 
gowflowCommented:
did u chk mine ?
gowflow
0
 
gowflowCommented:
Is this what you want sorted also by color descending ? pls press on Rank Data command button and chk in the sheet created.
gowflow
Hi-Lo-Report.xlsm
0
 
Element_CanAuthor Commented:
I did check yours against a larger selection of Data and it has issues.  I get error message and it removed some data.

Not sure why but i added some screen shots to review.
Results.doc
0
 
Element_CanAuthor Commented:
This updated version has the same issues.  It is removing the "a" in the product class.  Is this intentional?  IN some cases my classes are Alpha as well as numbers.  

Removing this would be an issue.
0
 
gowflowCommented:
well tks for advising ... after I asked u :(
can you post the wksheet ?
0
 
gowflowCommented:
looking at your screen shot is it possible that you had some class that were empty cells ???
gowflow
0
 
gowflowCommented:
what I see from the second picture is that the class was either empty or space causing the number to be 0 reason why it was on top of division B then you have division 1 naturally comes after. Problem in this column that you have a mix of items that are sometimes digits sometimes string and its important to translate them to values to get the correct sorting.

I could sort out the problem better if you can post the worksheet
gowlfow
0
 
Element_CanAuthor Commented:
No....i have reviewed and the classes were Alpha.

Here is a copy with updated data.  I cannot post the whole source file.
Hi-Lo-Report-Updated-Data-.xlsm
0
 
Element_CanAuthor Commented:
The issue is that the data in the class column can be alpha.  And your solution is removing the Aplha and replacing it with zero.  Not acceptable.
0
 
gowflowCommented:
try this now
gowflow
Hi-Lo-Report.xlsm
0
 
Arno KosterCommented:
Additional sorting should be easily fixed by adding a line :

res.Sort.SortFields.Add res.Range("A2:A" & res.UsedRange.Rows.Count), xlSortOnValues, xlAscending, , xlSortNormal
res.Sort.SortFields.Add res.Range("B2:B" & res.UsedRange.Rows.Count), xlSortOnValues, xlAscending, , xlSortTextAsNumbers
res.Sort.SortFields.Add res.Range("G2:G" & res.UsedRange.Rows.Count), xlSortOnValues, xlDescending, , xlSortNormal
res.Sort.SortFields.Add res.Range("F2:F" & res.UsedRange.Rows.Count), xlSortOnValues, xlDescending, , xlSortNormal

Open in new window



0
 
Element_CanAuthor Commented:
OK....so both the updated file from GOWFLOW and the additional lines of code from AKOSTER work.

How do i award the points and accept Solutions?  

In this case, i think it appropriate to accept the multiple solutions but having never done that before, i'd like to know if you both agree.
0
 
gowflowCommented:
simple you press on multiple and click on the threads that you feel gave you the correct answers and award the points accordingly to each thread
gowflow
0
 
gowflowCommented:
Ooops you mentioned
.... i'd like to know if you both agree.

From my part I cannot agree or disagree it is entirely and strictly to your discretion to award points and we as expert cannot comment on this, though the system allows the Expert in case of dissatissfaction to either put a comment explaining his dissatissfaction for the asker to then review again or to simply OBJECT to the closure and then its moderators who would interveen to sort things out.

gowflow
0
 
Element_CanAuthor Commented:
Both solutions work very well.

Thank you.
0
 
gowflowCommented:
welcome and tks for the grade
gowflow
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 18
  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now