Select specific row

[Krish]

I have a large file in Excel containing customer data. Each customer has
several rows. I am specifically interesred in selecting the last row for
each customer and analyse in a Pivot table. Could some one be kind enough to
let me know the formula to use for such selection.
Thanks
Krish

 

[duane]

I would write a macro to copy the last row of each customer data t
another sheet and do the pivot table there. Just go down the column o
customer names, and when the name changes copy the previous row t
another sheet and increment an index so you know what row to put it in

 

[anonymous]

-----Original Message-----
I would write a macro to copy the last row of each customer data to
another sheet and do the pivot table there. Just go down the column of
customer names, and when the name changes copy the previous row to
another sheet and increment an index so you know what row

to put it in.

And just exactly how does one do that?

If you want to help, then help. Don't provide "hollow"
information.

 

[Frank Kabel]

Hi
if this is a one time operation try the following:
1. Use a helper column to tag the last customer (lets assume your
unique customer name is in column A). e.g. use column E and enter the
followingh formula
=IF(COUNTIF(A1:$A$1000,A1)>1;"";"X")
and copy this down for all rows

2. Now use 'Data - Filter - Advanced Filter' to filter with this helper
column and to copy this filtered data to a new location

 

[Harlan Grove]

And just exactly how does one do that?

If you want to help, then help. Don't provide "hollow" information.

What a wonderful follow-up!

So how exactly would any of the rest of us know how little you know about
Excel from what you wrote in your first post? For those who do know the
rudiments of macros, duane's advice would have been sufficient.

The key point is that you can't use your original table as the source for
your pivot table if you only want the last row for each person. You need to
extract those records into a separate table which would be the source for
your pivot table. However, macros wouldn't be the easiest way to do this. An
Autofilter on an added column containing formulas would be easiest.

If your original data were in A1:X2000 and customer names or IDs were in
column C with row 1 containing column labels rather than data, then enter
the following formula.

Y2:
=C2=C3

Select Y2 and fill down into Y3:Y2000 (or double click the fill handle - see
online help if you don't know what the fill handle is). Col Y will now
evaluate TRUE in each row in which the customer name or ID changes in the
subsequent row. Select the augmented range, A1:Y2000, and run the menu
command Data > Filter > AutoFilter. There should be a drop-down arrow in
cell Y1. Click on it an select TRUE. This should filter your table so that
only the last row for each customer is visible. Select A1:X2000 (or the
bottommost row in the filtered table, which could be above row 2000, but
don't include col Y), Edit > Copy, move to a blank range somewhere else, and
Edit > Paste. Then clear the Autofilter by running Data > Filter >
AutoFilter again. Then clear Y2:Y2000. Use the new copied, filtered table as
the pivot table data source.

 

[duane]

If I might add, there was not enough info given in the first request fo
help for a more specific answer......

 

[Dave Peterson]

Once you have that helper column in place (either Frank's or Harlan's), you
could use that column in your pivottable.

Either use it in the Page field (and hide the value you don't want via the drop
down arrow) or use it in the column field and hide it there.