Other Software > Developer's Corner

'Sorting/grouping' methodologies

(1/3) > >>

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

Navigation

[0] Message Index

[#] Next page