MrExcel Publishing
Your One Stop for Excel Tips & Solutions

frequency of unique entries & eliminating blank cells


Posted by Coline on September 20, 2001 3:38 PM

I'd like to know how to do 2 things with ease:
1. I would like to compute frequencies on long lists of data with many repeating entries. For example, if I have a list of ID #s, and I would like to know how many times each ID appears, how can I do these computations most easily? So that this list:
ID
1
1
1
2
3
3

can ideally become lists like these:
ID Frequency
1 3
2 1
3 2

Also, I would like to know how to eliminate blank cells (particularly in the case of having a large database where each column represents each individual's data and I would like to eliminate blank cells by column) if these appear between entries so that this database:
1 2
4
2
3
3
4 2
becomes:
1 2
2 4
3 3
4 2

Thanks!


Posted by Gyula on September 20, 2001 4:11 PM

Frequency 3 1 2 2

1. Sort and sub-total
2. Select the two columns, go to Edit>GoTo>Special>Blanks, go to Edit>Delete>ShiftCellsUp

Posted by coline on September 21, 2001 1:48 PM

Thanks for the help. However, is there an easier way other than sorting/sub-totaling to find frequencies of unique values for many columns of data at the same time? I'm dealing with about 100 columns of data, and I would like to find separate frequencies for each column of data.
Thanks again!
Coline Frequency 3 1 2 2

Posted by anno on September 23, 2001 6:42 PM

coline
pivot tables are good for frequencies but if you have 100+ columns and lots of unique values it may get a little large. worth a try though. go to the 'data' menu, select 'pivot table report' and follow the steps in the wizard. Frequency 3 1 2 2