Need Macro to clean the data and arrange the data
- Thread starter rajreddy
- Start date
mohamed ilyas
Member
hi
power query can help this.. check.
power query can help this.. check.
Initially i was looking for a different solution using Python to clean the raw data, but your recommended solution is the easiest one, it is working great,
on the macro solution you have shared, when I have pasted a different data it is not working with 1 hour interval, can you please look into it.
I have attached the file.
rajreddy
Did You read my #2 reply's the first sentence?Why do You need to ... clean that raw data at all?
It's more useful than Your now used.
How did You paste a different data?
... Your above file has more differences than ... I have pasted a different data!
I can look it and I need answers,
... eg why do You need to do extra steps?
Marc L
Excel Ninja
Hi, why your result contains a single agent ?‼
Edit : where should be located the VBA procedure ?
I have a weekly data which changes each week and the duration can be max of 9 hours duration with different activities, so every week I paste a new data and it should show the output you have provided using the macro, sorry for any confusion..
p45cal
Well-Known Member
The attached fetches data from another Excel workbook, not from itself.
The table at cell B2 is a Power Query query which, when refreshed (right-click the table and choose Refresh):
So quite a lot that can go wrong!
Since it is unlikely that the workbook you want to fetch data from has the same name and location as C:\Users\Public\Documents\Macro Need to clean the data.xlsx, you can alter that location and name manually in cell M1 before refreshing the query.
To make this easier, there's a small macro that runs if you press the button Button 1 (at cell M2), which:
It should work with any dates and you shouldn't get any duplicates (unless they're in the original data, of course).
It could be made more robust.
It might be easier still; you say you paste the data in - so where is that data pasted from? It's likely you'll be able to query that data more directly without needing to paste it into Excel…
Tell me how you get on.
The table at cell B2 is a Power Query query which, when refreshed (right-click the table and choose Refresh):
- Uses the path and file name in cell M1 (M1 is a single celled named range called PathAndFilename)
- Expects there to be a sheet called "Raw Data" in that workbook
- Expects the data in that sheet to start in column B
So quite a lot that can go wrong!
Since it is unlikely that the workbook you want to fetch data from has the same name and location as C:\Users\Public\Documents\Macro Need to clean the data.xlsx, you can alter that location and name manually in cell M1 before refreshing the query.
To make this easier, there's a small macro that runs if you press the button Button 1 (at cell M2), which:
- let's you browse for the file
- puts the location and file name in cell M1
- refreshes the query
It should work with any dates and you shouldn't get any duplicates (unless they're in the original data, of course).
It could be made more robust.
It might be easier still; you say you paste the data in - so where is that data pasted from? It's likely you'll be able to query that data more directly without needing to paste it into Excel…
Tell me how you get on.