Forum Discussion
Reverse data, and automatically copy in values
Hi!
we have a time registration system which do not quite give us the reports that we need. Therefore, I have set up an excel template to edit the data as we want it to. However, I need help with automatically reversing the data. (they come by dates newest-oldest, id like it to be oldest-newest).
As I have currently set It up I have 3 spreadsheets where the first is the raw data, second is a work sheet where the data is edited, and the third is the desired outcome.
It is also a problem that the data in the third sheet are only formulas as they are to be manually edited later on. I know this is a quick fix by copying the data, and pasting in only thee values, but is there a way to do this automatically aswell?
I'd be open to suggestion if someone have a better method of solving this problem as a whole.
in advance, Thank you!
Mac version: MacOS Montery 12.1
Excel: 16.58
6 Replies
KnutHarsjoen Difficult to help based on your description only. Can you share (OneDrive, DropBox or similar) a file that is structured like your real one with some dummy data and how you want it ot look like?
Riny_van_Eekelenhttps://broharsjoen-my.sharepoint.com/:x:/g/personal/knut_brharsjoen_no/EZaKqIRACNxNsAQ6xi-jLCEBeEZpLeywV44PknwD8cLLrQ?e=U8dQTB
Here is the file, and how I shows in the template after I paste in the raw material. The only difference in the desired outcome is that the dates come in order automatically. As you can see in the file the newest date comes first. I would also like for the data to be copied in as only the values in the last spreadsheet, so you can edit the text manually.Another smaller problem is that the raw data vary in size, so in the last sheet the rows not used appears as 0 since the formulas are not used. It's not a big deal since I can only delete them, but is there a way where I can make them automatically dissapear if not used?
So "Ark1" contains the output from the time system. "Ark2" links cell-by-cell to "Ark1". And, in turn the "Detail" sheet links to "Ark2". Not sure why you would have these extra steps. Why not just take the data in Ark1, get rid of the formatting and transform the data into a structured table. And then just sort all by date in ascending order and do the edits you need/want.
Being on Mac you don't have the option to automate such "transformations" via Power Query, so you would need VBA if you can't do what I described above. But VBA is not my thing. Sorry.
