+ Reply to Thread
Results 1 to 14 of 14

group Rows based on a specific value in column

  1. #1
    Registered User
    Join Date
    03-17-2012
    Location
    Mississauga,Ontario
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    75

    group Rows based on a specific value in column

    Hi
    i have table of all different account numbers ,I am looking to export group of rows based on value in column D i:e value "3519" to create groups of rows which contain the part#3519 or #3519 to further export these to another sheet to build Invoices.
    the other group or accounts not containing the specific "part#3519" can be discarded or not billed.
    Attached Files Attached Files
    Last edited by Lisa4legin; 09-13-2014 at 05:10 PM.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: group Rows based on a specific value in column

    Hi Lisa,

    Create a tab called Sheet2 and then run the following from Sheet1:

    Please Login or Register  to view this content.
    HTH

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: group Rows based on a specific value in column

    No VBA needed for this. Put this ARRAY formula on a new sheet A2, and copy down and across...

    =IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$C2:$C200=3519,ROW(Sheet1!A$2:A$200)),ROWS(Sheet1!F$2:F2))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    03-17-2012
    Location
    Mississauga,Ontario
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    75

    Re: group Rows based on a specific value in column

    Quote Originally Posted by Trebor76 View Post
    Hi Lisa,

    Create a tab called Sheet2 and then run the following from Sheet1:

    Please Login or Register  to view this content.
    HTH

    Robert
    Hi Robert,
    apparently the i am looking to export the group of rows along with the '3519' value row ,this code only filters out the rows that contain the "3519" value.. is there a workaround for this?

  5. #5
    Registered User
    Join Date
    03-17-2012
    Location
    Mississauga,Ontario
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    75

    Re: group Rows based on a specific value in column

    Quote Originally Posted by FDibbins View Post
    No VBA needed for this. Put this ARRAY formula on a new sheet A2, and copy down and across...

    =IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$C2:$C200=3519,ROW(Sheet1!A$2:A$200)),ROWS(Sheet1!F$2:F2))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    So this works exporting the 'Account#s into sheet2 , so how could I adapt this formula to rebuild the group (all columns) to the new sheet?

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: group Rows based on a specific value in column

    Hi Robert,
    apparently the i am looking to export the group of rows along with the '3519' value row ,this code only filters out the rows that contain the "3519" value.. is there a workaround for this?
    I don't know what you mean

    If you could re-post your workbook showing how the data would look before and after a macro, this should give me a better idea.

    Thanks,

    Robert

  7. #7
    Registered User
    Join Date
    03-17-2012
    Location
    Mississauga,Ontario
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    75

    Re: group Rows based on a specific value in column

    Quote Originally Posted by Trebor76 View Post
    I don't know what you mean

    If you could re-post your workbook showing how the data would look before and after a macro, this should give me a better idea.

    Thanks,

    Robert
    Hi Robert,
    please see , i have posted the sheet again as before and after files. So i was looking to export all the other parts along with the '3519' for a particular account.
    these other parts are identified as having the same Account # and location-ID columns creating a group of parts for an account.
    Explanation: The thing is that customers(account#) order the '3519' which is a premium service which we bill but we need to include the other parts also along on the invoice when billed.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: group Rows based on a specific value in column

    Hi Lisa,

    Try this where though it finds an extra group (refer account 29076493) I take it that's it's because of an oversight:

    Please Login or Register  to view this content.
    Regards,

    Robert

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: group Rows based on a specific value in column

    Quote Originally Posted by Lisa4legin View Post
    So this works exporting the 'Account#s into sheet2 , so how could I adapt this formula to rebuild the group (all columns) to the new sheet?
    Change the range references to include the new sheet name. The formula is designed to allow you yo copy it down and across, it will pick up the other columns automatically

  10. #10
    Registered User
    Join Date
    03-17-2012
    Location
    Mississauga,Ontario
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    75

    Re: group Rows based on a specific value in column

    Quote Originally Posted by FDibbins View Post
    Change the range references to include the new sheet name. The formula is designed to allow you yo copy it down and across, it will pick up the other columns automatically
    yes this works ..thanks a lot Ford!

  11. #11
    Registered User
    Join Date
    03-17-2012
    Location
    Mississauga,Ontario
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    75

    Re: group Rows based on a specific value in column

    Quote Originally Posted by Trebor76 View Post
    Hi Lisa,

    Try this where though it finds an extra group (refer account 29076493) I take it that's it's because of an oversight:

    Please Login or Register  to view this content.
    Regards,

    Robert
    hi Robert,
    Nice ! thanks a lot..
    just an after thought ...
    is it possible to add more "values" to the group ,
    Please Login or Register  to view this content.
    like "3501"
    do i modify this line?

  12. #12
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: group Rows based on a specific value in column

    Hi Lisa,

    Yes, you would additional part numbers to this line...

    Please Login or Register  to view this content.
    ...like so for additional part numbers (you can have up to 30 individual part numbers):

    Please Login or Register  to view this content.
    Or you can use the following where each part number is separated by a comma ie 3519 for one part number or 3519,3501 for two (or how ever many part numbers you want) via the Inputbox function from the PartNumSelection macro:

    Please Login or Register  to view this content.
    Regards,

    Robert

  13. #13
    Registered User
    Join Date
    03-17-2012
    Location
    Mississauga,Ontario
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    75

    Re: group Rows based on a specific value in column

    Thanks Robert ! this really helps out appreciate your help!

  14. #14
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: group Rows based on a specific value in column

    You're welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 02-10-2014, 10:26 PM
  2. Replies: 12
    Last Post: 02-21-2013, 12:55 PM
  3. Group Rows Based on two values in a Column A
    By daillest319 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2012, 03:51 PM
  4. Group Rows in Sheet based on column value
    By krishnarao in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2007, 08:32 AM
  5. [SOLVED] Group & sort rows that have a specific value in column C
    By Lex in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-27-2006, 07:50 PM

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