Get documents about "How to Remove Duplicates Records in Excel
W
Description
How to Remove Duplicates Records in Excel document sample
Document Sample
The goal is to Count the Unique Occurrences that are greater than
$30.00.
Pivot Table gives a unique list of items, and then you can build your
COUNTIF formula
Count Greater than
$30.00 30 Count Greater than $30.00
Do Not Count
Duplicates 24
Dollars Spend Per
Attendee
78.71
78.71 Dollars Spend Per Attendee
126.6 7.63
80.3 12.95
123.77 18.54
93.12 29.29
73.83 35.15
66.56 38.5
154.04 66.28
83 66.56
38.5 72.02
124.12 72.61
72.61 73.83
133.57 78.71
133.57 78.84
125.3 80.3
108 83
120.39 83.38
121.97 93.12
112.5 100.36
100.36 108
29.29 112.5
66.28 120.39
66.28 121.97
72.02 123.77
83.38 124.12
83.38 125.3
83.38 126.6
83.38 133.57
83.38 154.04
66.28 Grand Total
78.84
18.54
35.15
7.63
12.95
The goal is to Count the Unique Occurrences that are
greater than $30.00.
Advanced Filter, Unique Records will get a unique list, and
then you can create your COUNTIF formula
Count Greater than Count Greater than
$30.00 30 $30.00
Do Not Count
Duplicates 24
Dollars Spend Per Dollars Spend Per
Attendee Attendee
78.71 78.71
78.71 126.60
126.60 80.30
80.30 123.77
123.77 93.12
93.12 73.83
73.83 66.56
66.56 154.04
154.04 83.00
83.00 38.50
38.50 124.12
124.12 72.61
72.61 133.57
133.57 125.30
133.57 108.00
125.30 120.39
108.00 121.97
120.39 112.50
121.97 100.36
112.50 29.29
100.36 66.28
29.29 72.02
66.28 83.38
66.28 78.84
72.02 18.54
83.38 35.15
83.38 7.63
83.38 12.95
83.38
83.38
66.28
78.84
18.54
35.15
7.63
12.95
The goal is to Count the Unique Occurrences that are greater than
$30.00.
In Excel 2007 you can use the "Remove Duplicates" feature in the Data
Ribbon, and then you can build your COUNTIF formula
Count Greater than Count Greater
$30.00 30 than $30.00
Do Not Count
Duplicates 24
Dollars Spend Per
Attendee
78.71
126.6
80.3
123.77
93.12
73.83
66.56
154.04
83
38.5
124.12
72.61
133.57
125.3
108
120.39
121.97
112.5
100.36
29.29
66.28
72.02
83.38
78.84
18.54
35.15
7.63
12.95
The goal is to Count the Unique Occurrences that are
greater than $30.00.
Array Formulas will allow you to Count values greater than
30 and not count the duplicates. For more about array
formulas, see this video series: 05 Excel Series: Array Formulas 1-14
Count Greater than
$30.00 $30.00
Do Not Count
Duplicates
=COUNTIF(A$8:A8,A8)=1
Dollars Spend Per Count Greater
Attendee than $30.00
78.71 TRUE 24 =SUMPRODUCT((A8:A43<>"")*(A8:A43>B4)/COUNTIF(A8:A43,A
78.71 FALSE See this link for more about this formula:
126.6 TRUE http://www.mrexcel.com/forum/showthread.ph
80.3 TRUE
123.77 TRUE
93.12 TRUE
73.83 TRUE
66.56 TRUE
154.04 TRUE
83 TRUE
38.5 TRUE
124.12 TRUE
72.61 TRUE
133.57 TRUE
133.57 FALSE
125.3 TRUE
108 TRUE
120.39 TRUE
121.97 TRUE
112.5 TRUE
100.36 TRUE
29.29 TRUE
66.28 TRUE
66.28 FALSE
72.02 TRUE
83.38 TRUE
83.38 FALSE
83.38 FALSE
83.38 FALSE
83.38 FALSE
66.28 FALSE
78.84 TRUE
18.54 TRUE
35.15 TRUE
7.63 TRUE
12.95 TRUE
Count Greater than
$30.00 24 =SUMPRODUCT(B8:B43*(A8:A43>B4))
24 {=SUM(B8:B43*(A8:A43>B4))} Remember to enter this with Ctrl + Shift + Enter
mulas 1-14
8:A43>B4)/COUNTIF(A8:A43,A8:A43))
/forum/showthread.php?t=361900
with Ctrl + Shift + Enter
This video is in response to the video named "Excel Finance Trick 11: How Long
Pay Off Credit Card Balance".
When solving for the number of periods to pay off a loan, if you change the
input variables, the Period PMT (Min Monthly PMT) cannot be less than the
Interest Paid each period or else the principal will grow forever - the number
periods is infinite.
How long to pay off your credit Card if you pay only the
minimum PMT required?
Balance = PV = 3,000.00
APR = i = 18.00% Type
n= 12 0
Minimum Monthly PMT = 78.00
n*x = NPER function = 57.77578438
x = n*x/n = years 4.814648699
It will take 4.81 years to pay off the credit card if we
Words: make only the minimum payment each period.
Reduction
in
Period PMT Interest Principal Balance
0 3,000.00
1 78.00 45.00 33.00 2,967.00
2 78.00 44.51 33.50 2,933.51
3 78.00 44.00 34.00 2,899.51
4 78.00 43.49 34.51 2,865.00
5 78.00 42.98 35.02 2,829.98
6 78.00 42.45 35.55 2,794.42
7 78.00 41.92 36.08 2,758.34
8 78.00 41.38 36.62 2,721.72
9 78.00 40.83 37.17 2,684.54
10 78.00 40.27 37.73 2,646.81
11 78.00 39.70 38.30 2,608.51
12 78.00 39.13 38.87 2,569.64
13 78.00 38.54 39.46 2,530.18
14 78.00 37.95 40.05 2,490.14
15 78.00 37.35 40.65 2,449.49
16 78.00 36.74 41.26 2,408.23
17 78.00 36.12 41.88 2,366.36
18 78.00 35.50 42.50 2,323.85
19 78.00 34.86 43.14 2,280.71
20 78.00 34.21 43.79 2,236.92
21 78.00 33.55 44.45 2,192.47
22 78.00 32.89 45.11 2,147.36
23 78.00 32.21 45.79 2,101.57
24 78.00 31.52 46.48 2,055.09
25 78.00 30.83 47.17 2,007.92
26 78.00 30.12 47.88 1,960.04
27 78.00 29.40 48.60 1,911.44
28 78.00 28.67 49.33 1,862.11
29 78.00 27.93 50.07 1,812.04
30 78.00 27.18 50.82 1,761.22
31 78.00 26.42 51.58 1,709.64
32 78.00 25.64 52.36 1,657.29
33 78.00 24.86 53.14 1,604.15
34 78.00 24.06 53.94 1,550.21
35 78.00 23.25 54.75 1,495.46
36 78.00 22.43 55.57 1,439.89
37 78.00 21.60 56.40 1,383.49
38 78.00 20.75 57.25 1,326.24
39 78.00 19.89 58.11 1,268.14
40 78.00 19.02 58.98 1,209.16
41 78.00 18.14 59.86 1,149.30
42 78.00 17.24 60.76 1,088.54
43 78.00 16.33 61.67 1,026.86
44 78.00 15.40 62.60 964.27
45 78.00 14.46 63.54 900.73
46 78.00 13.51 64.49 836.24
47 78.00 12.54 65.46 770.79
48 78.00 11.56 66.44 704.35
49 78.00 10.57 67.43 636.91
50 78.00 9.55 68.45 568.47
51 78.00 8.53 69.47 498.99
52 78.00 7.48 70.52 428.48
53 78.00 6.43 71.57 356.91
54 78.00 5.35 72.65 284.26
55 78.00 4.26 73.74 210.52
56 78.00 3.16 74.84 135.68
57 78.00 2.04 75.96 59.72
58 60.61 0.90 59.72 -
59 - - - -
60 - - - -
61 - - - -
62 - - - -
63 - - - -
64 - - - -
65 - - - -
66 - - - -
67 - - - -
68 - - - -
69 - - - -
70 - - - -
71 - - - -
72 - - - -
73 - - - -
74 - - - -
75 - - - -
76 - - - -
77 - - - -
78 - - - -
79 - - - -
80 - - - -
81 - - - -
82 - - - -
83 - - - -
84 - - - -
85 - - - -
86 - - - -
87 - - - -
88 - - - -
89 - - - -
90 - - - -
91 - - - -
92 - - - -
93 - - - -
94 - - - -
95 - - - -
96 - - - -
97 - - - -
98 - - - -
99 - - - -
100 - - - -
101 - - - -
102 - - - -
103 - - - -
104 - - - -
105 - - - -
106 - - - -
107 - - - -
108 - - - -
109 - - - -
110 - - - -
111 - - - -
112 - - - -
113 - - - -
114 - - - -
115 - - - -
116 - - - -
117 - - - -
118 - - - -
119 - - - -
120 - - - -
121 - - - -
122 - - - -
123 - - - -
124 - - - -
125 - - - -
126 - - - -
127 - - - -
128 - - - -
129 - - - -
130 - - - -
131 - - - -
132 - - - -
133 - - - -
134 - - - -
135 - - - -
136 - - - -
137 - - - -
138 - - - -
Related docs
Other docs by jhb16296
