• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to insert the text by mapping the working days with the name

S

Srinivasan

New Member
Hi,


How to insert the formuala insert the text by mapping the working days mapping with the names. Please find the below table for your reference.


Eg: ABC and Com (Names - Col B) has been updated on 31st of Oct and we have 5 working days where we have a buffer - till then i need as text 1. Post 5th working day the breach (Col C)should change as "Text 2" and 6th working day as "Text 3" and goes on.


Date Names Breach

10/31/2011 ABC and Com Text 1

11/1/2011 Vishal Text 1

11/2/2011 Boss Srini Text 1

11/3/2011 ABC and Com Text 1

11/4/2011 ABC and Com Text 1

11/7/2011 ABC and Com Text 2

11/8/2011 ABC and Com Text 3

11/9/2011 ABC and Com Text 3

11/10/2011 Boss Srini Text 1

11/11/2011 ABC and Com Text 1

11/14/2011 Boss Srini Text 2

11/15/2011 Boss Srini Text 3

11/16/2011 Boss Srini Text 4

11/17/2011 Boss Srini Text 5

11/18/2011 Boss Srini Text 6

11/21/2011 Boss Srini Text 7

11/22/2011 Lokesh Text 1

11/23/2011 Lokesh Text 1


Request your help in resolving this request at the earliest as I am in bwt of the project and struck with the above tables.


Regards,

Srinivasan Sanjeevi
 
Hi Srinivasan,


Assuming you have your date, names and breach in row 1 from col A. I would put =now() or =today() functions in cell D1, and 5 (number of days for breach) in cell E1.


Then insert the following magic formula in all the rows in Col C, row 2 onwards..


="Text " & ROUNDUP(($D$1-A2)/$E$1,0)


Thats it!!


Regards,

Prasad
 
I am sorry, I didn't read your requirements fully. I know the above answer is incomplete. :( I will come back..
 
try the formula in col C2 and downwards :


="Text " & IF(NETWORKDAYS(A2,D1)<=5,"1",NETWORKDAYS(A2,D1)-$E$1)
 
Date Names Breach

10/31/2011 ABC and Com Text 1

11/1/2011 Vishal Text 1

11/2/2011 Boss Srini Text 1

11/3/2011 ABC and Com Text 1

11/4/2011 ABC and Com Text 1

11/7/2011 ABC and Com Text 2

11/8/2011 ABC and Com Text 3

11/9/2011 ABC and Com Text 4

11/10/2011 Boss Srini Text 1

11/11/2011 ABC and Com Text 5

11/14/2011 Boss Srini Text 2

11/15/2011 Boss Srini Text 3

11/16/2011 Boss Srini Text 4

11/17/2011 Boss Srini Text 5

11/18/2011 Boss Srini Text 6

11/21/2011 Boss Srini Text 7

11/22/2011 Lokesh Text 1

11/23/2011 Lokesh Text 1


Sorry to come back, actually i need the networking day to be calculated as per Col A.


If you can see, ABC and Com has been updated 1st on 10/31/2011 and then later 11/3/2011 and 11/4/2011 respectively; where 5 working days is completed.


Next updated is on 11/7/2011 where 5 working days is over, and breach should be updated as Text 2 and next update is on 11/08/2011 and the breach should be updated as Text 3 as so on.


Hope now my requirement is clear and sorry for the confusion.


Regards,

Srini
 
Hi Srini ,


Can you please clarify a bit more ?


As I understand this , the following are all different entries :


ABC and Com , Vishal , Boss Srini , Lokesh


The first entry for any of these can be on some day ; thus for "ABC and Com" , it is on 10/31/2011 ; for "Vishal" it is on 11/1/2011 , for "Boss Srini" it is on 11/2/2011 , and for "Lokesh" it is on 11/22/2011. I assume these entries will be manually put in into these cells.


The next entries should all come automatically , based on your 5-day buffer , and how many weekdays have passed.


What is the entry for "Boss Srini" again on 11/10/2011 ?


Why are there no further entries for "Vishal" ?


Can you please explain some more ?


Narayan
 
Appreciate your follow up in resolving the query.


a) The first entry for any of these can be on some day ; thus for "ABC and Com" , it is on 10/31/2011 ; for "Vishal" it is on 11/1/2011 , for "Boss Srini" it is on 11/2/2011 , and for "Lokesh" it is on 11/22/2011. I assume these entries will be manually put in into these cells.

Comment - Yes, you are correct these entries (Dates & Names) are updated manually as and when the requests are received


b) The next entries should all come automatically , based on your 5-day buffer , and how many weekdays have passed.

Comments - from the first entry of any names the working day should be calculated. Its should be from the 1st entry till the 5th working day the breach should be updated as Text1. Post 5th working day when ever the name repeats it should update as Text 2, 6th working day its should get updated as Text 3 from the day of 1st entry.


