Search the Community
Showing results for tags 'vba'.
Hi I am new to this forum and very new to this SQL issue. As you know Excel allows you to sort a set of data in ascending or descending order and through an ordered list. And here begins my problem, ie I would like to know if it is possible to do this order through a SQL statement. Sort ascendingly and descending I already know how to do but what I wanted, and it is shown in the example I attached, it was first to sort by field F58 in ascending order and then to sort by field F66 by the following list 'RR-CON, RR-COGP, RR -COCN, RR-COCS, RR-COGL, RR-COS ' I've got an example, with the VBA code that fetches me a table from the F58 and F66 values according to certain criteria, and then that data is only sorted by field F58, I'd like it to be sorted from the list above. It's possible? Many thanks Jorge Cabral Teste com SQL_V1 - ENG.xlsm
my focus is across a whole 3 years (156 weeks) of data, beginning January 1st 2011 (or nearest date) and ending December 31st 2013 (or nearest date) – so while we are looking at 3 years total, we will only end up with 52 weeks in our clean version of the data, labelled Week 1 to Week 52 (and not dates).there will be items in this list that launched before 1st Jan 2011, for which part of the launch year will show up in the period we’re focused on. In order that data percentiles (will be calculated at later stage) are not clouded by this, we also need to move any part year data from launches preceding 1st Jan 2011 to the relevant column. To illustrate by example, an item launched in July 2010 will have 26 weeks in 2010 (that fall outside of our 3 yr window) and 26 weeks that fall inside our 3yr window, but the first week we see is actually week 27 of that item’s launch, and not week 1. So the 26 weeks of data that fall into our window, should be shifted out to align with week 27-52 of the ‘clean’ data columns – does this make sense? So i want to have another column next to the WeekNumber column, which will give me the correct number (from 1 to 52) to place the sales value for each product. If then you do a pivot as shown in http://i.imgur.com/VZVIlPb.jpg by arranging the weeknumber column in the Pivots Column area and Sales Value in Pivots Value area, you will see the sales values are then shifted properly to fit the 52 columns. So for each Product, 1] if the sales data starts from 1st week (W 2011 01) and its total weeks count is < 52, then it means some weeks sales data lying in previous year. So this data needs to be numbered to correct week (between 1 to 52), by finding its column: (52 - count of weeks containing data for that product + 1).2] if the sales data does not start from 1st week (W 2011 01) and its total week count is < 52 then move it to 1st week (W 2011 01) column in 1 to 52 weeks i.e. number it as 1, 2, .....<=52.3] if the data starts from 1st week and is 52 weeks data then keep it as it is showing from 1 to 52 weeks. i.e. number it as 1,2,....52. Hope this makes sense. see the linkback for further clarity. http://stackoverflow.com/questions/23825905/shifting-156-weeks-sales-figures-to-52-weeks