Macro to transpose unstructured data

[Chouz06]

Hi All,

So i have been sent unstructured data which i need in a nice format to analyse. I have attached what i would like the data to look like (the part at the bottom of the image).

I dont know if there is any code which would help in getting my data to look in that format. Appreciate any help

Thank you!

 

[CJ_London]

to clarify - is this an excel or an access question?

If excel, have you looked at using the transpose or pivot functionality

Transpose (rotate) data from rows to columns or vice versa - Microsoft Support

How to use Transpose in Excel to rotate columns and rows to rearrange your worksheet data.

support.microsoft.com

For access, data should be stored per your bottom table, not the top one. To convert, consider using a union query

 

[Pat Hartman]

If you only have to do this once, the quickest way is to create an append query. Define the target table. Then run the query 7 times, once for each date column and adjust the value for the date field accordingly and change the source column for the hours

 

[Uncle Gizmo]

If the data is in MS Access then my transpose tool should work:-

Normalization Tool - Nifty Access

Normalization Tool Introducing the "Transpose Tool" or "Database Normalization Tool," your go-to solution for seamlessly converting spreadsheet-structured data into MS Access-structured data. This transformation not only equips you to construct exceptionally powerful MS Access databases, but is...

www.niftyaccess.com

Use coupon code t0cpajo to get a free copy .....

You will need to add an ID field to the top table .

If it's in Excel, I understand from previous similar questions here that there's something called "power query", which I understand can transpose.