I've been asked a question by a fried on how to create a file in excel.
I'm a basic excel user so I'm no expert in this but I'm sure there are some geniuses on here who can help.
I have a txt file with a date and a time (every day for a year and every 5 minutes starting 00:00) with a value (3 columns). The file needs augmenting with a value every minute (based on the current value and previous value). Calculating the values I'm OK with and have done that in subsequent columns in the inital sheet, but I'm struggling in getting all the values back into the single column in the correct order and with the date and time filled in.
Can anyone give me any pointers - remember I'm no expert so treat me as a simpleton 🙂
So your existing data is in txt, as a single column?
Date i
Time 0
Value a
Date ii
Time +5
Value b
Etc?
And you want to turn it [i]back[/i] into that?
Edit
Every minute for a year? even in format:
Date i. Time +0 Value a.
Date ii. Time +1. Value b.
That's over half a million lines. Why dear God do you want this in excel, let alone text?
You’ll need to explain more clearly to get a definitive answer, but CONCATENTATE is the formula for combining text. Although as noted above Excel is probably not the best tool for the job.
Sorry, no didn't explain very well.
It's 3 columns Date, time, value.
Every day from 01/01 to 31/12 and time every 5 minutes from 00:00 to 23:55, each with a value.
I need to add the extra minutes into the minute column and calculate the value
I.E.
Date Time Value
01/01/2018 00:00 1.1
01/01/2018 00:05 1.5
01/01/2018 00:10 1.9
and I need to get to
Date Time Value
[b]01/01/2018 00:00 1.1[/b]
01/01/2018 00:01 x.x
01/01/2018 00:02 x.x
01/01/2018 00:03 x.x
01/01/2018 00:04 x.x
[b]01/01/2018 00:05 1.5[/b]
01/01/2018 00:06 x.x
01/01/2018 00:07 x.x
01/01/2018 00:08 x.x
01/01/2018 00:09 x.x
[b]01/01/2018 00:10 1.9[/b]
etc
EDIT: It's not for me, trying to do a favour for a mate, and yeah I appreciate excel isn't the best tool for this, but it's all that's available to him.
Why do you want this in its new format? What's the goal, because in guessing 525,600 lines of data isn't the end game?
But...
[url= https://support.office.com/en-us/article/Fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db ]fill series[/url]
Fill column with date
Fill column with time
Fill column with the formula you're using to calculate values.
But. And this is the big but. Why?
If you can calculate your values, even if it's based on the two variables time and date and a starting value, you only need one line surely to do all of this? This is exactly why you don't want it in text?
I appreciate the comments.
Like I said, its not for me.
As far as I know the file will be read into a UI that is expecting the data in this format, so that's why it needs to be in that structure. It is used against real time live data so it's sort of a model to compare against the real data. Unfortunately that's as much as I know and I'm not in a position to change any of the structure or format.
Like I said, just a favour for a mate, but I do appreciate your input
Fair do.
In the link above search for "Fill cells with a series by using the Fill command"
Looks a complete ball ache to do in excel.
Be a few lines of code in python or similar
As Jambo says, so if he happens to have a Mac it will be trivial, if he uses Linux it will probably also be trivial but if he uses windows he’ll need to install python first (or find a virtual environment for it) but given there are even Android python environments that would still be much simpler than he probably realises (or use pythonanywhere). If he’s manipulating data these would be useful skills to learn - and with a few pointers it’s trivial.
It’s so trivial in python I would do it for you on Sunday (I am away)... just let me know how you are interpolating the missing minutes (ie is it straight line linear interpolation?)
to get the existing lines with actual data spaced correctly before autofilling you can either look up complicated macro code on the internet or else create a new 4th column and fill it with numbers 1-whatever right down to the last populated row (imagine it's D1 to D500)
Then copy that set of cells containing your numbers and paste it into cells starting at D501. Then do it again starting at cell D1001 and then again and again.
Then sort columns A:D by number value in column D
(oh, and "couldn't we do that with python" is what I'm going to say whenever I talk to an IT person)
No idea if it helps but I worked it out once and was very proud 8)
