Use an array to find total and unique batch IDs

[AggroJagg]

Hi all,

I have a spreadsheet with many thousands of rows and tens of columns. A small sample of the data is below:


batch_id Name_first Name_last
15908 Luis Abengozar
16828 Luis Abengozar
16968 Luis Abengozar
17156 Luis Abengozar
17156 Luis Abengozar
17178 Luis Abengozar
18087 Luis Abengozar


In general these 3 columns will have other columns between them.

What I want to do is for each last_name show a count of the total number of batch IDs and also a count of the unique IDs.

So the final output from the data above would be:

Name_first Name_last Total_Batch Total_Unique
Luis Abengozar 7 6


I'm familiar with using an array in this way :

myarray = Range("a1:a10").Value

However I think my searching skills are lacking or my vacabulary is not sufficient to properly bring up search examples of populating an X by 3 array.

Also would this approach be the right way to do this? Is there a better way?

Many Thanks for any input

 

[xlbo]

Well a pivot table would give you a good starter for 10 - may need an extra field for doing a count of unique IDs buit would get you a long way there....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bartrein]

If i understand correctly then what you are trying to do could be easily achieved without VBA :

- Total_Batch (including unique Batch Id and duplicate Id's) is effectively equal to the number of appearances of the LASt_Name :

=COUNTIF(C:C,C2) - where column C contains LAst_Name(s)

- Unique values :

=SUM(1/COUNTIF($A$2:$A$8,$A$2:$A$8)) - entered as an array formula (ctrl+shift+enter) , where A2:A8 is your column with Batch_Id's