# Returning ordered values from a string in a single cell

1. ## 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?

2. ## 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

3. ## 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.

4. ## 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. ## 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.

6. ## 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.

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