+ Reply to Thread
Results 1 to 12 of 12

Filter down spreadsheet into another sheet based on a condition

  1. #1
    Registered User
    Join Date
    07-21-2015
    Location
    uk
    MS-Off Ver
    2010
    Posts
    10

    Filter down spreadsheet into another sheet based on a condition

    Guys I generally don't do much work with excel but I have been assigned a fairly tricky task (well in my opinion). I have a very large spreadsheet that I need to filter out specific data and copy to a second sheet. The second sheet should be populated based on a condition of the first sheet. I will provide a sample of my data and what I need to try and do:

    A B C D E F G H I J K L M
    Cust Name CORP ID APP ID Origin Network PTT Country Trunk Digits Route Dial ON-TERM OFF-TERM
    AC 01 1abcd2 UK 5555 152453 US 001/1212 3213 89112 PLAN 1 NO YES
    DC 02 1EFGH2 USA 5225 133453 UK 002/1213 4214 PLAN 1 YES NO
    TC 04 1abc22 UK 5335 1556453 US 001/1212 FULL 89112 PLAN 2 NO NO
    BC 1abcd2 UK 5555 152453 END 32223 55112 PLAN 1 NO YES
    NC 08 152453 212/9872 55112 PLAN 3 NO NO


    So from the above table I need to copy the rows of the table that contain a number in both columns H & I. If there is not a number in both these columns it is skipped. In fact if possible I want to add another column that concatenates the values of H & I when there is a number in both of them. My second sheet should like this;
    B C D E F G H I J K
    CORP ID APP ID Origin Network PTT Country Trunk Digits Route Dial
    01 1abcd2 UK 5555 152453 US 001/1212 3213 89112 PLAN 1
    02 1EFGH2 USA 5225 133453 UK 002/1213 4214 PLAN 1

    In the second table some columns are not needed so they are removed (A,L & M). This is a large sheet so I only provided a sample but it should show what I am trying to achieve. If any of this makes sense to you guys can you help out and point me in the right direction.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,411

    Re: Filter down spreadsheet into another sheet based on a condition

    You should realise that to Excel the digits 001/1212 (in H2) would not be recognised as a number, but as a text string. Will all the valid entries in this column be of that form, i.e. number slash number ?

    Are you looking for a formula solution, or a macro?

    Pete

  3. #3
    Forum Contributor prabhuduraraj09's Avatar
    Join Date
    05-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    330

    Re: Filter down spreadsheet into another sheet based on a condition

    Hi tyson,

    I have attached the sample file and below is the code for your reference.

    Do add Reputation, if i helped in someway.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-21-2015
    Location
    uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Filter down spreadsheet into another sheet based on a condition

    @Pete.. I would love to see a formula solution if possible. Yes I was thinking that, I may try add in another column and join col H & I together and remove the slash.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,411

    Re: Filter down spreadsheet into another sheet based on a condition

    In the attached file I have used column O in Sheet1 as a helper column (coloured blue), with this formula in O2:

    =IF(OR(H2="",I2=""),"-",IF(ISNUMBER(--SUBSTITUTE(H2&I2,"/","")),MAX(O$1:O1)+1,"-"))

    This is then copied down beyond your data to accommodate more data being added - the hyphens indicate how far the formula is active. The formula identifies the records which meet the criteria and gives each a unique sequential number.

    Then in Sheet2 I have used column A as another helper, with this formula in A2:

    =IFERROR(MATCH(ROWS($1:1),Sheet1!O:O,0),"")

    The formula finds the rows in Sheet1 where the records meet the criteria. B2 in this sheet contains this formula:

    =IF($A2="","",IF(INDEX(Sheet1!B:B,$A2)="","",INDEX(Sheet1!B:B,$A2)))

    which returns the appropriate data, and this formula is copied across to Column K to get successive fields. Finally, the row of formulae from row 2 is copied down as far as you think you might need them (to row 10 in this case).

    The formula-based solution will respond immediately to changes in Sheet1 - set I4 or I6 to a number to see the effect on Sheet2, or set H5 to a number.

    Hope this helps.

    Pete
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-21-2015
    Location
    uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Filter down spreadsheet into another sheet based on a condition

    Quote Originally Posted by Pete_UK View Post
    In the attached file I have used column O in Sheet1 as a helper column (coloured blue), with this formula in O2:

    =IF(OR(H2="",I2=""),"-",IF(ISNUMBER(--SUBSTITUTE(H2&I2,"/","")),MAX(O$1:O1)+1,"-"))

    This is then copied down beyond your data to accommodate more data being added - the hyphens indicate how far the formula is active. The formula identifies the records which meet the criteria and gives each a unique sequential number.

    Then in Sheet2 I have used column A as another helper, with this formula in A2:

    =IFERROR(MATCH(ROWS($1:1),Sheet1!O:O,0),"")

    The formula finds the rows in Sheet1 where the records meet the criteria. B2 in this sheet contains this formula:

    =IF($A2="","",IF(INDEX(Sheet1!B:B,$A2)="","",INDEX(Sheet1!B:B,$A2)))

    which returns the appropriate data, and this formula is copied across to Column K to get successive fields. Finally, the row of formulae from row 2 is copied down as far as you think you might need them (to row 10 in this case).

    The formula-based solution will respond immediately to changes in Sheet1 - set I4 or I6 to a number to see the effect on Sheet2, or set H5 to a number.

    Hope this helps.

    Pete
    @Peter that is exactly what I am looking for. Thank you very much. I got assigned this task and I was really only familiar with simple math formulas. You have helped me a lot. I have been looking all over the net and most attempted solutions were all macro based (which I never used before) but this solution is great and very flexible. Thanks once again.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,411

    Re: Filter down spreadsheet into another sheet based on a condition

    You're welcome - glad to be of help.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  8. #8
    Registered User
    Join Date
    07-21-2015
    Location
    uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Filter down spreadsheet into another sheet based on a condition

    @Pete I added rep to earlier. I am curious to know.. which way is better... using the formula or a macro? How hard would it be to replicate the exact same thing you done in a macro?

  9. #9
    Registered User
    Join Date
    07-21-2015
    Location
    uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Filter down spreadsheet into another sheet based on a condition

    Quote Originally Posted by prabhuduraraj09 View Post
    Hi tyson,

    I have attached the sample file and below is the code for your reference.

    Do add Reputation, if i helped in someway.

    Please Login or Register  to view this content.
    When I try this code I get a run time error 1004 "method of range class failed", In debug mode it points to the last line before end sub. It still works as expected though?

  10. #10
    Registered User
    Join Date
    07-21-2015
    Location
    uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Filter down spreadsheet into another sheet based on a condition

    @Pete If you get the chance to provide a macro solution, please do. I have tried the other macro solution provided above but it does not have the flexibility that I need. The table that I have has 19 Columns and a few thousand rows (which can change). The above macro solution seems to be a set number.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,411

    Re: Filter down spreadsheet into another sheet based on a condition

    I used to do a lot of programming years ago, but now that I have retired I tend not to and it is very easy to forget how to do things if you are not doing them constantly - I tend to leave macro solutions to those who are more proficient.

    Pete

  12. #12
    Registered User
    Join Date
    07-21-2015
    Location
    uk
    MS-Off Ver
    2010
    Posts
    10

    Re: Filter down spreadsheet into another sheet based on a condition

    No problem Pete. Your input was highly valued - so thank you for that.

+ 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. Deleting rows on one sheet based on a condition in another sheet in the same file
    By Steve2107 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-18-2014, 06:48 AM
  2. [SOLVED] Filter based on multiple or condition
    By mangesh in forum Excel General
    Replies: 5
    Last Post: 11-06-2013, 03:30 AM
  3. [SOLVED] [Urgent] Inserting Time sheet Data in to Master sheet Based On the Condition Using Macros.
    By kkcmania in forum Excel Programming / VBA / Macros
    Replies: 42
    Last Post: 05-17-2013, 07:56 AM
  4. Replies: 2
    Last Post: 05-01-2013, 12:26 PM
  5. formula to copy a row to another spreadsheet based on a single condition
    By mirocarlo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2010, 12:06 PM
  6. Replies: 1
    Last Post: 04-15-2009, 09:44 AM
  7. Excel spreadsheet lock based on time and condition
    By coolanks in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2008, 03:29 AM

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