create a filtered table

A

ASU

I have a data sheet with different items in column "A". and a running total
of the items in column "K".
Is there a way to filter the list so that it shows the last input of each
item with the running total. Of course totals would change as new data is put
in.
 
R

Roger Govier

Hi

Without using a filter.
Assuming row 1 is your header row and your data starts in row 2
Insert a new row 1.
In cell K1 enter = SUM(K3:K1000) change last value to suit maximum
size that your table is likely to be.
Place cursor in cell A3, Windows>Freeze Panes
Scroll down to your last row, and you will see the total for column K
visible, whilst looking at your latest row of entry.
 
A

ASU

Thank you for getting back so soon. But what I was after was the last figure
(in "K" column) for the individual items ("A" column). At the moment there is
two different items listed, but there might be more items in the list. I hope
it clearer now.
 
R

Roger Govier

Hi

I'm sorry but I'm not understanding the problem.

Does column A contain many names for example, with many duplicates of
the name and you are looking for the last row with Name_A in the column
and you want to know the value of K for that row? Is the total you want
to see, the total of column A, for all items of Name_A or the total of
all items in column K regardless?

Can you post a sample of what the data looks like, and another
description of what you want to see.
 
A

ASU

A K L
Item sold Remaining
curry 50 250
fish pie 140 160
curry 110 440
fish pie 200 560
fish pie 320 840
curry 134 630

Here is a cut version of what I have. What Im looking for is to be able to
copy the last figure (column K) entry of each Items(column A) and paste it to
a table in a different sheet. In this exemple it would look like this

curry 134
fish pie 320

The list would also cater for additional items added. Thank you for being so
patient.
 
R

Roger Govier

Hi

I wonder if we have the complete picture here?
Clearly, there must be some data in your table for items bought, as the
math doesn't support the amount remaining in each case.
I was wondering whether what you really wanted, was the amount remaining
for each items, as opposed to seeing the last sold value for the item.
If so then your summary sheet could keep a running total for you for
each product.
Assume the numbers bought are in column J

On Sheet2 set up some headers in A1:D1 of
Product Bought Sold Remaining
IN A2 onwards enter your list of product names
In B2 enter
=IF($A2="","",SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$J$2:$J$100)))
In C2 enter
=IF($A2="","",SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$K$2:$K$100)))
In D2 enter
=B2-C2
Copy B2:D2 down the page as far as your list of products extends

Change the length of the ranges (and the column Identifier for Bought)
to suit your data table, but do ensure that the ranges are of equal
length.

I may be jumping to the wrong conclusion here, and if I am I apologise.
 
A

ASU

That is absolutely brilliant. You guessed right, I was going to adjust the
one formula to fit the other items but you were two steps ahead. Thank you
very much
Could I ask you another question or do you rather me go through the normal
processes?
 
Top