filter data into catorgories named by type

[AllenF]

Hi guys.
Can somebody explain how i could achieve the layout as per the attached book please.
To take a list of random items, categorize them with a TYPE dynamic range and then sort everything into those categories.
hard to explain but i think the layout says it all. preferably in VBA as the list could be massive
Many thanks
Allen

[CAntosh]

I think you can get the result you're after without using anything too fancy. If you sort column C in ascending order so that all of the different 'types' are together, you can then use Data > Subtotal to get Excel to split the groups the way you want. Set the Subtotal options to: subtotal at each change in TYPE, using function 'count', adding subtotal to TYPE, and check 'replace current subtotals' and 'page break between groups'. The resulting table matches what you were looking for, gives you a count of each type, and doesn't require anything that may trouble your system.

EDIT: Your types also end up grouped for efficient use in the future.

[AllenF]

k thats almost got it cantosh. but i really wanted the headers at the top and the subtotals at the bottom of each group. if i just uncheck the subtotals at bottom then i get "TYPE Count at the top of each section not just the TYPE category
Is that possible or not?
cheers
Allen

[CAntosh]

I think it looks pretty solid if you move TYPE to column A, then check 'add subtotal to TYPE' when you use subtotal. You get the headers at the top and offset in a sensible way. You still get the subtotal count at the top, though. You can check 'summary below data' to move the count to the bottom, but it takes the header with it. If you're desperate for the header at the top AND subtotal at the bottom, use the arrangement advised above, then add the subtotal count beneath each type either manually or with VBA (or with subtotal in a way I haven't discovered yet). I'm on my out the door or I'd keep playing. Someone here can surely finish the job if you decide it needs more tweaking.

[AllenF]

k cheers its not laying out as per the example book though. and thats really how i want it laid out if possible
Allen

[AllenF]

i guess not possible in the way i want to do it

[CAntosh]

What is the 'No' column doing? Does it assign a unique number to each description, or will it tabulate the number of dogs, cats, oranges, etc.?

You can achieve the look you want with a PivotTable - under Row Labels, list TYPE first and Description second. Move 'No' down to the 'values' slot. You can choose whether you want 'No' to be counted, summed, etc. You can turn the subtotals on/off with PivotTable options. Using a Pivot table would also allow you to have both your original data and the resorted data, giving you maximum analytical flexibility.

[CAntosh]

If you're committed to a VBA solution, the following procedure should adequately mimic what you requested. It will create the new-look table on sheet 2. Be sure to clear all of the extraneous info from your sheet before running it - the procedure will get very confused if you still have "From this" in Row 1 or your mock-up of your desired result beneath the actual table. If possible, though, I would advise the pivot table option above; it'll give you a great deal more flexibility with your data.

Please Login or Register  to view this content.

[AllenF]

thats what I'm looking to do cantosh.
But when i assign the macro to a button it throws the wrong data. as if its skipping stuff and only giving the totals. ifI run the macro from vba or debug it comes out right

[CAntosh]

Hmm... It's running from a button for me, so I can't really diagnose why it's giving you difficulties. Unless there is something off about the data arrangement on the master sheet or the one it's trying to paste to, I'm stumped. When I delete row one and all of the 'how I'd like it to look' content from your sample, add a button attached to the macro, and hit the button... I'm getting the desired result on sheet two. If you need the macro attached to a button, maybe post a sample of what your workbook looks like just before you hit the button so I can see what you're seeing?