Sumproduct Function To Pull Data From Raw Data Import

  • Hi,


    (file attached).


    I get sent every week a raw data file which i import into excel - the w.e. tabs - and im using the SUMPRODUCT function to pull data from these raw data tabs into a summary table, so i can make a pivot table from it, and therefore a pivot chart, showing time on the x axis, and number of orders on the y axis.


    At the moment im using the formula


    =SUMPRODUCT(('w.e. 29th june'!$M$10:$M$105=LEFT(C$8,LEN(C$8)-2))*('w.e. 29th june'!$N$10:$N$105=RIGHT(C$8,1)),(OFFSET('w.e. 29th june'!$D$10,0,MIN(ROW()-9),96,1)))


    (solution provided by a friend) and i simply copy and paste that formula to the cells in the summary table for the days for that week, from 23rd june to 29th june.
    The problem i have is when i copy the formula down to the 30th of june, and change the 'w.e. 29th june' bits in the formula to 'w.e. 6th july', the formula fails to work anymore and I don't understand why, even if i adjust the w.e. 6th july tab so that its a mirror image of the w.e. 29th june tab (with the different figures).


    I'm not sure if this is even the right approach either, maybe someone here knows a better way of doing the objective:


    To be able to make a trend graph showing number of orders over time, split by Branch, and by Order type. The reason why i've laid it out the way I have is because when i create a pivot trend graph from it i can easily drop in which trend lines i'd like to see, for example, I can compare 'st albans E order type' with 'Peterborough E order type'. It also allows me to group the dates by month/weeks/years so i can change the amount of time displayed. So i would like to keep the layout in the 'data input' tab as it is, but maybe someone can come up with a better way of pulling the data from the w.e. tabs, either by modifying the way this SUMPRODUCT function i've been using works, or using a whole other function and approach altogether.


    You will notice that in the w.e. 29th june tab i've had to tweak the data for it to work with the current formula (column B, M, and N and a VLOOKUP function) so i can group the various postcodes (AL , CM etc.etc.) by branch.


    The whole approach i've taken is rubbish, i think, and would really appreciate some input on it. Hope i've made clear what i'm looking for! (i've highlighted in yellow when the formula stops working)


    I'd eventually like to get to the point where i could press a button, and the raw data would be automatically imported into that summary table, and therefore the pivot table, so i could refresh the chart every week quickly.

  • Re: Sumproduct Function To Pull Data From Raw Data Import


    It would really help if the original input data didn't have so many totals and subtotals dotted through it. Before writing something to work around that - how much control do you have over the inputs?


    I don't like the table at the top and summary at the bottom (but these can be worked around fairly easily) but the per-branch subtotals throughout the data are a pain to work around (it can be done and not too slowly, but developing good design is much better than working around bad design IMHO)


    FYI, there are definitely general imporvements we can make to some of the formulae to make them less complex and implicitly easier to read.

  • Re: Sumproduct Function To Pull Data From Raw Data Import


    hi,


    thanks for your response.


    It won't let me attach the original raw data file I get so you can see what can/can't be imported (it's an mht file) - if you need it i can email it to you i guess. The table at the top can be disposed off, but the subtotals for each postcode and the summary at the bottom comes as part of the main data unfortunately.


    I don't mind having to manipulate the raw data manually so the functions for the summary table can pull data easier - deleting rows etc. Maybe a command button can be made to automatically delete those subtotal rows/summary table at the bottom, through recognising them as subtotal rows/summary table somehow?


    If that isn't possible then i'd have to manipulate the data for the functions performed for the pivot table, as long as the processes involved are relatively simple (so other people apart from me can import the data and refresh the pivot table).

  • Re: Sumproduct Function To Pull Data From Raw Data Import


    Try in C16 and copied down & across,


    =SUMPRODUCT(--(ISNUMBER(SEARCH(LEFT(C$8,2),'w.e. 6th july'!$M$10:$M$105))),--('w.e. 6th july'!$N$10:$N$105=RIGHT(C$8)),INDEX('w.e. 6th july'!$D$10:$J$105,,MOD(ROWS($C$16:$C16)-1,7)+1))


    HTH

  • Re: Sumproduct Function To Pull Data From Raw Data Import


    thanks kris, that works great as a temporary solution, at least the formula now needs no modification, i just have to change the worksheet name to suit.


    Would still be good to know how you and other proper excel experts would approach my objective described in the 1st post.


    thanks for this tho!

  • Re: Sumproduct Function To Pull Data From Raw Data Import


    Hi,


    Quote

    Would still be good to know how you and other proper excel experts would approach my objective described in the 1st post.


    That's because of this portion


    OFFSET('w.e. 29th june'!$D$10,0,MIN(ROW()-9),96,1)


    if you drag this down the rows it increases by 1. So it would work for the first 7 rows. Then you need to tweak MIN(ROW()-9) with MIN(ROW()-16) and so on.


    HTH

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!