Sorting Data

[LincolnSixEcho]

I'm trying to sort some data with a pivot table & chart.

My first column are dates (many listed duplicate times)
My second column are store numbers(some of these listed duplicates as well).

I want to create a pivot chart & data that does the following:

I want to see the frequency of how many stores populated onto this list over the given time frame. However, If a store (ex. 561) populated twice on 6/1/11... I only want to count that once.

Help?

 

[Desu Nota from Columbus]

Have you tried to filter your data to show only unique records first?

 

[LincolnSixEcho]

No, I have not. My first reason for not doing that is because I cannot figure out how to do that. My second reason was because I thought it would ignore all records for that store. (Ex. Store 555 might have populated under multiple dates)

I want to gather data by how many stores populated on each particular day rather than by number of occurances.

 

[c_m_s_jr]

What version of Excel are you using?

 

[LincolnSixEcho]

I'm sorry, I should have stated that earlier. 2007

 

[c_m_s_jr]

Could you use the "Remove Duplicates" function on the "Data" tab and then run your pivot? That should remove the duplicates of the stores on a single date allowing you pivot to count correctly.

 

[LincolnSixEcho]

I actually figured it out. If anyone else was having the same problem... here what I did

I isolated the two columns into a new tab (I found that having additional columns with data was causing issues).

Next, I highlighted both columns and went into the advanced filter. I checked the box "unique records only."

From there, was able create a pivot chart. I used the Date in the Axis Field. I then used the store # (or location #) into the Values Box. (I also had to change the Value Field Settings to "Count" rather than "Sum."

Thanks guys for your assistance!