Pulling 1 of each account # from data set

[jpnyc]

Hi,
I have a bunch of data, for example;
A1: J3000
Column C has the account number which repeats multiple times.
I'm using the =SUMPRODUCT function to pull information I need in a summary table.

The problem:
I have to manually put in the account numbers in the summary table, as I only want a unique account # to come up 1X.

I would like the summary table to get the Account Number in column C then use the "=SumProduct" function to pull the data (which I can do).

How can I do that.
Thanks for your help.

Jason

[MarvinP]

Hi Jason,

It sounds like you need a new column to the right of your data that has a list of unique data from Column C.
In Excel 2007 and 2010 there is a function called Delete Duplicates but you need to work a little harder for 2003, but it can be done using Advanced Filters. See http://www.youtube.com/watch?v=vvgMlI4Sf90

Hope this helps.

[jpnyc]

Hi, Thanks.
I'm using excel 2003
I thought this would work but the data is very unique - meaning the each column may have a new number.
So when I Auto Filter - Unique it is still giving me repeats of the account number, because the next column over is a unique number.
I tried to use "Criteria" range but it is not helping.
Any ideas?
Thanks again,
Jason

[MarvinP]

Hi Jason,

I meant for you to copy Column C only (using Values Only) onto a clean worksheet. Then do the advanced filter on that column to get unique entries. Then copy it back to the original sheet (in a new column) to start your formulas.

[jpnyc]

Yep - that was it.
Thanks for your help.
Jason