+ Reply to Thread
Results 1 to 15 of 15

Excel 2007 : *SOLVED* How to group and sort data?

  1. #1
    Registered User
    Join Date
    09-20-2011
    Location
    Reading, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    *SOLVED* How to group and sort data?

    Hi guys,
    I really need some help with a complex problem i am having.

    I have been given about 90,000 rows of data.
    Split into modules, and the modules are in chassis'.

    Now.
    Some of the modules have reached their termination date, others haven't.
    What i need to do is find out which chassis' have no modules in them that have reached their termination date? If the chassis has one module that has reached its termination date then the whole chassis has to be classed as past its termination date.

    I have tried sorting and filtering but i split up the chassis' if i do this and then I don't know if the chassis has just one module that is past its termination date?

    If it helps module cells that have reached their termination date have a coloured background. If not they are white.

    Any help to this very complex problem would be so greatful.

    Many Thanks.
    Last edited by joshrob; 09-20-2011 at 10:49 AM.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Complex Formula Needed.

    Hi,

    I think a sample workbook would really help us to understand your problem.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,006

    Re: Complex Formula Needed.

    Please read and adhere to these simple rules!

    1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.
    Never use Merged Cells in Excel

  4. #4
    Registered User
    Join Date
    09-20-2011
    Location
    Reading, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How to group and sort data?

    Hi Dom,
    Thanks for the quick reply.
    Unfortunately the data itself is actually highly classified.
    Ill try make on similar up and post it on.
    Thanks.

  5. #5
    Registered User
    Join Date
    09-20-2011
    Location
    Reading, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How to group and sort data?

    Hi guys.
    Attached is a sample of the spreadsheet i am working with.
    As you can see there are 3 chassis' (jr1, jr2, jr3) split into their modules/slots as i have previously explained.
    The 3rd chassis (jr3) has no modules that are terminated and therefore is the chassis im looking for.
    There are thousands of chassis and even more modules. So i cant do it manually.
    Attached Files Attached Files

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,006

    Re: How to group and sort data?

    Maybe this?
    Where terminated is 0, that's your chassis...
    Attached Files Attached Files

  7. #7
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: How to group and sort data?

    My suggestion was =NOT(COUNTIFS($A$2:$A$41,A2,$F$2:$F$41,"Terminated")) in G2 copied down which would return TRUE if a chassis hasn't been terminated.

    Dom

  8. #8
    Registered User
    Join Date
    09-20-2011
    Location
    Reading, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How to group and sort data?

    Thats what im looking for.
    But i am a bit of a novice and i dont know what formula you have done?
    I can see it is COUNTIF but i dont know you applied it to all the data?

  9. #9
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: How to group and sort data?

    The formula is COUNTIFS which is similar to COUNTIF but allows multiple criteria. It was introduced from Excel 2007 onwards.

    The syntax is basically =COUNTIFS(Range_1,Criteria_1,Range_2,Criteria_2)

    In my example I've then enclosed it in NOT to coerce it into a boolean value.

    Dom

  10. #10
    Registered User
    Join Date
    09-20-2011
    Location
    Reading, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How to group and sort data?

    Dom,
    Would you mind doing your NOT example to our example spreadsheet so i can have a look??
    Thank you

  11. #11
    Registered User
    Join Date
    09-20-2011
    Location
    Reading, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How to group and sort data?

    Also guys,
    I dont want to have to enter in the chassis names manually?
    Is there a way that these NOTCOUNTIF or COUNTIF can let me just drag down and it will enter the chassis name automatically?

  12. #12
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,006

    Re: How to group and sort data?

    Here is Domski's solution:
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-20-2011
    Location
    Reading, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How to group and sort data?

    Hi guys.
    Thanks for all the help.
    Is there anyway that i can use the countif and countifs and get the chassis names automatically?
    Just there are about 20/30 thousand chassis names and it will take to long to do manually?

  14. #14
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,006

    Re: How to group and sort data?

    I still don't understand what you want for output?

    Domski's result is in G column. You can add filter in fir row and filter TRUE from g column.

    Mine formula will take out unique values and total/terminated.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    09-20-2011
    Location
    Reading, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How to group and sort data?

    I have now solved the puzzle and finished the assignment.
    Thank you to zbor and Domski for your guys help.
    Couldnt have done it otherwise.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1