+ Reply to Thread
Results 1 to 19 of 19

Extract Cell Results for all Cells with Certain Values

  1. #1
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Extract Cell Results for all Cells with Certain Values

    Hi,

    I'm looking for something that can help me extract all the data for a certain value in a long list of data into a separate column.

    For example, in a soccer season the data might look something like this (3 separate columns)

    Arsenal | 2 | 1
    Manchester United | 0 | 3
    Liverpool | 0 | 2
    Arsenal | 3 | 1
    Everton | 1 | 2

    I'm looking for the data to appear in a new columns only for the Arsenal value as:

    Arsenal | 2 | 1
    Arsenal | 3 | 1

    I've managed to do this with formulas but it completely slows the whole spreadsheet down and makes it really difficult to work with.

    Is there any VBA I can use to help me please? Unfortunately, I don't have much knowledge of VBA only formulas.

    Thanks,
    Baron

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,926

    Re: Extract Cell Results for all Cells with Certain Values

    Have you tried AutoFilter?

  3. #3
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Re: Extract Cell Results for all Cells with Certain Values

    I might be missing something but that would filter the data in the column currently holding the data. Whereas I need the data in separate columns.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,926

    Re: Extract Cell Results for all Cells with Certain Values

    So you know how to filter...

    If you filter the rows, you can copy the result and paste to wherever you want.
    Am I missing something?

  5. #5
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Re: Extract Cell Results for all Cells with Certain Values

    But I will be doing this for 20 different values on a weekly basis so would prefer if it worked automatically,

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,926

    Re: Extract Cell Results for all Cells with Certain Values

    OK, then we need more info.

    If you upload a sample workbook clearly showing what you have and what you want to have would help.

  7. #7
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Re: Extract Cell Results for all Cells with Certain Values

    Ok, I have attached an example. I hope that shows what I need but please let me know if anything requires further explanation.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,926

    Re: Extract Cell Results for all Cells with Certain Values

    See if this is how you wanted.
    Please Login or Register  to view this content.
    Last edited by jindon; 07-18-2016 at 08:27 AM. Reason: Found a bug and fixed.

  9. #9
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Re: Extract Cell Results for all Cells with Certain Values

    Yes, that works a treat, thank you so much! There's just one small extra thing I need it to do and that's to sort each column/team by reverse date order, so the latest date first. Would that be possible?

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,926

    Re: Extract Cell Results for all Cells with Certain Values

    Then this should do
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Re: Extract Cell Results for all Cells with Certain Values

    Perfect, many thanks jindon!

  12. #12
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Re: Extract Cell Results for all Cells with Certain Values

    Hi jindon,

    I'm trying to apply your code to another sheet but don't seem to be getting anywhere unfortunately.

    I was wondering if you could help me make this new sheet do the same thing please? It has a bit more data but I want to do the same thing with the Home Team & Away Team and date in the Home xG and Away xG columns please.

    Many thanks,

    Baron
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,926

    Re: Extract Cell Results for all Cells with Certain Values

    Change to
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Re: Extract Cell Results for all Cells with Certain Values

    Thanks for the reply, jindon. I tried that but it just seems to stall and nothing happens or can be clicked in Excel. Any ideas?

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,926

    Re: Extract Cell Results for all Cells with Certain Values

    It is working here and very strange if it doesn't do anything.

    It should at least copy something.

  16. #16
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Re: Extract Cell Results for all Cells with Certain Values

    I think the issue might be that there is data in columns G-L (G-J are hidden). When I copy just columns A-F (which is the data the code is copying) to another sheet the code works but the copy starts from column H.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,926

    Re: Extract Cell Results for all Cells with Certain Values

    Try this with the original sheet.
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    07-18-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    10

    Re: Extract Cell Results for all Cells with Certain Values

    Same issue I'm afraid.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,926

    Re: Extract Cell Results for all Cells with Certain Values

    Here's a result from the code.
    Attached Files Attached Files

+ 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. [SOLVED] Formula to extract cell values depending on other cells
    By Gekko42 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-12-2016, 04:11 AM
  2. Replies: 9
    Last Post: 03-09-2015, 10:15 PM
  3. Replies: 2
    Last Post: 11-11-2014, 06:41 AM
  4. Replies: 3
    Last Post: 08-21-2013, 07:10 PM
  5. [SOLVED] Search a range for cells that have values and set value in another cell based on results
    By Catsup in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-25-2013, 04:57 PM
  6. Replies: 3
    Last Post: 07-25-2012, 07:54 AM
  7. Replies: 2
    Last Post: 01-09-2012, 02:36 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