Software

Question

Gravatar
0 Votes
Locked

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

By rayn ·
Multi-column and row data set.
Looking to copy entire line of data (or range within row) to a new table based on searching one column to match a search criteria. For example: search col "C" for #1, copy "all" rows of data into a table that has a title "Volunteers with 1 Family" . Another return table set up for #2?etc.

Working with excel in a Mac = No VBA

Any Hope in this other then putting a lookup formula into every cell of every result table?

Would use the same process on another data table of Clients to sort into other output tables that would depend on a string <emergency delivery only>.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

How many categories?

by TobiF 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.

gravatar
Collapse -

SO

by rayn 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 ?

gravatar
Collapse -

On the contrary

by TobiF 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

gravatar
Collapse -

What about using Access instead?

by seanferd In reply to On the contrary

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

gravatar
Collapse -

Thank you

by rayn 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

Back to Software Forum

Start or search

Related Discussions

Related Forums