Welcome Guest.   Make a donation to an author on the site October 31, 2014, 06:51:13 PM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
View the new Member Awards and Badges page.
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1]   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: 'Sorting/grouping' methodologies  (Read 3329 times)
Target
Honorary Member
**
Posts: 1,410



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« 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
Charter Honorary Member
***
Posts: 8,774



[Well, THAT escalated quickly!]

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« 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
Supporting Member
**
Posts: 691


see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« 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
Honorary Member
**
Posts: 1,410



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« 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
Supporting Member
**
Posts: 691


see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« 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
Honorary Member
**
Posts: 1,410



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« 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
Supporting Member
**
Posts: 691


see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« 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'


* ExcelGroup.png (6.99 KB, 183x524 - viewed 215 times.)
Logged

Real generosity toward the future lies in giving all to the present -- Albert Camus -- www.InfoQube.biz
Target
Honorary Member
**
Posts: 1,410



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« 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
Supporting Member
**
Posts: 691


see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« 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
Honorary Member
**
Posts: 1,410



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« 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  huh)

many thanks

Target
Logged

"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
Target
Honorary Member
**
Posts: 1,410



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« 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  ohmy huh), 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
Pages: [1]   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.032s | Server load: 0.24 ]