Parsing/Restructuring Horizontal Table Data

[JMart2135]

Hello Everyone,

I have been asked to parse and standardize some data to be used for metrics generation. Problem is, whoever set this data up... never meant for it to be used this way.

The data shown in the screenshot is an example of the horizontal table that gets updated so it continues to grow to the right. I need to be able to drop the columns where the [Month] in row 1 has a  "total" value that is null.

Then I need to somehow take the data from each group of three columns ([Month], [Error Code], and [Comments]) and end up with a table with the following headers.

Month, Location Name, Person Name, Desc_Count, Error Code, and Comments.

I need this to auto select any new columns monthly as the table continues to grow. I tried splitting the top 3 rows then the "desc" rows using the sample tool the used the auto field-->auto select tool trick of dropping the booleans, however then my top 3 rows are no longer aligned to try and union them back together. Really stuck here. Almost need to try and drop the null columns then pivot the columns in counts of 3, i think? In the end, hopefully this will only be needed shortly as i try and convince them to change the table layout!

Thanks for the help, it is much appreciated!

[mceleavey]

Hi @JMart2135 ,

Can you provide the spreadsheet with some mock data representing the monthly change and I can build it for you.

M.

[JMart2135]

@mceleavey Thank you so much for the help. Attached is a dummy file. "Initial" and "Monthly Update" sheets show how the data would change from one month to the next. I also included what a final version of the data should look like for each of those 2 tabs.

[JMart2135]

.

[JMart2135]

Anyone else able to help? Havent had any response from @mceleavey 

[atcodedog05]

Hi @JMart2135 ,

Let me take a look into it and revert back in some time.

[JMart2135]

@atcodedog05  Thanks, i appreciate it. There is a sample of the month to month changes and what i am trying to accomplish as far as final output a couple of posts up called "Dummy file.xlsx"

[atcodedog05]

Hi @JMart2135 

Sorry your data is really complex it might take a while 😐

[JMart2135]

No problem, thanks for looking at it. I am still working it as well and trying to see what i can come up with.

[JMart2135]

@atcodedog05 or anyone who can help, its not pretty but i think i am very close to a solution. i had to break out the data into a couple of different sets to get this far. the last thing im trying to do is restructure this last piece of the data.

i feel like it should be easy, however i might have been staring at this so long that its just not clicking. The table at the top of the picture below is my data as it is at this current step. I need to get it transposed/pivoted, in a dynamic way since this horizontal table will continue to be updated, to look like the table at the bottom of the picture. if i can do this then i can join by record position back to the other half of the data with the key.