• 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.

Ignoring Duplicates

O

OleMiss2010

Member
I have a report that lists account numbers that meet certain criteria in column A and the size of the accounts in column B. I am trying to think of a way to add column B, under certain conditions based on other columns, so that it will only add each account once. Some of the accounts should be on the report multiple times, but I only want to consider their size once. Ideally, I would like to avoid using a macro and avoid editing the report. Any ideas?
 
Which entry should be used? What are the criteria? If it's simply the first, you could generate a new list somewhere of the value you want using a VLOOKUP or something, and then sum up that list. I think we would need a better idea of what the data layout is, and/or what the criteria is to give a more complete solution.
 
Basically everything will be the same for every account except for one column that doesn't matter for what I am doing. That column is the report filter that essentially pulls accounts that meet any one of several criteria (some accounts meet multiple criteria). So I can use any entry. Do you think creating a duplicate list will be best?
 
How about a PivotTable? Your criteria field(s) could be a PageField, the names are in the RowFields, and put your data in the DataField. Then, set the Data field settings to show Max, rather than Sum.
 
By PageField do you mean Report Filter?

That seems to work. I do have to add a formula into a cell outside of the table to sum the maxes to get the total rather than the max from the entire list that the Pivot's Grand Total gives me.
 
Yes, I forgot they changed the name of the field between 2003 and 2007.


True. If the report has the potential to change sizes, you can remove the Grand Total, and just do a sum on the whole column.
 
Yeah that's what I went with. This isn't quite as functional as I would prefer. I am essentially creating a stock analytics page to add to a report and want to be able to slice this in some ways that are simple within a PivotTable, though they are still possible.
 
You must log in or register to reply here.
Back
Top