gregfthompson
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
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
I hv to step out for a while will get to it when I am back. Check and let me know.
gowflow
gowflow
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.
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
gowflow
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is the file updated with 100,000 at the main file.
gowflow
Original-Address-worksheet---with-C.xlsx
gowflow
Original-Address-worksheet---with-C.xlsx
ASKER
Thanks much appreciated.
I'll be able to use both depending on the table.
I'll be able to use both depending on the table.
I included in your file Original-Address-workshee
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.
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,"="&'C
gowflow
Original-Address-worksheet---with-C.xlsx