Doing some charity work, now need some help in sorting data I collected

Software

Collapse -

How many categories?

by TobiF · 8 years ago In reply to Doing some charity work, ...

If more than some 15-20 categories, then this would be much better to handle through VBA.

But manually:
Sort the whole table according to your criteria. But the first category and paste into a separate workshet.
Repeat until you've split the table as required.

Collapse -

SO

by rayn · 8 years ago In reply to How many categories?

I take it that no standard excel lookup, hlookup,vlookup will process a automatic posting to sorted tables automatically - as the data changes ?

Collapse -

On the contrary

by TobiF · 8 years ago In reply to SO

A lookup function will always return one single value.

If you want to use this kind of functions to select sevearl lines of information, then you first need to prepare the data for this. As a result, you get a very heavy document.

But, if you want something totally dynamic, then this may be the correct way.

First Sort the data.
Next to the data, add a column, that will increment a counter for each new occurence. Let's say we're splitting the data depending on values in column C, and we use column A and B as "helpers", and the data begins in row 2:

Then, in B2, enter: "=if(c2=c1;b1+1;1)" and duplicate downwards in col B.
In A2 enter "=c2 & " " & b2

Now, In a separate sheet, enter the category name in A1, the column titles in row 2.
In A3 and downwards, fill in guiding values 1, 2, 3 etc as long as needed.

And the function to pick values to the second sheet:
In B3 "=vlookup($a$1 & " " & a3; Maindatasheet!$a$2:$h$300; 3; false)

In C3 "=vlookup($a$1 & " " & a3; Maindatasheet!$a$2:$h$300; 4; false)

Next, duplicate the distribution formulas downwards.

I may have mistyped something here. Use Excel help to doublecheck.

The if command has three parts: test, value if true, value if false. We use it to start numbering from 1 for each section.

vlookup searches among the values in the leftmost column. That's why we added the helper values in column A. The helper value is combined from the category label, a space and then the ordinal value. The 4th parameter "false" is needed to ensure that we only get complete matches.

Also very important is to correctly use the relative vs absolute references.

Good luck

Collapse -

What about using Access instead?

by seanferd · 8 years ago In reply to On the contrary

Would that work better for such a project? A database rather than a spreadsheet?

Collapse -

Thank you

by rayn · 8 years ago In reply to On the contrary

Thank Tobif, I will look into the logic you provided. I think I will have to go the way of a db and am looking into Filemaker Pro. I just may have to temper my my impatience and buy into the learning curve of the db system.

Thank you very much for your guidance.

Ray

Software

Question