How can I get the count of all values in an array in a list format?

Question

This is a summary of my total list of numbers. There are many more and to get them all in one single column will take ages. I want to get a list that will tell me the total number of times each number appears in the total array. for example, 8703 appears 32 times and so on. How can I do this ?

Answer 1

Re: "the total number of times each number appears"

(truncated)

The free 'Professional Compare' workbook includes several utilities.

The "List Unique Items" is one of them.

Download from OneDrive... https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

Nothing Left toLose

Answer 2

thank you - is this built into excel or a 3rd party plugin ?

Answer 3

Hi,

Solution workbook: 22_Oct_21_1.xlsm

Please check whether the following is helpful:

Sample data.

Step 1) Remove blank cells.

Q. How to remove blank cells?

A. Select columns that have numbers.

> go to Home tab > Go To Special > a dialog box will open > select Blanks > click OK

Result: Only blank cells will be highlighted.

> Right click on any cell that is highlighted > click on Delete > Select Shift cells up.

Only blank cells are deleted.

Step 2) Select the numbers > in the task bar, see the count of numbers.

Step 3) Create a helper column.

> In the 1st cell, write 1.

> Select the cell on which You wrote 1.

> Go to Home tab > in Editing group, click on Series.

> Series dialog box will open.

> Please refer to the following screenshot and select the options in the dialog box.

- In the Stop value field, write the total count of number that You determined in Step 2.

Result: in the helper column, serial numbers from 1 to (count of numbers) will populate.

Step 4) In the adjoining column, in the 1st cell, please write the following formula:

=INDIRECT(ADDRESS(ROUNDUP(0.2*ROW(A1),0),ROW(A1)-(ROUNDUP(0.2*ROW(A1),0)*5-5)))

- A1 is the 1st cell that has the number.

To copy the formula for all numbers:

> in the right bottom corner of the cell in which You wrote the formula > double-click.

Result: all the numbers will be populated in 1 column.

Step 5) In the adjoining column, in the 1st cell, please write the following formula:

=COUNTIF($H$2:$H$331,H2)

To copy the formula for all rows:

> in the right bottom corner of the cell in which You wrote the formula > double-click.

Note: In the above formulas, please change ranges/cell references to suit Your requirement.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 4

Re: "is this built into excel or a 3rd party plugin ?"

"Professional Compare" is an Excel workbook that I created.
I am the 3rd party in this case.

Download from OneDrive... https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

'---
Nothing Left to Lose

Answer 5

Excel 365 with Power Query.

Count unique numbers.

No formulas, no extra columns, no VBA macro,

https://www.mediafire.com/file/1sbkocym4n4j6qu/10_22_21.xlsx/file

https://www.mediafire.com/file/tvbo8xqvdc7vjxs/10_22_21.pdf/file

All versions of Excel have Power Query.