I need to count the amount of weeks a piece of text has appeared since last change

[markpem]

Hello

Sorry for the confusing title, I'm not sure how to put it into words. I have done a table hopefully explaining it all. I will need to count a range as every week the date gets added between the current status and the last week's date (Current status will always be the last most furthest column on the spreadsheet)

I need to count the amount of weeks a piece of text has appeared since it last changed. I don't mind if it's done over a forumula or VBA. If it is VBA (which I'm quite happy with) I guess a range would need as the columns are always expanding. It could always be something like Column B to LastUsedColumn or something?

Thankyou


Client


18/04/2015





25/04/2015





02/05/2015





09/05/2015





Current Status











Mr Jones





Considering Options





Considering Options



Considering Options
Considering Options
Considering Options 4w






Mrs Smith


Considering Options





Considering Options





Set Up Purchase





Declined



Declined 1w






Mr Patel


Considering Options





Set Up Purchase





Set Up Purchase



Bought
Bought 1w






Mr Fernandez


Considering Options



Declined
Declined




Declined






Declined 3w

[PAB]

Hi markpem,

Give this a go, change the references accordingly.


=IF(SUM(F3=E3),CONCATENATE(F3," ",SUMPRODUCT(--(C3:F3=F3)),"w"),CONCATENATE(F3," ",SUMPRODUCT(--(C3:F3=F3)),"w"))

I hope this helps!

[SamT]

Sub CurrentStatus()
'for help see: http://www.vbaexpress.com/forum/showthread.php?56061
Dim StatusCol As Range
Dim Cel As Range
Dim Status As String
Dim PrevWks As Long

'Adjust Sheet nameas needed
Set StatusCol = Sheets("Sheet1").Range(1, Columns.Count).End(xlToLeft).Offset(, -1)
Set StatusCol = Range(StatusCol.Offset(1), StatusCol.End(xlDown)).Offset(, 1)

For Each Cel In StatusCol
Status = Cel.Offset(, -1).Value
PrevWks = 1

Do While Cel.Offset(, -1 - PrevWks).Value = Status
PrevWks = PrevWks + 1
Loop

Cel = Status & " - wks: " & PrevWks
Next Cel

End Sub

[p45cal]

Assuming your grid's top left cell above is located in cell A1 and you Current status column is currently column F, then in cell F2 enter the formula:

=E2 & " " & COLUMN()-LARGE(IF($A2:D2<>$B2:E2,COLUMN($B2:E2)),1) & "w"
but be careful to array-enter this with Ctrl+Shift+Enter, NOT just Enter.
Now copy down.

To adjust for an expanding number of weeks, you should first copy across the formulae in column F using the little black box situated at the bottom left of the selected formula cells in column F, then delete all formulae except for the column you want them in, and then you can enter the new data in the columns to the left of the Current Status column.

[SamT]

If you read the above before now, I changed a code error and made it a little more User friendly.

[markpem]

Hello Both

Thankyou for taking the time to reply. I am currently testing both solutions but SamT I have an issue with the vba code on this line


Set StatusCol = Range(StatusCol.Offset(1), StatusCol.End(xlDown)).Offset(, 1)

It says:

Run-time error '91':
Object variable or with block variable not set


Would you be able to advise? I did do a quick Google and tried the set option explicit but it just came up with Syntax error when I run it

[SamT]

That was my bad. Somehow I had a different picture of your worksheet than it really was.

Using this structure, the code below tests ok.


Name

b
c
d
results




a

a
a
a - wks: 3



a
b
b
b - wks: 2



a
b
c
c - wks: 1




Option Explicit

Sub CurrentStatus()
'for help see: http://www.vbaexpress.com/forum/showthread.php?56061
Dim StatusCol As Range
Dim Cel As Range
Dim Status As String
Dim PrevWks As Long

'Adjust Sheet nameas needed
Set StatusCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Offset(, -1)
Set StatusCol = Range(StatusCol.Offset(1), StatusCol.End(xlDown))

For Each Cel In StatusCol
Status = Cel.Value
PrevWks = 1

Do While Cel.Offset(, -1 * PrevWks).Value = Status
PrevWks = PrevWks + 1
Loop

Cel.Offset(, 1) = Status & " - wks: " & PrevWks
Next Cel

End Sub

Using Option Explicit is always a good idea, along with often using the Debug menu : Compile VBA Project.