+ Reply to Thread
Results 1 to 12 of 12

Query on how to find values from original table not in a selected table.

  1. #1
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Query on how to find values from original table not in a selected table.

    Hi all, first time posting here. I've been creating a football (soccer) database using pivot table, vlookup and index & match.

    All has been going well, but i've come to a stumbling block, I need to find the "missing" players from a match day 11+subs. Fig 1 shows the starting 11 and subs and the relevant data i've used using the terms above. I've had a trouble wording this query into google (and on here).

    Fig 1.

    Fig1.jpeg

    (I've another table which gets rid of the #ref's)

    Fig 2 shows the squad list for 2016 which is on another sheet, so I essentially want a table or range under fig. 1 which shows the players and their stats who aren't in the 11+subs.

    Fig 2.

    Fig 2.jpeg

    Any help would be fab

    Cheers,
    Jake
    Last edited by jake_111; 04-17-2017 at 07:20 PM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,410

    Re: Query on how to find values from original table not in a selected table.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Quang PT

  3. #3
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Query on how to find values from original table not in a selected table.

    Thanks for the reply. I have uploaded a mock example workbook.

    This sheet has the 11 players for the match and substitutes for the match.

    The "missings" underneath are the players who are not in the 11+subs. (Manually inputted)

    The squad is the list on the right, I don't want to have the players who have "release" next to their names in the "missings", as they're not with the team anymore.


    When a website lists the names for the matchday I can copy/paste them into my workbook (with a little editing) and it will automatically fill in the 2016 starts with index/match from a pivot table i've created. (In this example I've just used vlookup)

    So I guess i'm asking if it's possible to automate the 'missing' list where "Julia Elkholm" and the other three are.

    I hope this information helps!
    Attached Files Attached Files
    Last edited by jake_111; 04-18-2017 at 12:32 PM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Query on how to find values from original table not in a selected table.

    This proposed solution employs a helper column (P), which may be moved and/or hidden for aesthetic purposes. The formula that populates the helper column is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The names of the missing players are populated using the array entered formula*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Query on how to find values from original table not in a selected table.

    This works a treat, thank you so much JeteMc.

    I suppose the next question(s) I have would be:

    If this table was the only one I used to create the team list and missings in (for a whole competition), and there was to be another 20+ teams in it (the team info would follow down from the right under Hammarby)

    How would you incorporate it when you have numerous clubs and you just want it to focus on one side to pull the information from, rather than extending the formula to cover all the teams, as that would then pull the missings from anyone that wasn't in the 11+subs. I guess I'm looking for as much automation as possible.

    Thanks again for your help. I have uploaded an updated workbook with a couple more teams.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Query on how to find values from original table not in a selected table.

    If I understand I'd suggest another helper column, which could be hidden/moved for aesthetics, that would be populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula for the original helper column is then modified to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    When the club name in cell H5 is changed that will change the values in the original helper column so that it is only considering the roster of that club.
    The array entered formula for the missing players has the range increased so that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The range will need to be further increased once the other teams are added.
    Let us know if you have any questions.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Query on how to find values from original table not in a selected table.

    Sorry for late reply. This works really well thanks, have been playing around with it and now it's all working in my database.

    Thanks again JeteMc.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Query on how to find values from original table not in a selected table.

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your fist post. I hope that you have a blessed day.

  9. #9
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Query on how to find values from original table not in a selected table.

    Hello, sorry to bring this thread back up.

    I got my database working well using the formula's you gave me, however, I've tried to use it on a new database but cannot for the life of me work out why it's not working.

    I've upload a new workbook using the previous example names you did for me. But it's within a new database format. If you could work out the names under "missing" again that would be great. (I've removed the "release" part of the formula in AJ column as it's not needed).

    Cheers,
    Jake.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Query on how to find values from original table not in a selected table.

    Paste the following into the formula bar while cell T24 is selected:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Activate by simultaneously pressing the Ctrl, Shift and Enter keys before copying down the column.
    Let us know if you have any questions.

  11. #11
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Query on how to find values from original table not in a selected table.

    Thanks again JeteMc, got it working in the new DB.

    Is there any chance you could tell me how you worked that out? I can see the additional formula, but no idea how you came to that conclusion. I can see me having to ask again otherwise (haha!).

    Cheers,
    Jake

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Query on how to find values from original table not in a selected table.

    I believe that you are talking about the ...ROW(A$4:A$95)-MIN(ROW(A$4:A$95))+1... part of the formula.
    My objective in this part of the formula is to get an array of numbers beginning at 1, because I need SMALL to tell INDEX the first true is found in the nth row of the range.
    I could have put in ...ROW(A$4:A$95)-3..., and it would have worked just as well. However some respected formula writers feel that there may be some benefit in writing a formula that is of the form ...ROW(range)-MIN(ROW(range))+1... (rows in the range minus the minimum row in the range plus one). Here range is the rows containing the data (in this case the roster) involved so that you have a format that is easier to remember. (I hope that makes sense).
    Guess the bottom line is that it is stylistic, at least in my view. Perhaps someone will chime in and do a better job of explaining why that syntax is better.

+ 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: 05-14-2016, 03:05 PM
  2. Reset to original values in pivot table
    By lbabli in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 03-21-2014, 02:18 PM
  3. Replies: 2
    Last Post: 11-22-2013, 02:52 PM
  4. Re-sort table without affecting original table?
    By jfrog in forum Excel General
    Replies: 1
    Last Post: 01-30-2013, 01:19 PM
  5. Replies: 0
    Last Post: 12-05-2012, 03:48 PM
  6. Replies: 0
    Last Post: 09-05-2012, 06:12 PM
  7. Creating new table from selective data in original table
    By rasmussk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2009, 09:46 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