Artanoon all! In what I hope (and I assume is going to be the case to those with more knowledge than I on the matter - should be a large pool, in fairness) is an easy data manipulation query, I have the following conundrum but not much time I can spare to properly attack it in Excel, which, sadly and for whatever reason, is my main tool at home. Note: I do actually have MS Access, but it's been so long since I last used it, I might as well be starting from scratch. Outline I am copying text in from a webpage to Excel. The data comes in text format, from a table of sorts, but copies in as one column without delimiters (thus I can't use text to columns). As you'll see, it's a column of grouped data that I want to replicate as a table so I can add my own formulae. Looks like this*: And when I copy it into Excel, it comes out like this (my colouring): UEFA Europa League 1 X 2 Stats Feyenoord Celtic Today17:45 1.62 3.4 4.2 Lille Dinamo Zagreb Today17:45 1.4 3.75 6 Aston Villa Young Boys Today17:45 1.14 6 13 Objective I want to be able to compare the lowest odds in a match to the next lowest odds in a match and find the gap. So in the example above, I'd be comparing 1.62 to 3.4, 1.4 to 3.75 and 1.14 to 6. I can work out the gap and the rest of the analysis I need with formulae, but it's the laying out of the data that's the time sink. Problem (i) I need to recreate the table as it is on the webpage, with each match and the odds as separate entries, like this (wouldn't paste properly, so screenshot): Some formula or macro must be able to do this, but it has to be repeatable and obviously with macros, any deviation with the layout of the data results in a mess due to it using cell refs rather than any common sense, so I'm guessing macros ain't it. Problem (ii) Ideally, I'd like the data ordered from lowest odds to highest, but retaining whether or not the odds relate to home or away team or the draw. If I fudge the second match to show the draw as least likely and the last match to show the home/away odds in reverse for the sake of the example, I'm looking for something like this (I've highlighted the differences): If the numbers can't be re-ordered and keep the identifier (home/away/draw), I could try a nested IF function to work the difference out between the two lowest, but that starts to get messy. I think it would have to end up about six layers deep. I did wonder if a pivot table might help, but haven't dug into that one yet. I did also wonder whether there was anything of use in the web page code available via the "Inspect" or "View page source" right-click menu options, but I know less about that than I do nuclear fusion, so gave up immediately. I also thought about restarting Python learning because the chaps used to use scripts at work (I think it was R, more appropriately) but although that's also on my to-do list, it's obviously not a quick thing. So whaddya reckon? Is there a solution, or am I reduced to manual effort? Thanks in advance! +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *(I deliberately left the last match in there, because not only has nobody at the club thought "Hang on, everybody we play is going to think it funny that they're all off at the weekend to 'play with Young Boys'", but they also didn't stop to think "Here, we're called Young Boys, right? Although English might not be our first language, globally it's spoken "quite a lot". And I've just noticed that we're playing at a ground (almost exactly) called 'W@nked Off Stadium'. Probably not be a big deal if we were called Ajax or Barcelona. But we're not."). So of course, I found that funny.
I have done a quick and dirty macro to solve the first part. I can probably get the other bit done soon-ish, but I am being pestered a little for my day job. Code: Sub Sort() i = 7 j = 2 Do While Worksheets(1).Range("A" & i) <> "" Worksheets(1).Range("G" & j - 1) = "1" Worksheets(1).Range("H" & j - 1) = "X" Worksheets(1).Range("I" & j - 1) = "2" Worksheets(1).Range("E" & j) = Worksheets(1).Range("A" & i) Worksheets(1).Range("E" & j + 1) = Worksheets(1).Range("A" & i + 1) Worksheets(1).Range("F" & j) = Worksheets(1).Range("A" & i + 2) Worksheets(1).Range("G" & j) = Worksheets(1).Range("A" & i + 4) Worksheets(1).Range("H" & j) = Worksheets(1).Range("A" & i + 5) Worksheets(1).Range("I" & j) = Worksheets(1).Range("A" & i + 6) i = i + 8 j = j + 3 Loop End Sub Should work with the data in the format you posted above in column A
Ah, Midders - you're a star! And thank you. I'll run it through and post the output. EDIT: What have I missed?
You just need to remove Code: Sub Midders1() from the code as each Sub declaration is the start of a new function (essentially). Each requires its own End Sub line, which yours currently has only one of and wrapping subs within each other is a recipe for disaster
Aha! I did wonder if I had to add another End Sub - point noted on nesting those though. Cheers noiz. Removed as advised and have ended up with this output, so we're getting there, but it looks like I'm out by a cell or so. The Lille match is missing the home odds and has pulled Aston Villa up to its group, but Young Boys (no sniggering at the back) has been dropped from the Villa game and the next game down has been pulled into the Villa grouping. I tried reading through the code (I can read it better than I can write it) but I ended up in circles with the references. This is the post-run table:
Ok, looks like the data has come through without the extra blank lines and the initial headers. Couple of changes should fix it right up; Code: Sub Sort() i = 1 j = 2 Do While Worksheets(1).Range("A" & i) <> "" Worksheets(1).Range("G" & j - 1) = "1" Worksheets(1).Range("H" & j - 1) = "X" Worksheets(1).Range("I" & j - 1) = "2" Worksheets(1).Range("E" & j) = Worksheets(1).Range("A" & i) Worksheets(1).Range("E" & j + 1) = Worksheets(1).Range("A" & i + 1) Worksheets(1).Range("F" & j) = Worksheets(1).Range("A" & i + 2) Worksheets(1).Range("G" & j) = Worksheets(1).Range("A" & i + 3) Worksheets(1).Range("H" & j) = Worksheets(1).Range("A" & i + 4) Worksheets(1).Range("I" & j) = Worksheets(1).Range("A" & i + 5) i = i + 6 j = j + 3 Loop End Sub
Cheers Midders - and apologies for delay to next test. Repasted the above in just now and re-ran the macro, but nothing happened at all: I also owe you an apology - I think the issue you note above with the data going in without the extra blanks and headers must have been my copy/pasting technique, so appreciate you working around it! What's stopping the current code from running at all though?
