How to separate out data from a cell single cell into multiple columns and rows-CodePudding

I have a spreadsheet where each row represents a single PC and a cell in each of these rows contains a list of all the log files that have been created over the last 30 days along with how many occurrences of an error were seen in each of those log files.

Here’s a couple of examples of the cell that contains the log files & errors, one for pc 297441 and one for PC 297897. I’ve only including a few days in this examples when normally this would show all the logs that were created over a 30 day period all in the single cell.

/cygdrive/c/app1/parent/297441/spfeed.log:0/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-06:0/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-07:0/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-08:2/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-09:0/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-10:1

/cygdrive/c/app1/parent/297897/spfeed.log:0/cygdrive/c/app1/parent/297897/spfeed.log.2022-09-06:3/cygdrive/c/app1/parent/297897/spfeed.log.2022-09-08:0/cygdrive/c/app1/parent/297897/spfeed.log.2022-09-09:0/cygdrive/c/app1/parent/297897/spfeed.log.2022-09-10:0

To make it easier to read I’ll add a few carriage returns to show each log files on a separate line but in the spreadsheet it’s one continuous line as above.

/cygdrive/c/app1/parent/297441/spfeed.log:0
/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-06:0
/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-07:0
/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-08:2
/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-09:0
/cygdrive/c/app1/parent/297441/spfeed.log.2022-09-10:1


/cygdrive/c/app1/parent/297897/spfeed.log:0
/cygdrive/c/app1/parent/297897/spfeed.log.2022-09-06:3
/cygdrive/c/app1/parent/297897/spfeed.log.2022-09-08:0
/cygdrive/c/app1/parent/297897/spfeed.log.2022-09-09:0
/cygdrive/c/app1/parent/297897/spfeed.log.2022-09-10:0

Here can you see each log file ends with the date it was created, the first log file doesn’t contain a date as that is the date when the logs were captured, so in this case it would have been 05/09/22. At the end of each log file is a : along with a number that shows how many times the error was seen.

So here we can see for 297441 we have log files for every day and we see the error record 2 times on the 08/09/22 and 1 time on the 10/09/22. For 297897 we didn’t get a log file for the 07/09/22 and we got 3 errors recorded on the 06/09/22.

What I’m looking to do is carry out some analysis on this, so for example graph how many times the error was seen over the month, which day had the most occurrences of the error and how many errors did each PC see over the month for instance.

In order to create a pivot table/chart so I can do that I think I need to separate out the single cell that contains all this information into separate rows and put each element into a separate column so it would look something like this.

PC                                      DATE                         ERROR COUNT
   
/cygdrive/c/app1/parent/297897/    ||   spfeed.log:              ||  0
/cygdrive/c/app1/parent/297897/    ||   spfeed.log.2022-09-06:   ||  3
/cygdrive/c/app1/parent/297897/    ||   spfeed.log.2022-09-08:   ||  0

The problem is I’ve got no idea how to do this. Someone helped me

Formula in C5:

=LET(a,REDUCE({"PC","DATE","ERROR COUNT"},A1:A2,LAMBDA(a,b,VSTACK(a,{"/cygdrive/","spfeed",""}&TEXTSPLIT(b,{"/spfeed",":"},"/cygdrive/",1,,"")&{"",":",""}))),IFERROR(--a,a))