Requesting help to extract particular data from (complex) Excel sheet.

[Elijah]

Hi all,

I have a rather complex sheet (table) from which I'd like to extract data to create statistics. Please see attached file. Sheet labeled "Source" is the original. I'm particularly interested in Swing 1, 2, 3, ...etc. Time 1,2,3,... etc. is also of interest, but not a priority. Maybe the final solution for Swings can be used for that one also...

I've been gathering statistics on this by copy and pasting manually, but I find that in the end it's too time consuming, so I need to find a better way to accomplish this. Ultimately, I'd want to create bins of various sizes and make a frequency count/histograms.

I don't completely envision how the end result would be, but, I imagine if I could somehow create a list/column(s) containing all these cell values, I could use that to make a count. One problem is that I might want to isolate this day-by-day. Also, every row have a different # of swings, making this task even harder.

Any ideas at all?

The first step I did on my own was to create a linked sheet which simply extracts the data of interest (Swings). Maybe something workable can be done from there...

PS: It would also be great if lookback period can be user-defined, i.e., last 20 days or entire sheet. I've set up something similar in other sheets when doing counts by simply using the OFFSET function.

Thanks in advance for all and any help!

Elijah

[Glenn Kennedy]

Copy the date column. Select R2. Then Paste special checking "values" and checking "transpose".

In r3:
=IFERROR(INDEX(OFFSET($7:$7,COLUMNS($R:R)-1,,,),AGGREGATE(15,6,COLUMN($C$7:$O$7)/(OFFSET($C$7:$O$7,COLUMNS($R:R)-1,,,)<>""),ROWS($1:1))),"")

copy across to the end of your data and down 13 rows

[Elijah]

Great! Thanks a lot, Glenn Kennedy. As far as I can tell by taking a quick look, you completed it for me.

Regardless, this is an improvement from what I have had until now. I see one potential problem with this solution: It can't be updated dynamically.

I didn't specify this in the original thread, so my bad. But this sheet is typically updated daily or at least weekly.

Could a solution be made such that updating the source sheet which in turn updates the LinkedSheet also updates the summary columns?

PS: If not. No worries. I can work with this.

Thanks in advance.

[José Augusto]

See if this solution helps you

In Row 7 of 'LinkedSheet' use the following formulas for Column A, B, D and E:

Please Login or Register  to view this content.

Copy formulas to Row 10
In C7:C10 use T, S, P, E as flag for Time, Swing, Price, End

Select A7:E10 copy to A7:E76238

Column A is a helper column

Your data is in a table with Date, Flag, # and value

See the file
Note: In the file only A7:E10 has formulas to shorter the file size.

[Elijah]

Hi, José!

Thank you! That is indeed a very clever and advanced solution. But I'm not sure if it helps me...

It seems like the way you set up the sheet, I have every day in one row, but I still have to select which swing I would want. So, I don't have access to all swings for one single day without looking it up. It's a clever solution I might find use for, but maybe not for exactly what I wanted now...

Ultimately, I'd want to create statistics similar to the picture below (I didn't have time to upload a sample sheet now):

Summary.png

As can be seen, I've created bins in column A and done a frequency count. For this, I've made a histogram as can bee seen. I also calculate other statistics like number, average, max/min, etc.

To do this frequency count, I imagine I'd have to have everything neatly organized like in a list.

The suggestion by Glenn seems very workable, except that I would have to manually update it whenever the tables are updated.

Best regards and thanks.

[Glenn Kennedy]

How does this look?

[Glenn Kennedy]

Here's a non volatile version (avoiding the use of OFFSET).

[José Augusto]

Hi Elijah

No problem and thanks for the feedback.

Regards

[Elijah]

Hi,

Thanks a lot, GlennKennedy! It looks great!

First sheet does not seem to auto-update when I add a new row.

The second sheet auto-updates nicely. So, mission accomplished, I guess. : )

