Question Locked

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

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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 .

6 replies

by tobif

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.

by rayn

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

by tobif

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

by seanferd

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

by rayn

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

This discussion is closed to new replies.