Use a Drop down selection and count how many in column, then offset and add 1

[v2jtb]

I am wondering if I can use VBA to have a Drop down selection in Column A to count how many of the selected items are in the column, when selected. Then in column B (same row) offset and add 1 to the count.

So the user will say select apple from Apple, Orange or Pear, counts how many apples in column A +1 then adds that figure to the cell beside the selection in column B. So th count is 11 apples and the selection will make it 12 so it adds 12 to cell next to apple in column B

[DGagnon]

you can do this with data validation and countif, but i dont fully understand your layout, could you attach a sample with the desired output?

[v2jtb]

I hope this helps, also it will be part of another VBA script. I will need to concatinate to make a unique record.

[DGagnon]

you can use this formula to do what you are trying, starting in B11 and down

=IF(A11<>"",COUNTIF(A$1:A10,A11)+1,"")

[v2jtb]

Hi thank you for your help.
The added bit is that it needs to be concatenated with column A. So the cell in column B will be unique, like Apple1 this will be used as an ID. So the cell keeps the ID, which means that the cell somehow if moved or deleted keeps the ID?

[DGagnon]

after you have the IDs you can paste values over those items. I'm not totaly sure of the application, but that should work.