You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Task 1 – Excel Task
This task involves some complex data manipulation in Excel. The values in columns A, C, and D are correct, but there are some issues with column B. Some cells in column B have multiple comma delimited values. We would like to split any cells with multiple values into that many rows. Each new row should contain the same information in columns A, C, D.
So this:
Department Course code CODE SumOfActivity Hours
Royal Birmingham Conservatoire PT1256-01-SEP-1-2020-1, PT1257-01-SEP-1-2020-1 X7 541Would become this:
Department Course code CODE SumOfActivity Hours
Royal Birmingham Conservatoire PT1256-01-SEP-1-2020-1 X7 541
Royal Birmingham Conservatoire PT1257-01-SEP-1-2020-1 X7 541This would take a long time to do manually, but can be accomplished in under 5 minutes.
It definitely took me longer than 5 minutes but I can't decide how I could have done it quicker and I always like learning to be more efficient and tricks. I went for a data > text to columns split by a comma and had the output put to the right of all the info so that I didnt need to work out how many blank cells to insert. I then went for inserting the correct amount of blank rows, transposed the horiztonal data to vertical and copied the data down into the cells using the ctrl button so that excel didnt add one each time.
How would other wizards or queens have done it?
I wouldn't have used excel 🙂
Inserting rows, yuck. What are you like with VBA?
I'd try copying column B to a new worksheet and save it as a CSV
When you read the CSV back in I'd 'hope' the commas would be interpreted as columns.
Then merge back in to the original.
Edit - oops re-reads question. You then need to turn columns into rows somehow
i'd send it back to the person who created it and ask them to do it properly...
I wouldn’t have used excel
Ditto.
Power Query is your friend here - stick the original data into a table (Ctrl T) and use Power Query with that table as a source (Data/Get Data/Other Sources/Table with that table selected), then use the Split Columns function by delimiter and in the advanced options split into rows. If you've been given 5 mins go and make a cuppa with the remaining 4.
VBA - extremely basic but would like to learn more if possible. Hmmm power query sounds interesting but is something that needs installing and I'm guessing they wanted it done using just the basic excel. Sounds like I've done it the only way, will be interested to hear how they'd have done it then!
could you have done a Find & Replace and put a carriage return in place of a comma?
Export CSV - > perl -> Import CSV
Make me a csv, I'll send you the perl !
Surely part of learning to use Excel should be learning when it's not the right tool for the job?
or small one line macro? something like?
replace (",", &vbcr)
I'd use VBA, get it to look for a comma in each row, if one exists insert new row, populate with data from the row above, clean up the previous row.
Sounds like I’ve done it the only way, will be interested to hear how they’d have done it then!
I would be interested too. For a one off that wasn’t massive I’d do much the same as you
I’d use VBA, get it to look for a comma in each row, if one exists insert new row, populate with data from the row above, clean up the previous row.
Is what I’d do if there were a large amount of data or I was going to do it more than once
Surely part of learning to use Excel should be learning when it’s not the right tool for the job?
I'm curious for myself, what tool would you recommend in this instance?
I am far from an excel ninja. In situations similar to that posed by the OP I've been using Power Query. Any other tips or tools that can make life easier I'd gladly look at.
tool would you recommend
Before recommending a tool he may have to ask what the job is