+ Reply to Thread
Results 1 to 6 of 6

Returning ordered values from a string in a single cell

  1. #1
    Registered User
    Join Date
    04-15-2010
    Location
    Chicago
    MS-Off Ver
    Office 365
    Posts
    17

    Returning ordered values from a string in a single cell

    I'm trying to figure out how to order values within a string. I'm connecting to a web source that will provide a string return to a single cell with something along the lines of (9, 4.5, 3.8, 10.3, 0, 23.8)

    There are ways to make this dirty and parse those numbers into their own cells and use the LARGE() function, but I would like to be able to consolidate the formula into 1 cell and pull out the numbers into their own cells in order from highest to lowest.

    Any thoughts?
    Last edited by Dissonant; 10-23-2010 at 01:44 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Returning ordered values from a string in a single cell

    The attached returns the numbers within the string in an order of choice (assumed Ascending unless optional final parameter set to TRUE.

    The basic UDF is:

    Please Login or Register  to view this content.
    and as you can see the real work (sorting the Array) is conducted by QSortInPlace which is c/o Chip Pearson and is documented here

    The above would be called from the cell along the lines of:

    Please Login or Register  to view this content.
    where [,TRUE] denotes optional
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-15-2010
    Location
    Chicago
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Returning ordered values from a string in a single cell

    It looked like I could make this work, but it appears that the function i'm using to put together the string of numbers (4.5, 6, 8.7, 9.2, 10.5, 23, etc...) isn't returning the values as numbers so your function won't work. Ideally I'd like a formula where I could call a number position IE. 1, 2, 3.. Highest, Second Highest, Third Highest and just return that single value to a cell.

    I'll attach a portion of the spreadsheet so you can see exactly what I'm doing. This is for a fantasy football league and I'm trying to create a formula in excel to determine a person's optimal lineup in a given week (highest possible score using bench players in place of a starter who didn't do as well).

    I'm using a web query to download the data directly from ESPN, but I'm not really experienced with that. This is probably a topic for another post though. I'm sure there are ways I could streamline how i'm doing that as well....

    Anyway, I'm currently using 2 functions in the sheet.

    GetElement:
    Please Login or Register  to view this content.
    and FindSeries:
    Please Login or Register  to view this content.
    You can see on the 2nd sheet what I'd like the results to be. I need to search the array for the highest QB score, the 2 highest RB scores, the 2 highest WR scores, the highest total for the flex slot (3rd RB or 3rd WR, whichever is higher), the 2 highest DP's, and the highest K, TE and D/ST.

    Typically there will be more variety than just WR and RB on the bench. In Column C you'll notice that I used GetElement to pull the player's position from their name line. I used an IF statement because defensive players are listed by their actual position but I need their value returned as DP.

    I probably should have disclosed all of this from the start. Thank you very much for your work on this so far. Any additional help you could provide would be very much appreciated.
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Returning ordered values from a string in a single cell

    Regards Column C you could retrieve the 4th "element" using:

    Please Login or Register  to view this content.
    Of course if you have other DP's other than Defensive End & Linebacker then the above would need tweaking.

    Regards the values ...

    So are you saying that you only ever have 19 players listed but the no. of positions on the bench will vary meaning you may have >1 QB on the list ?

    So you're essentially trying to generate the correct 11 + 8 based on the requisite combination of positions ?

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Returning ordered values from a string in a single cell

    Assuming the above assumptions are correct...

    Please Login or Register  to view this content.
    It follows that F5 is slightly different so overwrite the above and add the following version:

    Please Login or Register  to view this content.
    Assuming you then want the players associated with those "best" scores

    Please Login or Register  to view this content.
    and again, per F5, G5 needs to be altered slightly from the above version

    Please Login or Register  to view this content.
    Attached is a sample with all of the above - all VBA removed.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-15-2010
    Location
    Chicago
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Returning ordered values from a string in a single cell

    You went above and beyond! I wasn't even planning to grab the names associated with the scores It looks like this is in working order. Thank you very much! Now on to my next task, efficiently grabbing data from the ESPN site.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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