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

Question

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 here separate out the error count which created a series of columns for each error. The problem with this is that each PC may not have the same number of log files because some days will be missing so I can’t for instance, say column 1 will always contain the errors for the 05/09/22, column 2 06/09/22 etc. The error count has to correspond to the day where it happened.

Does anyone have any ideas? Thanks

Edit

This looks it might do it but I realised I made a mistake when I was copying over the two example lines

The path should be /cygdrive/c/spfeed/parent/ and not /cygdrive/c/app1/parent/ I tried updating your formula but it doesn't work correctly. Would you mind updating it to work with this path as I must admit I don't fully understand your formula? Much appreciated if you could.

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

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

Answer 1

Here is one possibility using ms365's functions like REDUCE() and TEXTSPLIT():

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))