I’m not sure how or why you used the OFFSET function. In fact, the formulas you’re using is a bit above my head to be honest. But they seem to do what the should be doing.

When I mentioned OFFSET, what I meant is that it would be nice for me if I could create «dynamic» statistics using a dynamic lookback period. I have been able to do this in my other tables, when the data is presented in rows similar to the SOURCE sheet.
I’m at work and don’t recall the exact formula, but I have one cell reference the lookback period and the formula also says which row to start from. That would be row 7 on the source sheet. This could be for example N = 20.

The formula would then return the average of the 20 cells down from row 7.

Now that the days are set up in columns instead, I would find a way to use the OFFSET (or some other formula) to return an average based on N to the left, starting at column A.

If you or anyone else have any ideas, please let me know. I might figure it out tinkering on my own after work tonight.

Thanks in advance and thanks for what you’ve done so far!

Best regards.

Elijah

I'm attaching a sheet with the average for N = 5. How I envision it. Would need the dynamic formula though. As mentioned, I can figure it out for row. Just not sure about columns...

[Glenn Kennedy]

See sheet Last20. This was the approach that I originally took. Since col B took about 15 mins to calculate the whole Table, I abandoned that approach. However, if all you want is the most recent (top) 20 values, then it becomes workable again.

[Elijah]

Hi, Glenn,

Really appreciate your efforts.

Your solution is interesting, but not quite what I had in mind. It can work of course. Let me think on it. Just checking in now before heading out again.

I'm attaching a sample sheet which shows how I've accomplished "dynamic" lookback period for similar data when stored in rows. This is time data sorted in bins. This data set is of course easier since it has only one value per day.

Changing the value in cell B3 changes lookback period. Last 20 days or whole sheet (1444 rows) takes the same time to calculate basically.

I'm wondering if I could set up a similar formula for the summary sheet you made in Sheet 1 above.

For example, if I want to calculate average values and the lookback period in cell B20 = 5, the formula would average A4:E16.

If B20 = 20, the formula would average A4:T16.

I hope (and think) the OFFSET function could be set up this way also, but I don't have time to look at it in detail right now.

In addition to average, I would also want to make frequency counts and histograms as show in the sheet I'm attaching. As long as the general OFFSET solution can be found, I think that also can be done.

Thanks again.

[Glenn Kennedy]

It is possible to get column A and column B, as shown in your attachment at Post 11, with as many rows per day as necessary, from your raw data: but - as I said at Post 10 - it will take up tp 15 minutes or so to calculate when you select "All" as an option

In Post 9 you talked about an average of the last 20. That is what I gave you at Post 10. It can easily be adjusted to cover any number.... but the more points you want, the longer it will take.

You seem (to me) to be continuously changing your requirements. So take a deep breath, think aboiut it and let me know DEFINITIVELY what you want and (if you - potentially - want all rows) whether you'r prepared to go away and do something else while your PC works on it.

Then, I'll give it one more go.

[Elijah]

Hi, Glenn,

I think perhaps I figured this out on my own this morning using OFFSET.

I don't think my requirements have changed, but as I said initially, I didn't quite envision how the end result would look like. I imagined the first step would be to isolate the numbers of interest in a separate table.
For this, you arrived with an elegant solution.

I'm attaching my (almost) final solution. Lookback period can be defined in cell B2. Calculating 20 days or 1467 takes the same time. I haven't double checked my calculations, but it does seem to be correct at a glance.

Also, I ABS-ed your formula. I just added ABS in front of the formula in cell A29, marked the entire row and dragged it down to the bottom. It appears to be correct. Please comment if not.

The final step for me would be to replicate the same thing for Time 1, Time 2, Time 3, etc. I tried that in sheet Time and it does appear to be corect also.

Also - the way your formula is set up, can I update sheet Source with new rows and also have the formula extend one cell further to the right end of the sheet when updating or would I need to extend the formula manually?

Best regards and thanks in advance!

[Elijah]

Silence equals agreement?

