Log In
Register
Home
July 29, 2024
Offline
My company uses an excel sheet to create quality alerts. The data I need to extract from all of these alerts is in different cells with no headers. The data IS in the same cell for each alert (date, non-conformance, machine number, etc.) Some of the data cells are merged Is it possible to extract this data into one basic sheet with headers so I can create a dashboard?
See AnswerJuly 29, 2024
Offline
Quality Alert
Moderators
January 31, 2022
Online
Please upload a file (or files) that demonstrate the actual structure (or lack thereof) of the data. Without that, it's difficult to help.
July 29, 2024
Offline
Here you go.
Moderators
January 31, 2022
Online
Thanks for the file. But now please explain what you want to do with it/them.
Say, you have 10 of these sheets what data would have to be extracted to your dashboard?
July 29, 2024
Offline
Rows 4 and 5 (shift, author, date, location, equipment, kanban, and part number) and rows 22 through 24 which are merged and use a dropdown menu to select the non-conformance.
Thank you very much for your help!
July 29, 2024
Offline
Any suggestionss?
Moderators
January 31, 2022
Online
I didn't notice your post number 6 until now.
Sorry for being ignorant, but I don't know what you mean. what would you want to extract and how should the end result look like?
July 29, 2024
Offline
I want to use power query to make this attachment.....
July 29, 2024
Offline
Look like this attachment. I have hundreds of them to pull. I am concerned about the "SPLIT" field because it is merged cells.
July 29, 2024
Offline
Come on dude. You ain't even trying on this one. Send an administrator.
Moderators
January 31, 2022
Online
I'm afraid PQ can't create a schedule like you attached to post nr 9. It's a formatted report with boarders, colors, a picture, checkboxes, data validation etc. PQ loads data back to Excel in a tabular format, which would then be the basis for further analysis.
You could use PQ to pick-up all the data from separate files (like the example.xlsx) and create one large table. Then I would consider to use Mail Merge in Word to create the forms you need. Just an idea.
And as a general note on merged cells. These do more harm then good and should be avoided as much as possible.
PS: Please don't call me 'dude'!!
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I assume you want something like the attached file. It's just a quick fix solution that might work with your real data.
In this example I saved your 58314-0A020-split-07-11-24.xlsx three times as 1.xlsx, 2.xlsx and 3.xlsx to C:\Temp\1 folder. Did some minor changes in 2 and 3 files just to have some differences.
As always, it is better to adjust so that the reported data is entered in a correct format than to try to correct it later on.
Any way, I hope this gives some help on your way forward.
Br,
Anders
Answers Post
Moderators
January 31, 2022
Online
Makes sense Anders! I totally misread the OP's intentions. Tack så mycket!!
July 29, 2024
Offline
Anders,
Thank you very much! Your example shows exactly what I want to do. I need to query hundreds of these Quality Alerts. I did more research and learned a bit about removing merged cells when transforming as I was worried about the merged cells in rows 22-24. For this Quality Alert, it resulted in both the shift and the unmerged "SPLIT" cell being in the same column.
Best regards,
Bradley
October 5, 2010
Offline
Come on dude. You ain't even trying on this one. Send an administrator.
Hi Bradley,
Please have some respect and patience. You are getting free help here.
regards
Phil
VIP
Trusted Members
December 7, 2016
Offline
Hello Bradley,
Great that it works as you want. As long as the structure is the same in all files, i.e. the number of rows and columns are the same, it will work. The file I provided was just a simple quick fix solution.
With Power Query you can do a lot and if you take time and learn how to use the advanced editor and work with the M functions, then you are set to do advanced stuff.
Br,
Anders
1 Guest(s)
