hi everyone. Got one big question.
here goes:

how can I group data in excel, from one speadsheet that acts as data column then putting the result to other spreadsheet. For example,

spreadsheet 1: one column there contains "developer' or "designer"

spreadsheet2: must contain all the names which are tagged in spreadsheet1 as "developer"

spreadsheet3" must contain all the names which are tagged in spreadheet1 as "designer"...

guys.. help.. I'm just starting to learn excel=)

This is quite simple for an accountant that uses Excel a lot.
The answer create an extra column.
Use a code :-
1 for developer and
2 for designer.
Enter the code
Sort according to the code.
You now get the two types grouped separately.

If you're dealing with a growing list, I would suggest something quite similar to RolCam's answer.

By adding a helper "key" column, you can create an index that numbers your designers and developers sequentially.

Once that index is in place, you can use that key column to automatically display the names in order on your other sheets, as you requested.

This technique uses MAX(), INDEX() and MATCH() on your job sheets to grab data off the list sheet.

Take a look at this sample and see if you can apply it to your situation.

=========
NOTE: In this sample I used a separate key column for each job. This makes the realtime action in the sheet very robust, no matter how big your list gets, even into the 10s of thousands.

But if your list is always going to be small, then you could actually use an array formula on the job sheets to draw the names over one at a time by evaluating the entire dataset each time without needing to add a key column. It's very calculation intense, but quite usable on small datasets.

EDIT:
I've updated this sample sheet to show the array formula answer, too. It's in column E on the Job sheets.

When you make changes to an array formula, you press CTRL-SHIFT-ENTER to confirm your changes and activate the array. If you just press ENTER you will break the array and most likely just see an error. If that happens, press F2 on that cell and then CTRL-SHIFT-ENTER to reactivate the array.

Once the array formula gives you the correct first answer in the first cell, then you can copy it down further to get the rest of your list to appear.

If you decide to use the array formula, you can delete the Key columns from the List sheet.

NOTE:
This version of the array formula is very hard to read, and it only evaluates 100 rows of the LIST sheet, but it's evaluating all 100 rows in EACH cell of column E. So that's a lot of calculating. Remember what I said about not trying this on too large a data set.

Attached Files

Index-Match(JobSorting).xls (33.5 KB, 235 views)

--thanks... :) great help!!