Link to home
Start Free TrialLog in
Avatar of gregfthompson
gregfthompsonFlag for Australia

asked on

Count number of rows containing same number is Column A

The attached file contains 2 worksheets.

Original Address List worksheet contains an ID number for each row.
The requirement is for a macro to count the common ID numbers and create a new worksheet called Counted List, to show a list of unique ID numbers, with a count of how many occurrences for each unique ID that there were in Original Address List. (As per example worksheet Counted List)

A Pivot Table version has been completed. Pivot Table version is also attached for reference.
But this means that the new table has to be copied and pasted each time.

What would be easier to manage is a macro that produced the same result, so that one file could be set up with the macro, and then other files could be opened, and the same macro could  be run on these files.
Original-Address-worksheet---with-C.xlsx
Original-Address-List-worksheet---w.xlsx
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Here is first part.

I included in your file Original-Address-worksheet---with-C.xlsx an array formula that will get you the unique values in Col A and their respective count. Please check this and let me know so we can move on part 2 of the question.

Here are the formulas: In col C to get the unique values this formula should be save as CTRL SHIFT ENTER all pressed at the same time to get the {} around it.
=INDEX('Original Address List'!$A$1:$A$10000,SMALL(IF('Original Address List'!$A$1:$A$10000="",ROWS('Original Address List'!$A$1:$A$10000),IF(MATCH('Original Address List'!$A$1:$A$10000,'Original Address List'!$A$1:$A$10000,0)=ROW('Original Address List'!$A$1:$A$10000),ROW('Original Address List'!$A$1:$A$10000),ROWS('Original Address List'!$A$1:$A$10000))),ROW()))

Open in new window


And this formula in Col D to get the count per item. Normal formula.
=COUNTIF('Original Address List'!$A$2:$A$10000,"="&'Counted List'!C2)
gowflow
Original-Address-worksheet---with-C.xlsx
I hv to step out for a while will get to it when I am back. Check and let me know.
gowflow
Avatar of gregfthompson

ASKER

Thanks. These formula are all that are required.

But when I try to extend the number of rows to 1000000, an error occurs and I am unable to find what has gone wrong.
What is the maximum limit number of rows that you need so I can fix you the formulas
gowflow
SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here is the file updated with 100,000 at the main file.
gowflow
Original-Address-worksheet---with-C.xlsx
Thanks much appreciated.

I'll be able to use both depending on the table.