What is the entry for "Boss Srini" again on 11/10/2011 ?

Comments - Sorry, it was a typo error. I have updated the below table for your reference.


Why are there no further entries for "Vishal" ?

Comments - No further request has been received for Vishal


Can you please explain some more ?

Basically, i am looking for the names as how many times they are breaching considering from the date of 1st update

a) from the date of 1st update till 5th working days (Buffer)- 1st breach ( Text 1)

b) on 6th working day (5 working day + 1 working day) the breaches are considered as - 2nd breach (Text 2)

c) on 7th working day (6 working day + 1 working day) the breaches are considered as - 3rd breach (Text 3)

d) follows so on...


Kindly, let me know if you require any additional info on the same.


Regards,

Srini
 
Hi Srini ,


Thanks for the clarifications. However Vishal's case is still somewhat confusing.


Once an entry has been made on 11/1/2011 , shouldn't the system start tracking automatically ? How do you say that there are no further entries against Vishal because there are no further requests ? Does it mean the system tracking will happen only after there is a second entry against a particular name ?


What happens if there are multiple breach entries on a particular date ?


Narayan
 
Hi Srini ,


I think it's clear now.


All entries will be manually entered as Company Name concatenated with "Text 1".


When you run some macro or decide to update the worksheet through some means , the "Text 1" text should be replaced by the appropriate text "Text 2" or "Text 3" or whatever , depending on the number of days that have elapsed since the first entry against that company name.


Is this correct ?


Narayan
 
Hi Srini ,


One more doubt to be clarified :


If a company repeats , with gaps of a lot of days in between , the text "Text #" depends on the number of days which have elapsed or the number of breaches ?


In your example , consider that on 11/9/2011 , ABC and Com has been updated as "Text 4" ; now , the next breach is on 11/11/2011 ; should the text on this date be updated as "Text 5" or "Text 6" ?


Narayan
 
Dear Narayan,


Please find the below clarification.


When you run some macro or decide to update the worksheet through some means , the "Text 1" text should be replaced by the appropriate text "Text 2" or "Text 3" or whatever , depending on the number of days that have elapsed since the first entry against that company name.


Is this correct ?


Comments - Yes, you are correct.


If a company repeats , with gaps of a lot of days in between , the text "Text #" depends on the number of days which have elapsed or the number of breaches ?


In your example , consider that on 11/9/2011 , ABC and Com has been updated as "Text 4" ; now , the next breach is on 11/11/2011 ; should the text on this date be updated as "Text 5" or "Text 6" ?

Comments -

a) the text have to change by number of breaches

b) It should be "text 5"


Please let me know if you require any additional info on the same.


Regards,

Srini
 
Hi Srini ,


Can you please check out the following ? Copy and paste it in ThisWorkBook.


Public Sub Change_Breach_Text()

Const DAYS_BUFFER = 5

Dim Number_of_Rows, Row_Counter, len_company, check_count, Breach_Count As Integer

Dim Company, check_company, text_suffix, new_company As String

Dim curr_date, check_date As Date

Dim skip_company As Boolean

Application.ScreenUpdating = False

Start_Range = "B6"

End_Range = "B23"

ThisWorkbook.Sheets("Sheet1").Activate

Range(Start_Range, End_Range).Select

Number_of_Rows = Selection.Rows.Count

Row_Counter = 0

For Each Company_text In Selection

len_company = Len(Company_text)

Company = Left(Company_text, len_company - 6) ' Assumed that suffix will always be "Text #"

skip_company = False

For check_count = Row_Counter - 1 To 0 Step -1

If Range(Start_Range).Offset(check_count, 1).Value Like Company + "*" Then skip_company = True

Next

If Not skip_company Then

Range(Start_Range).Offset(Row_Counter, 1).Value = Company_text

curr_date = Range(Start_Range).Offset(Row_Counter, -1).Value

Breach_Count = 2

For check_count = Row_Counter + 1 To Number_of_Rows

check_company = Range(Start_Range).Offset(check_count, 0).Value

If check_company Like Company + "*" Then

check_date = Range(Start_Range).Offset(check_count, -1).Value

days_diff = Application.WorksheetFunction.NetworkDays(curr_date, check_date)

If days_diff > DAYS_BUFFER Then

text_suffix = Trim(Str(Breach_Count))

new_company = Company + "Text " + text_suffix

Range(Start_Range).Offset(check_count, 1).Value = new_company

Breach_Count = Breach_Count + 1

Else

Range(Start_Range).Offset(check_count, 1).Value = Company_text

End If

End If

Next

End If

Row_Counter = Row_Counter + 1

Next

Application.ScreenUpdating = True

End Sub


I am sure it can be improved on , but first , you need to confirm that it does the job !


Narayan
 
You must log in or register to reply here.
Back
Top