I think I've managed to replicate this in the sheet I will be using. It was kind of slow when updating and my Excel seemed to crash a little, but I guess I can live with that as the time saved is huge already.

I had some questions about the formula used though in order to not have any errors I'm not able to spot myself. I'm now referring to the last sheet on this, Post 10 by Glenn. First formula (which is dragged sideways and down) in cell A4:

HVISFEIL(1/(1/INDEKS(Tabell25[[Swing 1]:[Swing 13]];KOLONNER($A:A);1+4*(RADER($7:7)-1)));"")

This is in Norwegian code. Sorry about that.

Why Row 7?

Thanks!

[Elijah]

Hello again,

So, the way GlennKennedy extracted values from Source sheet and set it up in columns is GREAT. In fact, I completed my sheet with statistics and frequency count using that set-up.

The problem now is that my sheet is crashing and/or takes forever (10 minutes +) to load new data and I suspect the formula for extracting values from Source sheet might be the problem?

Is there a solution for this? Or perhaps some other way of achieving the same result?

Thanks so much in advance.

Elijah

[Glenn Kennedy]

1. Silence means nothing other than I've been away in the real world doing useful things (brewing beer, today; spilling it on the floor yesterday to the extreme annoyance of she-who-must-be-obeyed....).

2. ABS is fine.

3. Are you still using the sheet from Post 11? I have just copied all of the raw data to a blank workbook, deleted all the data and then pasted it back in. It took just about 1.5 seconds to calculate. My PC is geeting elderly and was an off-the-shelf thing... So not nuclear powered, by any means. How are you loading new data?

[Elijah]

Haha. Sounds like a good weekend, except the spilling of the beer. I ended up with mackerel in tomato on my carpet this morning. Not sure which is worst of that and beer.

Not Post 11, I think. I'm uploading the last sheet I'm using:

The actual sheet "Source" is much larger. Since I want to anonymize my data, I'm disclosing only the part I'm using for this specific question. The sheet "Source" is updated using VBA which extracts data from a SQL database. I didn't create it myself. So, I'm using a macro workbook also. It might be that my macro and your formula ain't good friends. Updating the Source sheet can be slow, but far, far from what's happening when I'm combining it with 'your' sheet.

I also created a sheet for Time 1 - Time 13 also using your formula. It's in the same workbook, but not included here since the file was too big (I actually had to delete some rows also).

What you'll see is that I've extended the formula down 50 rows from the 1st row. This is because I have another data set I'd like to calculate also which does in fact have close to 50 swings (Swing 1 - Swing 50).

If all else fails, I'll simply have to update the sheet Source manually each time I refresh my database.

PS: Any comments on why it's ROW 28 in formula in A26 as I asked about above? Don't quite understand it, but the result seems to be correct.

Best regards and thanks in advance.

[Glenn Kennedy]

Back in UK morning.

[Elijah]

Sure thing.

But, I might also save us the trouble and simply update this sheet without a dynamic link to my VBA updated sheet and instead copy and paste from my VBA sheet after I've updated it. This is after all what I've been doing mostly on my other sheets.

[Glenn Kennedy]

1. Row 28. On an earlier version, it must have been row 28. As it's simply a counter it doesn't matter. For tidiness' sake change it to $26:26. It is also more robust than using ROWS($1:1) in data that start elsewhere (e.g row 26). It works fine unless/until you delete row 1... A lot of folk (me included!!) forget that bit.

2. As your VBA runs, updating the source sheet, the formulae are probably going mental recalculating everything. Two possible options. Modify the VBA code to disable formul a calculation, or turn formula calculation to manual while you run the update. It's not an area where I have much experience... but give it a go.

[Elijah]

Glenn,

Great!

Turning on/off automatic formula calculation did the trick! If someone knowledgeable of VBA reading this could give me a line or two of code to do this in the VBA script, I'd be grateful. Actually, I just googled it and came across this:

https://www.automateexcel.com/vba/on-off-calculations/

