0

So I have column A (contains the word) and column C (contains the date) shown below. The columns are occasionally separated by new headers, such as for "Word" and "Date" and a blank space.

Word               Date
BV                 12/06/2017
BV                 12/06/2017
BV                 13/06/2017
BV                 13/06/2017
BR                 17/07/2017
BR                 17/07/2017
BR                 24/07/2017

Word               Date
BT                 30/07/2017
BT                 30/07/2017

Word               Date
BY                 05/08/2017

First the date would be converted in terms of week number into a new column D, such as 12/06/2017 to week 24.

Using something like:

Sub TimeConverter()    
  Dim I as Long, MaxRow as Long
  MaxRow = Range("A" & Rows.count).End(xlUp).Row
  For I = 2 to MaxRow
    Cells(I, "D").Value = DatePart("ww", Cells(I, "C"), 7)
  Next 
End Sub

Then I would like the VBA macro code to look through column A and find the number of times a word appears and match with a date on the same week number into a new column B.

Using something like:

=COUNTIF(A:A, "BV")
=COUNTIF(A:A, "BR")

Output
# 4
# 3

Now to then combine them together so that the unique word (column A) counts (column B) can be separated into the corresponding week number (column D).

Desired Output:

BV      4      24
BR      2      29
BR      1      30
BT      2      30
BY      1      31

Any suggestion would be great! Thank you.

F. Lee
  • 85
  • 1
  • 7
  • a dicitonary structure would do the job for you - take a look here, exercise a bit and you would be able to do it - https://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure – Vityata Aug 24 '17 at 09:42
  • @Vityata is it possible to see an example for my situation to get a better understanding of the dictionary structure? – F. Lee Aug 24 '17 at 09:58
  • In your situation, the key would be a combination of Word + Week and Value would be the repetition times. E.g. key `BV24`. And every time you see it, you would increment by 1. – Vityata Aug 24 '17 at 10:10
  • why you need vba? you can do this with pivot table – Skirmante Valentaite Aug 24 '17 at 10:26
  • @SkirmanteValentaite Hi VBA would be better because I would need to do this for multiple files – F. Lee Aug 24 '17 at 10:28

1 Answers1

1

Let's say that with your VBA code you have managed to get something like this as an input:

enter image description here

Then, as mentioned in the comments, you need to implement a dictionary to get something like this:

enter image description here

As you see, the keys of the dictionary is the word + the week number together. Thus BR29 is different than BR30.

Copy the sample input, run the code below and you will get the desired output:

Option Explicit

Public Sub TestMe()

    Dim myDict          As Object
    Dim lngCounter      As Long
    Dim strKey          As String
    Dim objKey          As Object

    Set myDict = CreateObject("Scripting.Dictionary")

    For lngCounter = 1 To 14
        strKey = Cells(lngCounter, 1) & Cells(lngCounter, 3)
        If myDict.exists(strKey) Then
            myDict(strKey) = myDict(strKey) + 1
        Else
            myDict(strKey) = 1
        End If
    Next lngCounter

    For lngCounter = 0 To myDict.Count - 1
        Cells(lngCounter + 1, 6) = myDict.Items()(lngCounter)
        Cells(lngCounter + 1, 7) = myDict.keys()(lngCounter)
    Next lngCounter

End Sub

Then you have to work more to find a way to split the keys from BV24 to BV and 24. You need to find a way to eliminate the zero from the results as well.

Vityata
  • 42,633
  • 8
  • 55
  • 100