VBA to count data and echo into another cell

[Jameo]

Hi all,

Really just looking for an opinion on how to proceed witht he following.
I have raw data in the form of:

DataItemName|Date

These data item names give information about the location and details of a breach in a quality control measure. i.e. examples of the data would be:

BarrowNM.CV1|somedate
BarrowNM.H2S1|somedate
BarrowNM.WB1|somedate
DragonTer.WB1|somedate
DragonTer.H2S1|somdate

and so on and so forth. Basically, every time a dataitemname such as this appears in the data, it relates to a unique breach. What I would like to do is count the amount of times each of these breaches occurs and then populate some values in another sheet.

Now in my head I am thinking of something akin to the following

Sub test()
    Dim lRow, nRow As Long
Dim BCV, BHS, BWB, DWB, DHS As Integer
 
    BCV = 0
    BHS = 0
    BWB = 0
    DWB = 0
    DHS = 0
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    nRow = Sheets("Breaches").Range("A" & Rows.Count).End(xlUp).Row + 1
 
    For i = 1 To lRow
        If Cells(i, 1).Value = "BarrowNM.CV1" Then
            BCV = BCV + 1
        End If
        If Cells(i, 1).Value = "BarrowNM.H2S1" Then
            BHS = BHS + 1
        End If
 
        If Cells(i, 1).Value = "BarrowNM.WB1" Then
            BWB = BWB + 1
        End If
 
        If Cells(i, 1).Value = "DragonTer.WB1" Then
            DWB = DWB + 1
        End If
 
        If Cells(i, 1).Value = "DragonTer.H2S1" Then
            DHS = DHS + 1
        End If
    Next i
 
    Sheets("Breaches").Cells(nRow, 2).Value = BCV
    Sheets("Breaches").Cells(nRow, 3).Value = BHS
    Sheets("Breaches").Cells(nRow, 4).Value = BWB
    Sheets("Breaches").Cells(nRow, 5).Value = DWB
    Sheets("Breaches").Cells(nRow, 6).Value = DHS
 
End Sub

So I have tested this, and it works as anticipated. Counts the number of times a value appears, and echoes it into the appropriate cells in the appropriate sheet after the last row.

So I guess I should get to my question. As well as putting in the cell values, I would like to insert the name of the sheet the macro is ran on in the first column of the breaches sheet.

Something like

Sheets("Breaches").Cells(nRow, 1).Value = SheetName

But I am not sure of the appropriate code. Another point on this, all the sheet names are in the form of Dec10, Jan11, Feb11 etc. If the above macro inserts the sheet name in the first column, will excel automatically recognise it as being in a date format? This is important, as after I have compiled the data, I will then be writing a macro to plot the data in a series of charts with a timescale of a rolling 13 month period. (Not sure how I will define a rolling 13 month period in a macro, but that is a problem for another day)

Also, I have in effect 100 different dataitemnames, so as you can imagine, writing this macro in totalility, in the form I currently have it, will be both time consuming and will conatin a hundred if statements. Is there any more efficient ways you can think of doing this?


Thanks all for your time

 

[Jameo]

Cheeky bump

 

[MickG]

This is one way , but I think it might have problems.
The code Find all the Unique (strings) Names in column "A" and then counts the number of duplicates for each Unique string.
The unique names are then posted to row (1) stating "B1" of sheet "Breaches".
The Count for each Unique Item is the placed in the last used Row +1 starting Column "B" with the sheet name in Column "A.
The possible problem is if you add new names to the list the old count of Unique items will be out of place Column Wise.
To resolve:- If in column "A" of your data sheets you had the complete list of all possible names, then the code could be sorted so that the Names List would then always be Compete, and in the correct order.
This would obvouly give an extra number to the count of each item, which could be removed in the code.
If that was not an option, you could place the Unique set of names in row (1) of "breaches", Then the code could Match the Name to the count.
Your thouights !!!

[COLOR=navy]Sub[/COLOR] MG12May25
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] lst [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 2)
ReDim Stray(1 To Rng.Count)
    [COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            [COLOR=navy]If[/COLOR] Not .Exists(Dn.value) [COLOR=navy]Then[/COLOR]
                n = n + 1
                Stray(n) = n
                .Add Dn.value, 1
            [COLOR=navy]Else[/COLOR]
                .Item(Dn.value) = .Item(Dn.value) + 1
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR]
ray = Application.Transpose(Array(.keys, .Items))
n = .Count
[COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] Sheets("Breaches")
    .Range("B1").Resize(, n) = Application.Index(ray, (Array(Stray)), 1)
        lst = .Range("A" & rows.Count).End(xlUp).Offset(1).row
            .Range("B" & lst).Resize(, n) = Application.Index(ray, (Array(Stray)), 2)
                .Range("A" & lst) = ActiveSheet.Name
[COLOR=navy]End[/COLOR] With
MsgBox "Run!!"
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Jameo]

Sorry Mick,

I've only just seen you reply, was is meetings all day yesterday afternoon. Thanks for getting back to me.

I must admit that I like your way a lot more than mine, and I think with a little bit of adjustment it should work.

As you correctly anticipated, problems arise with the name list. For every month, not every dataitemname appears, as there is not a breach in every way every month.

Rather than list all the unique names in the breaches sheet, would it be possible to list them all in a different reference sheet, and use that to match the data item names?

I've only just got into work this morning and so have not had the chance to try to understand your coding. I'll go through it now and try to get to grips with exactly what you are proposing.

Thanks so much for your input.

 

[Jameo]

Hi again Mick.

Thats a very clever macro you have there I have to say

Its not quite what I need it to do, but it is very close. I think I definitely need a reference sheet with which to check the values against, to ensure that all data items are counted and placed into breaches in the same column order (i.e. if there are no values of that particlular type in the range, a zero will be populated).

In breaches I will have the double column headings you see, with the site name first, with all the different quality values underneith it. This way, after this macro has imported the data into the breaches sheet, I will have a sequential list of the breaches seperated first by site, and then by type, with the month headings in the first column. This will allow me to plot the total number of breaches by type for every site.

I'm going to attempt to modify your code this morning, but this isn't exactly one of my strengths. I'm an engineer by trade so I am more comfortable hitting things with a hammer to make them work

Wish me luck - and thanks again!

 

[MickG]

If you could show an example of you "Breaches" Sheet showing layout and some data, I'll give it some more thought.
You could hard code the Unique names (within code) to give you the layout for the headers.
You should also be able to post your sheet to the thread in a decent format by first placing a border around each cell in the range to form a grid of data and then copying and Pasting to the thread.
Mick

 

[Jameo]

I have slightly changed my approach Mick. Your previous posts have given me an idea about a slightly easier way to proceed. Please bear with me for a bit to try out some of these ideas. If i come back to you I want it to be with a fully formed idea, rather than me clutching at straws.

I really appreciate your help so far. Thanks so much