# How to Remove Duplicates Records in Excel

W
Description

How to Remove Duplicates Records in Excel document sample

Shared by:
Categories
-
Stats
views:
1
posted:
6/27/2011
language:
English
pages:
14
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
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))

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
How to Marketing Tradeshow to Retailers