Regrouping in excel - some equivalent to decode in sql?

[mchoss]

Imagine a very large dataset comprising of various items that could be grouped to make a large dataset more manageable eg,

potatoes 10
carrots 7
beans 3
beef 5
lamb 4
pork 1

being turned into:

vegetables 20
meat 10

- What is the best way to do this in excel?
- Where can you define which specific items contribute to each subtotal category?
- The idea being to reclassify the items to end up with a much smaller dataset with far fewer individual items

Thanks in advance

 

[xlbo]

Have a look at pivottables and the "Group" function associated with them

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mchoss]

Yes, but this again relies on the data being classified already (ie into meat and veg in the above example). Is there a quick way to write conditional statements to classify each item? (It is impractical to do this manually - the dataset is enormous)

Cheers

 

[xlbo]

Yup - in code, you can use "Select Case" but you asked this in MSOffice not VBA so I gave you a non VBA way of doing it

In the above example, exactly how would you use the decode statement ?? you would still have to manually classify each of the items into its category at some point as there is no logical pattern to use.....thus your argument against a pivottable seems a strange one....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mchoss]

You're quite right, shortly after submitting this i realised that decode wouldn't do the job.

Thanks for the response, and yes, i knew the code way of doing this so i shall take another look at the group function.