• 100% Satisfaction Guarantee
Zabo04, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 283
Experience:  Experienced in Word, Excel, Access, Powerpoint, and Outlook.
58597962
Type Your Microsoft Office Question Here...
Zabo04 is online now

# I am working on/analyzing an Excel file with nearly 1000 data

### Customer Question

I am working on/analyzing an Excel file with nearly 1000 data entries. I'm using AutoFilter to see if some of the items happen multiple times. Is there a quick and simple way to find out:
1. How many total items there are in the entries (say there are 1000 entries, but 100 of the items each showed twice, 200 items each showed 3 times, there are actually a total of 500 items in this 1000 entries)?
2. How many items showed once (1x), 2x, 3x, ...10x in the entries?
3. Which items are showing multiple times?
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Zabo04 replied 1 year ago.
1. =sumproduct(1/countif(datarange,datarange). If there are blanks in the data range then will throw a divide by 0 error. You can use =sumproduct(1/countif(datarange,datarange&""), but that will add 1 to the value because it will count the blank once.3. Next to each data entry use =countif(datarange, leftcell). That will tell you how many times each item appeared.2. =countif(column you put the formula for 3. in, 1). Counts how many 1s appear (those that are unique).
Expert:  Zabo04 replied 1 year ago.
Let me know what questions you have, if you want to attach the excel workbook here I can work on it for you.
Expert:  Zabo04 replied 1 year ago.
Here is an example. Not I added an if statement to determine if blanks existed, if they did I subtracted 1 else 0.
Expert:  The-PC-Guy replied 1 year ago.
just use a pivot table, select your column you want to count, then add pivot table, use the column you selected for rows, and count values. If you would like to set up a remote session so I can show you how to use the pivot table let me know
Customer: replied 1 year ago.

Sorry, I don't understand what did you mean. Pivot table is to convert columns to rows, and vise versa. I have about 1000 rows and only 3 columns. Changing rows to columns will make the file VERY wide, and I don't know how that could help me count how many times each item appeared. Using the same way that suggested, after converted to pivot table?

Expert:  Zabo04 replied 1 year ago.
1. It says four in cell E3 because only four of the values in column A are unique. The fifth repeats. I am suggesting you not use a pivot table. For what you are doing is not the most effective means.
Expert:  Zabo04 replied 1 year ago.
2. So in my example there are two 2's. So count the number of 2's and divide the count by 2. =countif(B:B,2)/2, for the number with 3 =countif(B:B,3)/3.
Expert:  Zabo04 replied 1 year ago.
I am revising my example. To create a list without duplicates requires array formulas, so if you edit it you would have to press ctrl+shift+enter to get it to calculate. You can drag copy the cells with the formula and Excel will take care of it after you hit ctrl+shift+enter for the first array formula.
Expert:  Zabo04 replied 1 year ago.
Because array formulas are complicated for Excel, they take a lot of processing power. This workbook will run slower than you are used to. Cell E1 has the total number of items. This reduces the search area of the array formulas by searching only those cells with values to create the unique list because I reference that cell using indirect to build the range as a part of the formula in column J. You will see E3 and E10 have the unique item counts, here 10, and column J has 10 values, so the array formula works. Column K performs a vlookup to find how many times each unique value occurs from the list. D7 through D10 is a chart of the number of occurrences for each item, E7 through E10 has the number of items occurring that many times, so 6 items occur once, 2 items occur twice, 1 item occurs 3 times and occurs 4 times. (6*1)+(2*2)+(3*1)+(4*1)=17, all items accounted for, and 6+2+1+1=10 which matches the formulas in E3 and E4.
Customer: replied 1 year ago.

Wow, sounds very complicated to me. I'll need some time to study on this, and hopefully I can make myself understand it. I'll ask if I am unable to figure it out through your examples. Thanks for your patience.

Expert:  Zabo04 replied 1 year ago.
Take your time, ask questions. It is a complicated problem for Excel (because it requires searching and a database or programming language would be better, but not at all as visible or easy to manipulate).