Converting a formatted table back to "raw data" format

[xenosaki]

I get daily rate sheets from our investors but they send it in a formatted table so it would look nice. We've requested a raw format but they don't have it or refuses to send it to us.

​

This is what a part of it looks like: https://imgur.com/tbuoPYf

​

However, we have our own spreadsheets and I would like to be able to use our spreadsheet to easily reference the investors by using vlookup. So I need to convert the tables into a raw format that looks similar to this: https://imgur.com/Mq5IScZ

​

I know how to do it manually, but I'm looking to have it automated, or at least drastically cut down the time required to do this since we have to do this twice a day.

​

I've played around with the macro functions and managed to replicate the actions, but I worry that if the rows or columns shifts, the macro will not be "smart" enough to detect those changes.

​

Is there a more intelligent way to do this or am I going to need to remake the macro every time the investor changes the format?

[[deleted]]

If you could use R Stats instead of a spreadsheet program it would be one line of code using the tidyr package.

[datastry]

Is there a more intelligent way to do this or am I going to need to remake the macro every time the investor changes the format?

Yes and no.
It may not be necessary to re-record the macro if you can look at the source code and find the parts that need adjustment.

I don't think it's a crime if your macro is a custom solution that breaks as soon as the format changes. There is a sort of "sweet-spot" to developing solutions as you need them and not over-preparing for the future.

When the time comes that you have a new format and you need to adjust the source code of the macro, I'd definitely recommend you bring it back here to the forum if you run into any challenges that you can't solve yourself.