Import New Pivot table columns to worksheet automatically?

[Keyfinder]

Hi,

Complete beginner to Excel

I have made a workbook that uses Power Query to import data from .csv files to a table every time that I add a new file to the folder. Works perfectly.

I then create a Pivot table from that Data in order to focus on the particular data that I want. Works perfectly.

As the data increases week by week, the pivot table grows and grows. Perfect.

I then have to create a new "presentable" worksheet (a standings table for a racing league) using the data contained in the pivot table, which will then perform a couple of other functions on the data. Is there a way to have the data import to the new Worksheet automatically as the pivot table expands?

I currently have it so that the target worksheet has dynamic ranges(?) linked to the pivot table to import some things, but I have to define a new range every time the pivot table expands.

Is it possible for the worksheet to recognise that the data set in the Pivot table has expanded and import the new data accordingly? I can do this for the leftmost column by defining the range as being as large as I expect it to grow to during the racing season (the names of the entrants) and their respective weekly finishes and points. However every week the pivot table adds three new columns between the previous weeks results and the Grand totals. It is these columns that I would like to import.

Any help at all appreciated, even if it's just "No, stop looking, you can't".

Screen shots of the data to follow, hopefully it explains what I'm trying to do.






Pivot table (Week 1), I would like the columns that arrive in Week 2 to export to the following worksheet automatically.






Destination/Presentation sheet with columns I'd like to populate automatically.



Thank you very much in advance.



K

 

[johnny51981]

So, rather than doing a Pivot Table, are you able to do some calculations (VLOOKUP, SUMIFS) from the total Data that is produced from Power Query?

The data would grow down, but you would just need to add the new race section (Finish, Points, Fastest Lap) to the end/far right.

There would be no need for any named ranges, let along adding any.

 

[Keyfinder]

So, rather than doing a Pivot Table, are you able to do some calculations (VLOOKUP, SUMIFS) from the total Data that is produced from Power Query?

The data would grow down, but you would just need to add the new race section (Finish, Points, Fastest Lap) to the end/far right.

There would be no need for any named ranges, let along adding any.

Honestly, I have no idea what they are or how to use them. I'll look for tutorials on what VLOOKUP and SUMIFS are and see.

Thank you!

 

[johnny51981]

VLOOKUP for text results and SUMIFS for numerical value results.

Both will be wonderful and will save your life. But make sure to look into VLOOKUP with Multiple Lookup Criteria...it's a bit more complexicated.

Others might be better with an INDEX/SEARCH/FIND scenario, but I'm not super strong at that.

 

[Keyfinder]

Ok, thank you for suggesting this. Clearly I need to dig deeper but my initial thoughts are that a problem will arise when importing driver names as they will not always be the same each week and so I only want to import this first instance of each name occurring. This is how my Query data arrives, currently only showing week one as we have only run one week>

For the second week the query data table will extend by (probably) a very similar number of entries, but I don't want to get duplicate names added into my Presentation standings sheet whilst still sorting the data by track, and I want to add in any new names at their correct position in the standings sheet. Further complexicating this is that we have two car classes and I will need to sift/filter the data and produce separate standings tables by car and as well, two more standings tables based on whether the drivers have run both cars. It's all a bit complicated. Pivot tables gave me a very easy way to sort the data, and copy paste it.. however I am now faced with an awful lot of copy pasting and was hoping there would be a (relatively) simple way to set all 5 standings sheets to update automatically.

If you still think the answer is, as you say VLOOKUP and SUMIF, then I'll go and apply myself fully to the problem - but if what I've explained makes things look different then let me know and I'll save myself a headache!


Thank you very much for taking the time with this.

K

 

[Keyfinder]

Can I power query a power query? That could filter the data for me, right? To make the VLOOKUP a bit easier on the Car class side of things at least, maybe.

Thank you

K

 

[johnny51981]

I was just replying for just that.

In your Power Query that appends all of the weekly data together, right click on that and select Reference...this will show you the same exact results. Remove all other columns other than Driver Name. Then under Remove Rows, select Remove Duplicate Rows.

 

[Keyfinder]

Once again thank you for taking the time with this. But.. I think it's all getting a bit above my level of understanding and I need to learn a lot more before I don't need to be held by the hand through every single step.

I can do as you suggest, Reference, remove columns and duplicate rows. But then I don't really know what to do with what I now have or understand how it will eventually match up with the standings as they change each week based on where the individual drivers finish.

So yeah, I'd need to be walked through the whole process step by step and that is definitely not what I'm asking for.

I appreciate your taking it this far. I'll go away and stick to my pivot tables and copy-pasting whilst I try and learn/understand more about Power query and VLOOKUP and SUMIF.

Cheers

K

 

[Keyfinder]

It's not really copy-pasting, it's just setting a new "spill" or 5 or 6 every week. Maybe I was just, or am being, a bit lazy.


Have a good 'un

K

 

[Keyfinder]

I was just replying for just that.

In your Power Query that appends all of the weekly data together, right click on that and select Reference...this will show you the same exact results. Remove all other columns other than Driver Name. Then under Remove Rows, select Remove Duplicate Rows.

I'm not sure if it's good decorum to give you a notification about this.. but I just wanted you to know that you have changed my life sir. And I've only got as far as VLOOKUP with multiple conditions, haven't touched SUMIF yet.

Thank you so much.

K