How to count unique values in a dynamic range

[Gabe2k2]

So I confess I`m pretty useless in Excell and know of some of its potential and want to learn so My first task I want to ask about is lets say I have a Column A filled with numerical values entitled Codes and I want to Write a vba so each time I enter a new code that is the same as a pre existing Code/number it adds it to a total Column B
does this make sence.

some thing like


Codes total
1 ,,,,,,,2
2 ,,,,,,,2
3 ,,,,,,,1
4 ,,,,,,,1
5 ,,,,,,,1
1
2
7,,,,,,,1
8,,,,,,,1
9,,,,,,,1


even Better would be for it to delete the cells in column A or Highlight the duplicates but I dont want to see a total beside the duplicates


I think I Need to work with the =COUNTIF($A$2:$A$"total number of entries I dont know how to call this ",A3) then create a new column with new cells for totals =Unique(A2:"total entries call ") but so far I havent been able to achieve what I want also the number of entries in column A will change rapidly 1 every few seconds or less I just want to total up and list the unique entries in column A automatically

I`m using it to make a simple Spreadsheet for column A will be filled with barcode numbers and I just want to total how many items I scan that are the same in column B

Its for work and this would take some of the tedium out of manually counting each item

[jolivanes]

I am sure that at one time or another you have googled for an answer for a problem.
If you you for instance need to know the cost of a replacement windscreen for your car, would you google on "Need help" as you did here?

[Aussiebear]

Can you supply the "simple" workbook so we can see where you are headed here? Click on Go Advanced and follow the prompts from there.

In the mean time, and since you mentioned CountIf as a potential solution, CountIF requires a range to select from and a value to search by. Now since only you know at this stage how big the range can be, there are a couple of ways to go about this.

1. You could estimate a range, slightly bigger than anticipated. For example, you think it might be A2:A180, therefore you could use =Countif($A$2:$A$200,A2) in cell B2 and copy down, or;
2. You could make a range A2: A180 dynamic to overcome the growth or depletion of the data range.

Since you also mentioned the function =Unique(Data Range), can I assume you might be using Office 365?

Now the biggest query that I have is that you also mentioned that the range in Column A will be changing "every few seconds".... How can you react to this, if you have been manually counting the occurrences of individual values?

[georgiboy]

My sister lives in Morecambe - small world.

If you have Excel 365 and are looking for a seperate list to count the duplicates then i would recomend converting the range to a table and using something like the below formula:

=SORT(LET(r,Table1[Column1],u,UNIQUE(r),HSTACK(u,COUNTIF(r,u))),2,-1)

Or maybe filtered to only numbers with counts > 1:

=SORT(LET(r,Table1[Column1],u,UNIQUE(r),d,HSTACK(u,COUNTIF(r,u)),FILTER(d,INDEX(d,,2)>1)),2,-1)

If you want conditional formatting to highlight the duplicates then:
Select the column in the table you want to add the CF to
Under the 'Home' tab in the ribbon select 'Conditional Formatting'
Hover over 'Highlight Cell Rules'
Select 'Duplicate Values'

I recommend putting any data you have for this in a table and not just a range, the reason for this is that the both of the formulae above and the CF will grow with the data as you add it so there would be no need to worry about referencing the end of the range within the formula.

And yes as pointed out above, it is always good to set a meaningful title to a thread, this one could have been somthing like "Counting Duplicates in a range" or something.

[arnelgp]

you may test this.

[Gabe2k2]

you guys are awesome thanks for all the Help so far I will be back as I greatly admire the help Again Awesome work guys/girls Simply awesome !