• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Plot upper 5 rows when using filter

R

rickards

New Member
I have a spreadsheet including customer, commodities and volumes. For this I am using data filters to chose which commodity to display. So far nothing strange. I am then plotting what is filtered in a chart. It is actually looking exactly like one of the tutorials on this website (http://chandoo.org/wp/2009/02/12/make-a-dynamic-chart-using-data-filters/).


My problem is that I have some 500 customers for every commodity and I only want to plot the upper 5 rows when I filter. How do I do this? When I specify the range, I want to say for an example "Start one row below A2 and plot next 5 regardless of row-number". This might mean plot row 335, 127, 111, 456, 206. Not A3:A8. Any ideas?
 
You can export that data into a second sheet, like a "Reporting sheet".


Using subtotal formula and a couple of helping columns, you can know what cells are visible and link only the top 5 rows to this "Reporting Sheet", that you print using a A3:A8 type print range.


I did this and I can explain it to you if you like. I'm at work now, in a couple of hours I'll come back here and explain this method to you, if you're still interested.
 
Ok I'll write it now:


In Sheet1 you have your database of filtered data, of which you only want to print the top 5 rows. Let's assume your database uses only 3 columns, A B and C. In column D you will put the value 1 in every cell. In column E you put the subtotal sum of each cell on it's left, like this:

(for cell E2, for example)

= subtotal (9 , D2)


This way you have a column that is always 1 in visible cells and is 0 in non visible cells.


In colum F, each cell will be the sum of the cell in its left and the cell above, like this:

(for cell F3, for example)

= E3 + F2


This way you have a column with 1 in the 1st record visible (not filtered), 2 in the 2nd, 3 in the 3rd and so on...

Using match and index in sheet2 you can export the top 5 rows to a "locked" range of cells and define a print area accordingly.


Hope it helps.
 
you can download an example here:


https://docs.google.com/leaf?id=0B5piylDHt3ybOGNlOGUzMDMtNDEyZS00NWJiLThjNzAtNGZkZTIwZTIyYWMw&hl=en_US&authkey=CNGl-v0D
 
I've checked it out. It works perfectly except one small thing. When I'm starting to sort after certain criterias it get's a reference problem since in the "AUX 3" column it's always fetching the value in the cell above. When the above cell then becomes text, it does not work anymore.


I tried to fix this with a simple if-function (=IF(ISTEXT(G1);D2;D2+G1)), but then the whole point is lost, it then includes the hidden rows. Does anyone know how to fix that?
 
I found this solution, which works (but not satisfactory):


=SUMPRODUCT(SUBTOTAL(9;OFFSET(M5;ROW($M$5:M5)-ROW(M5);0));--($M$5:M5=1))


then just copy for all rows.


Buuuut, now I got a new problem. The spreadsheet currently includes 10,000+ rows, and when I started to use this solution the calculation speed slowed down drastically. Now it takes some 5 minutes for every slight change. This basically makes the whole report useless :(


How can I make it faster?
 
hi there...


the only way i see it, in my example, the only way the aux3 would return an error (because of trying to add text) is because of the first row (and thats why, if you check my example, the first cell in aux3 column has a different formula (simple link to aux2 column, rather than a sum)...


the problem (i guess thats what happened) is that if you delete rows (namely the 1st row), you loose this first cell formula in column aux3 and all hell breaks loose.


to solve this, we should have a simple formula in column aux3, that would work in all cases (1st cell and following).


your formula should work but sumproduct'ing 10.000 cells inside each one of the 10.000 cells of aux3 would be heavy load (thats why your calculating speed is so slow, i believe).


i think this simple formula for aux3 should do the trick:


=IFERROR(F1+E2;E2)

(formula applied to 1st cell in column aux3(F:F), which is cell F2.


note aux3 is column F:F and aux2 is column E:E.


what this does is the same it did in my example (summing the cell in the left (aux2[E2]) with the cell on top (aux3[F1]). where it cant be done (this should be in the first cell because of summing text) it just assumes the value in aux2 (1 in the 1st visible cell)...


im not so good at explaining it i guess, but try it and tell me if it worked. this should be MUCH faster than the sumproduct formula you wrote, i hope.
 
You must log in or register to reply here.
Back
Top