Ask Experts Questions for FREE Help !
Ask
    miss_claire's Avatar
    miss_claire Posts: 4, Reputation: 1
    New Member
     
    #1

    Aug 10, 2009, 04:40 AM
    how to group data in excel
    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=)
    ROLCAM's Avatar
    ROLCAM Posts: 1,420, Reputation: 23
    Ultra Member
     
    #2

    Aug 10, 2009, 05:12 AM

    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #3

    Aug 10, 2009, 12:26 PM

    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
  1. File Type: xls Index-Match(JobSorting).xls (33.5 KB, 235 views)
  2. miss_claire's Avatar
    miss_claire Posts: 4, Reputation: 1
    New Member
     
    #4

    Aug 10, 2009, 10:08 PM
    --thanks... :) great help!!

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel - aligning data based on a common value. [ 7 Answers ]

I have 2 lists of data. List A contains 2 columns - Lead ID (column A) & Email ID (column B). This list contains 3,175 rows. List B contains 2 columns - Email ID (column A) & Email Address (column B). This list only contains 3,085 rows. I need to match up the Lead ID with the Email...

Preparing charts using excel data [ 2 Answers ]

Please help me Whether there are any softwares available on the web to prepare detailed charts/graphs showing x and y axis using data stored in MS excel/XML spread sheet? The chart/graph so prepared must be available for detailed analysis and viewed on a full screen! Thanks in Advance!

Data cleansing in excel [ 1 Answers ]

Every day I get reports of more than thousand rows for data cleansing. In that report mainly we have to delete all the related records as per amount like +100 and -100 should be deleted. I have attached the sample report here for your reference.

Shortcut for entering data in Excel [ 2 Answers ]

I often work with data in scientific notation. Is there a shortcut for entering numbers using only the number section of the keyboard? (i.e. entering 1.7E+09 without using the ‘e’ character)


View more questions Search