I added Application.Calculation = xlManual as the first line of code in my script and Application.Calculation = xlAutomatic as the last line of my script.

It seems to work!

Thanks for all help Glenn. Putting this as solved now.

PS: You deserve more reputation, but I was not allowed to give you now.

Best regards.

Elijah

[Elijah]

Allright. So I find myself facing a final challenge on this particular sheet. I might not need these statistics, but since I'm already doing this, I might as well include it. I'm uploading the original sheet on post 1 updated.

I'm interested in the difference between End 1 and Time 1, End 2 and Time 2, ..., End 13 and Time 13. This is not calculated in advance and will need to be calculated by me. I label this Difference 1, 2, 3, ... etc. I would like to have this presented the same way Glenn set it up for me on this final sheet as columns.

1. Can this be obtained directly from sheet Source? Or would I need to first create a helper sheet to make the calculations? I started doing this in "LinkedSheet" on the sheet I'm uploading.

Creating a helper sheet shouldn't be a big problem.

The problem is that a lot of the rows are blank (not the same amount of data each day. I tried to get around this using IFERROR. It's rather long and messy, but seems to work.

Would you guys agree with this approach?

Summarized: 1. Make helper sheet; 2. Extract data using Glenn's approach.

I'd imagine this would be the way to go.

Thanks in advance!

[Elijah]

Would you guys agree on my suggested approach for this final statistics I want to compute?

Thanks in advance.

[Elijah]

Hi,

So, I went ahead with the procedure outlined above:

1. Create a new sheet which a) extracts Time & End from the original data source (Instrument sheet).

2. Calculate new statistics Duration (difference between Time n and End n in sheet DurationRawData.

3. Extract Duration in column format as taught by GlennKennedy in a new sheet.

I copied the exact same formula Glenn used, but I don't get the right values...

For example, first column should read 01:06; 00:49, 04:27.

Second should read 00:01; 00:23; 04:06; 01:00

It seems like the first row is correct, but not what's below.

Any ideas? Something wrong in my DurationRawData sheet or is the "Glenn" formula typed incorrectly by me?

Thanks so much in advance.

PS: I had to zip this file as it seems very large no matter what I do. I even had to delete quite a few rows...

Best regards,

Elijah

[Elijah]

I think I just figured it out. It was the last part of the formula which should say

COLUMNS($A:A);1+3 and NOT 1 + 4 which was the original formula. Reason being that there's 4 columns between each Time 1, Swing 1, etc., but 3 between Duration columns. So, problem solved.

[Elijah]

But, I seemed to have gotten a new problem.

I want to do a frequency count. It seems like Excel won't recognize the numbers in the table produced by Glenn.

I'm attaching a quick sample workbook.

To the far left is values produced by the 'formula'. See frequency count for 00:01. It should be 1. Excel does not seem to be able to count this.

To the right is values I just put in. Here, the frequency count works fine.

Any ideas? Is the formula somehow formatting the time values as text or some other format not recognizable by the COUNTIF?

If so - any way around this?

[Elijah]

Hi,

So, I went ahead with the procedure outlined above:

1. Create a new sheet which a) extracts Time & End from the original data source (Instrument sheet).

2. Calculate new statistics Duration (difference between Time n and End n in sheet DurationRawData.

3. Extract Duration in column format as taught by GlennKennedy in a new sheet.

This seemed to go well, but I was mistaken...

It's virtually impossible to load new data in the sheet. Excel instantly freezes. I've turned formulas to manual calculation, both in VBA and directly in the sheet. No luck.

I've also tried copy and pasting data from the original source, but that causes a crash also.

Any ideas, please?

Attaching the latest sheet which is correct (but impossible to update). Not sure what causes the sheet to crash. Slow? Sure. But crash? The other prior sheets work fine...

[Elijah]

I tried copy and pasting and it actually worked, BUT, it took like 3-4 minutes, I think. So it works, but not very well...