'Sorting/grouping' methodologies

[Target]

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

[f0dder]

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.

[PPLandry]

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)

[Target]

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

[PPLandry]

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

[Target]

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

-PPLandry (March 26, 2008, 06:28 PM)

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

[PPLandry]

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'

[Target]

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

[PPLandry]

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

[Target]

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

[Target]

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