How many times has been entered an unique item in a cell

[Villalobos]

Hello,

I would like to ask some help regarding data recording. In my test file I would like know that how many times has been entered an unique item in A1 (Sheet 1) and when was the last entry.

My first idea was that I create a Log code in on another sheet which is record all changes and after I can calculate easy the number of entering but I would like avoid to add new sheet to the file.

Could someone give me some advice?



Thanks in advance the response!

 

[Hui]

Have a look at the attached:

The data is stored in Sheet 2 which is hidden

 

[Villalobos]

Hello Hui,

Thank you for your time and response!

My first idea was similar that you posted (log the cell changing and after count the unique values), but I would like to ask that what would you do if you could not use any kind of sheet or range to record the changing of cell A1? Is that even possible (I think not, somewhere must to store the data)?

 

[SirJB7]

Hi, Villalobos!
If you can't use any worksheet range to record whatever it might be, where would you store that information? AFAIK Excel stores information in cells and cells are only found in worksheets.
Regards!

 

[NARAYANK991]

Hi ,

Can you please explain your actual requirement , instead of discussing a sample file ?

1. What kind of data entry will be done , by how many users , how many times ?

2. How do you want the reporting to be done , and how often ?

3. Are you sure you don't want even the report to be stored somewhere , as a record ?

Narayan

 

[Hui]

You could use named formula to store the data in an array

 

[Hui]

Here is the implementation using the data stored in a Named Formula
The Named Formula "d" contains 3 columns
with 1 record for each record of A to H
ID, Count and Date/Time
It is updated by VBA Code as required
It will only work for the Values A-H and doesn't add new values to the array, but it could

Enjoy

 

[Hui]

I have added the code to add variables which aren't in the existing list A-H
I have used code from Pearson Consulting http://www.cpearson.com/excel/vbaarrays.htm
to redimension the lower bound of a multi-dimension array

Obviously the reporting section doesn't allow for the new data but change say H to I and it works fine

the revised file is below:

 

[Villalobos]

@Hui

Hats off Sir!
To tell the true, I can't find words, simply you are fantastic, perfect job, I never thought that this was possible at all.
Now I believe in my Prof.

@SirJB7,
@Narayan,
Thank you for your attention!