|
Target
|
 |
« on: March 26, 2008, 01:10:37 AM » |
|
I have a request from a colleague to assist with sorting and grouping data (in Excel) into blocks of X records (in this case 500), but I have no idea how to go about this
Sorting and subtotalling the data is not a problem, however I have no idea how to go about sorting the results into the requisite blocks (I'm presuming some efficiencies here, ie some sort of best fit type arrangement)
I've googled sorting without joy - clearly this isn't an exact fit but I really have no idea what what it is i should be searching for
The only examples I know of are tools like FillCD or Burn To The Brim (for filling CD/DVDs) but I have no idea how they do what they do
any suggestions?
Target
|
|
|
|
|
Logged
|
"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
|
|
|
|
f0dder
|
|
« Reply #1 on: March 26, 2008, 08:55:06 AM » |
|
Do you need any particular grouping (ie., best fit or whatever), or do you simply need to sort-and-chop?
You can do a lot with Office's vbscript, but it's not a super-joy to work with, the documentation is relatively bad, the IDE is lacking, etc... but it gets the job done.
|
|
|
|
|
Logged
|
 - carpe noctem |
|
|
|
PPLandry
|
|
« Reply #2 on: March 26, 2008, 09:39:40 AM » |
|
How about using the pivot table for grouping? Is the grouped output still supposed to be functional or only for presentation purposes?
[edit] Working with data is generally much more flexible and efficient in Access. I would import it and then perform various queries, pivot table, etc in Access (an then move it back to Excel if need be)
|
|
|
|
« Last Edit: March 26, 2008, 09:50:38 AM by PPLandry »
|
Logged
|
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz |
|
|
|
|
Target
|
|
« Reply #3 on: March 26, 2008, 06:10:21 PM » |
|
output is used as a basis for inputing journal transactions, hence the 500 record limit
in line with this I see a best fit grouping as the desired outcome. Also worth noting that some subgroups may require splitting, ie where there are more than 500 records for a given group
I'm anticipating the solution would be done using VBA
the idea is to simplify the process. I had thought of using a pivot but it seems a bit of overkill just to get the subtotals. Likewise, exporting to access, manipulating the data, and exporting it back to excel seems excessive (though this could be done from excel using VBA) - having said that, I still wouldn't know how to arrange the groups into a best fit arrangement
Hope this helps
Target
|
|
|
|
|
Logged
|
"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
|
|
|
|
PPLandry
|
|
« Reply #4 on: March 26, 2008, 06:28:00 PM » |
|
Access can link to Excel data (no need to export). Then a query can return records 1-500, 501-1000, etc. I can provide the query if you're not too fluent in SQL
|
|
|
|
|
Logged
|
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz |
|
|
|
Target
|
|
« Reply #5 on: March 26, 2008, 06:45:12 PM » |
|
thanks for your input!! Access can link to Excel data (no need to export). Then a query can return records 1-500, 501-1000, etc. I can provide the query if you're not too fluent in SQL
likewise Excel can access and control access (no need to export) and I can break the data into 500 row blocks without using anything else, but this isn't what I'm hoping to do an example - source data is 2000 records of this there are 750 tagged ABC001, 100 tagged as ABC002, 1100 as ABC003, 50 as ABC004, etc the idea is to subtotal the groups ABC001 - 750 ABC002 - 100 ABC003 - 1100 ABC004 - 50 then sort them so they fit into the 500 record limit group 1 - 500 X ABC003 group 2 - 500 X ABC003 Group 3 - 500 X ABC001 Group 4 - 100 X ABC003 & 250 ABC001 & 50 ABC004 the result would be either to tag each record with a group ID, or output as distinct blocks of data (i haven't really thought this far yet...) Target |
|
|
|
|
Logged
|
"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
|
|
|
|
PPLandry
|
|
« Reply #6 on: March 26, 2008, 07:35:13 PM » |
|
Hi see.
For such a case, an all Excel solution could be:
- Data > Sub-Totals
- VB Code to generate text files (scan rows and change file when count=500 or when cell contains 'Count'
|
|
|
|
Logged
|
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz |
|
|
|
Target
|
|
« Reply #7 on: March 26, 2008, 07:56:28 PM » |
|
many thanks, but this still isn't quite what I'm looking for
using your approach, I would sort the data to group the applicable fields, then loop through and insert a blank row at 500, 1000, 1500, etc.
net result would look like
group 1 - 500 X ABC001
group 2 - 250 X ABC001 & 100 X ABC002 & 150 X ABC003
Group 3 - 500 X ABC003
Group 4 - 450 X ABC003 & 50 X ABC004
potentially this could result in any given block containing a trivial number of records that have been carried forward from a previous block, where the whole subgroup could have been contained with a block if the groups had been sorted and aligned (by the group subtotals)
if it helps, consider the CD filling model (how many files can I bestfit on a CD)
This must be do-able (the aforementioned CD tools are proof), but how??
Target
|
|
|
|
|
Logged
|
"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
|
|
|
|
PPLandry
|
|
« Reply #8 on: March 26, 2008, 08:11:58 PM » |
|
In your example:
ABC001 - 750
ABC002 - 100
ABC003 - 1100
ABC004 - 50
dim col(100) as collection (or you can use arrays if you want)
dim Col2(100) as collection
dim ColRemainder(100) as collection
scan sheet
iCol=1
col(iCol).add xyz
if groupChange then iCol=iCol+1
loop
Once you have it grouped, then
calculate full sub-groups (qty=500) by using something like col(i).count-(col(i).count\500)*500 to get remainder.
For each full sub-group, copy col items to new collection col2
Put group remainders into a separate collection ColRemainder
You then simply need to output your col2 collections and ColRemainder collections
|
|
|
|
|
Logged
|
Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz |
|
|
|
Target
|
|
« Reply #9 on: March 26, 2008, 08:44:48 PM » |
|
Cool! I hadn't thought of that (obviously - DOH!!) I'll need to work through this to make sure i understand it correctly (collections/arrays are a weak point  ) many thanks Target |
|
|
|
|
Logged
|
"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
|
|
|
|
Target
|
|
« Reply #10 on: April 01, 2008, 06:38:18 PM » |
|
if anyones interested, and didn't know already (I clearly didn't), this is known as a 'napsack problem' Googling this resulted in a nosebleed (trying to understand the mathematical representations  ), so I'm no closer to working out the mechanics Target |
|
|
|
|
Logged
|
"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
|
|
|
|
ExcelGroup.png