You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
something somehting; ASSEMBLE! (Ooooh, is that a coding joke ?)
(This could get very messy withthe site formatting; pray for me)
I get a weekly report in spreadsheet format that I can't change before I acquire it, so need to do this myself
What we want is to make it easily readable and sortable - with minimal time spent on it so I'd like to semi-automate it
If I was a bike thief and this was about STWers and their bikes, it'd be in the following format (colour's just for illustration):
... and I want it in the following (and then will delete some lines to tidy it up). Point is that I want to automate "copy country into this column from that single cell, down to where the entry changes and then use the new country" etc and I can't figure out what I want to ask for (I can make it populate the first line of any "country" or "Area" or "name" but I can't see what I can ask for to take care of the multiple entries per category
Grateful for any thoughts. I used Some messy-looking nested IF(ISNUMBER(SEARCH("country" type stuff to get as far as I did. I only really know vlookup and match/find and I don't think they'll do this
(I don't really speak macro at all)
ta
Gut feeling is that power query could work for this - get data from the current format then wrangle it into the layout you want.
Yeah, looks like a job for power query. Or as the recipient of the data, tell the provider to put it in the format you need, you are their “customer” after all!
Never heard of it yer honour - will do some reading. Thanks
I think my big problem it the "country" and Area" fields as (apasrt from those terms appearing each time) there's really nothing that the machine can use to identify them as different - as I see it
(the nature of the data below them is different - and I could teach an 8 year-old kid to do this but - I can't see how to make the software recognise and act on that)
Sadly this is the NHS. Took us several years to be allowed anything and then we got what we were given - the jobs list is long and we're not a priority
Macros are not as hard as they look!
You basically press record, do the stuff, press stop, and that should be it. You'll then find that it doesn't work for various reasons, but it's a good starting point.
I would probably filter the column to include 'contains country', then copy it to the left, do a find and replace to get rid of the word 'country' on the new column, etc. Not sure how I'd do the copying down for the new country column, off the top of my head!
I would probably filter the column to include ‘contains country’, then copy it to the left, do a find and replace to get rid of the word ‘country’ on the new column, etc
Yay - that's pretty much exactly what I did ! (except using that SEARCH and IF crap - does work even if it's ugly)
Not sure how I’d do the copying down for the new country column
Yeah - that
(it's not a massive report, so could do it manually but just feels like there's a better way)
I would go for macros by default although mostly c# nowadays.
A slightly ugly way would be as below. Switching out country as needed.
=IF(ISNUMBER(SEARCH("country", A2)),RIGHT(A2,LEN(A2)-SEARCH(":",A2)),F1)
You have to do it for all the rows (including the headers) so doesnt look quite how you want it and would be wrong for the first area and who in each set. However might be able to get round that with conditional formatting offsetting to blank out if it has a : in it.
Depends how you are actually using it though?
Can do conditional formatting on it by choosing formula for the rule and then.
=ISNUMBER(SEARCH(":",A2))
Switch the font to white and it will appear empty if printed/casually reviewed.
Xlookup might help. Do you use SharePoint?
Macros, Scripts, lookups or Power BI.
Could you ask the provider to send you separate files for each region or at least split the data ito columns. It can't be beyond their wit to do so.
I can't quite see the end solution at near midnight but I'd start with a "line type" marker so I knew which lines held which kinds of data. Then by checking the line type of the line above it it could identify of the entry was a new country etc or just to copy from above.
If I could get that structure right I'd then set that as a template file and paste in the raw data each time with the formulae pre-set.
Looking to learn power query as part of my development plan for this year. That has been suggested by others and may be better.
This is trivial in AppleScript. In fact just done it as an exercise, less than 2 dozen lines of code (although no error checking so will shit itself if the data is not in the expected format 😀)
If you use a Mac shoot me a PM & I'll send you the code.
don't really use Excel or indeed Windows so can't help with a more native solution otherwise but must be easily doable in VBA or one of the scripting languages mentioned?
Python or Perl for the win, working on an exported CSV or using an excel lib for full automation.
As zilog says, scripting is pretty straight forward.
I'm assuming you don't really need those repeated County/Area lines that have no data lines ?
"morning" all - and thanks for the thoughts
Afraid (in the medium term even) revised output is not a goer and no chance that I am going to be learning or allowed to use Python etc. I'm not a computery person at all really and the system at work is quite heavily restricted
Haloric (and others) is correct that the redundant rows in the second image will be deleted in the final version (I have done what somebody suggested and "marked" them for later deletion using the ":" (using a recorded macro! - I think I meant I don't speak VB when I said macro up above)
It's that extension downwards of the blue/brown country/region bits that I can't fathom
Sadly it will "have to" be in Excel (with no purchasable add-ons). We do have sharepoint - not seeing where that's heading though (that's how non-IT I am)
Zilog - your work will provide you with a flying car before mine offers me a mac 🤣
Move the data over to column D and down a row, then paste and drag something like this:
=IF(LEFT(D2;8)="country:";RIGHT(d2;LENGTH(d2)-8;a1)
(I'm translating from Spanish Excel so apologies if I get the function names wrong!)
Do something similar for the other two columns.
Not 100% automated but still pretty simple.
Edit: you might need a fourth column which will tell you when the data column doesn't contain either country, area or who, then use that to filter out the stuff you don't want.
It’s that extension downwards of the blue/brown country/region bits that I can’t fathom
You just need to look at the cell above and populate from there if you dont have a match.
=IF(ISNUMBER(SEARCH(“country”, targetcell)),RIGHT(targetcell,LEN(targetcell)-SEARCH(“:”,targetcell)),cellabove)
Add conditional formatting to turn it right if the targetcell has a : in it.
Then you will have something appearing how you want so long as you dont look in the formula box.
If you don't mind columns A B and C not having gaps between regions then this should work. Assuming your data begins in cell D2 with the first cell as Country:
In cell A2
=IF(ISNA(TEXTBEFORE(D2,":"))=TRUE,A1,IF(TEXTBEFORE(D2,":")="Country",TEXTAFTER(D2,":"),A1))
In cell B2
=IF(ISNA(TEXTBEFORE(D3,":"))=TRUE,B1,IF(TEXTBEFORE(D3,":")="Area",TEXTAFTER(D3,":"),B1))
In cell C2
=IF(ISNA(TEXTBEFORE(D2,":"))=TRUE,C1,IF(TEXTBEFORE(D2,":")="Country","",IF(TEXTBEFORE(D2,":")="Area","",IF(TEXTBEFORE(D2,":")="Who",TEXTAFTER(D2,":"),C1))))
Afraid (in the medium term even) revised output is not a goer and no chance that I am going to be learning or allowed to use Python etc. I’m not a computery person at all really and the system at work is quite heavily restricted
Lots of people in the NHS already using python and its free. You may even already have it installed. The learning curve will be slightly higher but the ability to maintain it when someone changes the input file will be infinitely more productive. You almost certainly can force excel to do it (I have done that sort of thing in the past). Its almost certainly more effort than you think.