count unique records and summarize large amount of data

C

CYDSRQ

New Member
Joined
Nov 26, 2013
Messages
35
Hi and thank you for reading this post. I am using Excel 2013 and Windows 7 Professional, and have limited experience with formulas and none with VBA.

I have 51k records that list units of service and am trying to summarize the data, specifically :
unique CYDID
Sum of "Count" field per unique CYDID

Headings A1 to L1
Data A2 to L8

Thanks for any help!


[TABLE="width: 659"]
<colgroup><col width="64" style="width: 48pt;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;" span="2"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="64" style="width: 48pt;" span="2"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="154" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5632;"> <col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]CYDID[/TD]
[TD="width: 99, bgcolor: transparent"]Fname[/TD]
[TD="width: 64, bgcolor: transparent"]Lname[/TD]
[TD="width: 64, bgcolor: transparent"]GradYr[/TD]
[TD="width: 102, bgcolor: transparent"]DOB[/TD]
[TD="width: 64, bgcolor: transparent"]Gender[/TD]
[TD="width: 64, bgcolor: transparent"]Race[/TD]
[TD="width: 75, bgcolor: transparent"]schDate[/TD]
[TD="width: 154, bgcolor: transparent"]eveTitle[/TD]
[TD="width: 64, bgcolor: transparent"]eveID[/TD]
[TD="width: 64, bgcolor: transparent"]theCount[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12190[/TD]
[TD="bgcolor: transparent"]Mathew[/TD]
[TD="bgcolor: transparent"]Perry[/TD]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]11/3/1995[/TD]
[TD="bgcolor: transparent"]Male[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent, align: right"]2/3/2007[/TD]
[TD="bgcolor: transparent"]Teen Scene[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12190[/TD]
[TD="bgcolor: transparent"]Mathew[/TD]
[TD="bgcolor: transparent"]Perry[/TD]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]11/3/1995[/TD]
[TD="bgcolor: transparent"]Male[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent, align: right"]4/18/2008[/TD]
[TD="bgcolor: transparent"]McBean Scene[/TD]
[TD="bgcolor: transparent, align: right"]200087[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10537[/TD]
[TD="bgcolor: transparent"]Tim[/TD]
[TD="bgcolor: transparent"]Perry[/TD]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]1/29/1994[/TD]
[TD="bgcolor: transparent"]Male[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent, align: right"]4/8/2006[/TD]
[TD="bgcolor: transparent"]Teen Scene[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10537[/TD]
[TD="bgcolor: transparent"]Tim[/TD]
[TD="bgcolor: transparent"]Perry[/TD]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]1/29/1994[/TD]
[TD="bgcolor: transparent"]Male[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent, align: right"]4/18/2008[/TD]
[TD="bgcolor: transparent"]McBean Scene[/TD]
[TD="bgcolor: transparent, align: right"]200087[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14945[/TD]
[TD="bgcolor: transparent"]Mark[/TD]
[TD="bgcolor: transparent"]Rose[/TD]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]1/18/1994[/TD]
[TD="bgcolor: transparent"]Male[/TD]
[TD="bgcolor: transparent"]White[/TD]
[TD="bgcolor: transparent, align: right"]6/14/2008[/TD]
[TD="bgcolor: transparent"]Teen Scene[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]15970[/TD]
[TD="bgcolor: transparent"]Joseph[/TD]
[TD="bgcolor: transparent"]Thomas[/TD]
[TD="bgcolor: transparent, align: right"]2015[/TD]
[TD="bgcolor: transparent, align: right"]7/13/1997[/TD]
[TD="bgcolor: transparent"]Male[/TD]
[TD="bgcolor: transparent"]Other[/TD]
[TD="bgcolor: transparent, align: right"]11/22/2008[/TD]
[TD="bgcolor: transparent"]Teen Scene[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]15970[/TD]
[TD="bgcolor: transparent"]Joseph[/TD]
[TD="bgcolor: transparent"]Thomas[/TD]
[TD="bgcolor: transparent, align: right"]2015[/TD]
[TD="bgcolor: transparent, align: right"]7/13/1997[/TD]
[TD="bgcolor: transparent"]Male[/TD]
[TD="bgcolor: transparent"]Other[/TD]
[TD="bgcolor: transparent, align: right"]7/1/2011[/TD]
[TD="bgcolor: transparent"]Club Scene - North Port[/TD]
[TD="bgcolor: transparent, align: right"]200124[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Hi Caribeiro77 thanks for your response - doesn't the pivot table count all records? Wouldn't that result in duplication ?
 
Upvote 0
The Distinct Count in Pivot Tables worked great for the unduplicated count..... does anyone have an idea how to sum the field labeled "Count" by unique ID? I can't seem to make that work in Pivot Table....

Thank you!
 
Upvote 0
CYDSRQ said:
The Distinct Count in Pivot Tables worked great for the unduplicated count..... does anyone have an idea how to sum the field labeled "Count" by unique ID? I can't seem to make that work in Pivot Table....

Thank you!
Click to expand...

Unfortunately I do not have Excel 2013 for testing. I use Excel 2010 or 2007
I can try to create a formula probably an array formula.
What exactly do you need? What would be the expected result with the data sample from post #1?

M.
 
Upvote 0
Thanks Marcelo.

On Sheet2 - see result desired

[TABLE="width: 439"]
<colgroup><col width="64" style="width: 48pt;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;" span="2"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]CYDID[/TD]
[TD="width: 99, bgcolor: transparent"]Name[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]GradYr[/TD]
[TD="width: 102, bgcolor: transparent"]DOB[/TD]
[TD="width: 64, bgcolor: transparent"]Gender[/TD]
[TD="width: 64, bgcolor: transparent"]Race[/TD]
[TD="width: 64, bgcolor: transparent"]TheCount[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]12190[/TD]
[TD="width: 99, bgcolor: transparent"]Mathew[/TD]
[TD="width: 64, bgcolor: transparent"]Perry[/TD]
[TD="width: 64, bgcolor: transparent"]2013[/TD]
[TD="width: 102, bgcolor: transparent"]11/3/1995[/TD]
[TD="width: 64, bgcolor: transparent"]Male[/TD]
[TD="width: 64, bgcolor: transparent"]White[/TD]
[TD="width: 64, bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]10537[/TD]
[TD="width: 99, bgcolor: transparent"]Tim[/TD]
[TD="width: 64, bgcolor: transparent"]Perry[/TD]
[TD="width: 64, bgcolor: transparent"]2012[/TD]
[TD="width: 102, bgcolor: transparent"]1/29/1994[/TD]
[TD="width: 64, bgcolor: transparent"]Male[/TD]
[TD="width: 64, bgcolor: transparent"]White[/TD]
[TD="width: 64, bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]14945[/TD]
[TD="width: 99, bgcolor: transparent"]Mark[/TD]
[TD="width: 64, bgcolor: transparent"]Rose[/TD]
[TD="width: 64, bgcolor: transparent"]2012[/TD]
[TD="width: 102, bgcolor: transparent"]1/18/1994[/TD]
[TD="width: 64, bgcolor: transparent"]Male[/TD]
[TD="width: 64, bgcolor: transparent"]White[/TD]
[TD="width: 64, bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]15970[/TD]
[TD="width: 99, bgcolor: transparent"]Joseph[/TD]
[TD="width: 64, bgcolor: transparent"]Thomas[/TD]
[TD="width: 64, bgcolor: transparent"]2015[/TD]
[TD="width: 102, bgcolor: transparent"]7/13/1997[/TD]
[TD="width: 64, bgcolor: transparent"]Male[/TD]
[TD="width: 64, bgcolor: transparent"]Other[/TD]
[TD="width: 64, bgcolor: white"]2[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
hmm... I'm confused.

Looking at your post above it seems that you want a simple SUMIF in TheCount column.

So try (assuming data in Sheet1)
go to Sheet2

Data > Advanced Filter
in List Range pick Sheet1 A1:G8 (do not select columns H-K)
pick Copy to another location
Copy to: A1
check Unique records only

You get this (columns A-G)


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
CYDID​
[/TD]
[TD]
Fname​
[/TD]
[TD]
Lname​
[/TD]
[TD]
GradYr​
[/TD]
[TD]
DOB​
[/TD]
[TD]
Gender​
[/TD]
[TD]
Race​
[/TD]
[TD]
TheCount​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
12190​
[/TD]
[TD]
Mathew​
[/TD]
[TD]
Perry​
[/TD]
[TD]
2013​
[/TD]
[TD]
03/11/1995​
[/TD]
[TD]
Male​
[/TD]
[TD]
White​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
10537​
[/TD]
[TD]
Tim​
[/TD]
[TD]
Perry​
[/TD]
[TD]
2012​
[/TD]
[TD]
29/01/1994​
[/TD]
[TD]
Male​
[/TD]
[TD]
White​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
14945​
[/TD]
[TD]
Mark​
[/TD]
[TD]
Rose​
[/TD]
[TD]
2012​
[/TD]
[TD]
18/01/1994​
[/TD]
[TD]
Male​
[/TD]
[TD]
White​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
15970​
[/TD]
[TD]
Joseph​
[/TD]
[TD]
Thomas​
[/TD]
[TD]
2015​
[/TD]
[TD]
13/07/1997​
[/TD]
[TD]
Male​
[/TD]
[TD]
Other​
[/TD]
[TD]
2​
[/TD]
[/TR]
</TBODY>[/TABLE]


Then type TheCount in H1 and put this formula in H2
=SUMIF(Sheet1!A:A,A2,Sheet1!K:K)
copy down

Hope this helps

M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

darrenmccoy
  • Question Question
Help to Index unique records in Multiple columns
2
Replies
11
Views
1K
Peter_SSs
Peter_SSs
S
  • Question Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
983
AlanY
A
H
  • Question Question
Distinct Count based on given criteria
Replies
3
Views
812
SpillerBD
S
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
C
  • Question Question
copy entire row based on cell vale
Replies
1
Views
696
MARK858
MARK858

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top