none
Sugestion for Cnditions RRS feed

  • Question

  • I need to update Dates usning different conditions.

    I need to update Date for "Bangladesh/India/Pakistan-Enablement start"(Column B") on (Column D),
    but on other Worksheet it was given as "Project kick-off mobilisation meeting"
    so I cant use Vlookup for that. I need to update Dates , Problem is that data was not accurate evrytime. Rows are Up and down every time. is there any formula to be used or any macro which should be done. Second worksheet name was TMO.

    Need ypour Suggestion.


    Saniv Sharma

    Thursday, July 21, 2016 2:37 PM

Answers

  • Hi Saniv Sharma,

    yes, your code will only assign the values to "Bangladesh/India/Pakistan-Enablement Start"

    because you code like that.

    yes , I can see that the task name is same but the countries are different and some times the date is different.

    so here yes you can take the reference from column A1. code like below.

    I updated in my previous code.

    Sub dem()
    Dim lastrow As Long, Erow As Long
    Dim dt As Date
    Dim i As Integer
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    If Sheet1.Cells(i, 1) = "14BD" And Sheet1.Cells(i, 2) = "project kick off mobilization meeting" Then
    dt = Sheet1.Cells(i, 4).Value
    Exit For
    End If
    Next
    'Debug.Print dt
    lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    If Sheet2.Cells(i, 2) = "Bangladesh/India/Pakistan-Enablement Start" Then
    Sheet2.Cells(i, 4).Value = dt
    Exit For
    End If
    Next
    End Sub

    but here you will face a same problem. because I take the correct date of project related to country from sheet1 but there is nothing in sheet2 to check the project numbers.

    I can't see any column in sheet2 with that I can compare the project number like I did with sheet1.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 28, 2016 1:45 AM
    Moderator

All replies

  • Let me restate to confirm I understand:

    • You have two sheets.
    • Each sheet has a date that needs to be the same
    • Each sheet calls this date something different (which is why the VLookup won't work).
    • On one sheet the date is called 'Bangladesh/India/Pakistan-Enablement start'
    • On the second sheet it's called 'Project kick-off mobilisation meeting'

    If I have that right so far, how do YOU know the two dates are the same? What is it that links the two names together for you? Is that link consistent across all of the data, in other words are all 'Enablement Start' always the 'mobilization meeting'? Side question, on the sheet with 'mobilization meeting' entries, are they somehow unique or do they all say 'Project kick-off mobilization meeting'?

    Where I'm going is if you can build a key that is based on the relationship between the two differently named dates, you can use a vlookup with this new derived key.

    If there is no way to build a unique key, then you need to modify your data so you can.

    Thursday, July 21, 2016 4:00 PM
  • Hi Saniv Sharma,

    first of all I notice that you had created several threads for issues like you had mentioned above.

    like for date , and for the data like "Project kick-off mobilisation meeting".

    so first I want to confirm with you whether all cases are related with each other and having a combined issue and for simplicity you divided them in smaller parts and represent here to get solution or they are totally independent with each other and having separate issue. so if they are connected then I can try to merge them.

    you had mentioned that you want to Formula or macro to solve this issue.

    let me clear , this forum only handles issues regarding Excel Object Model.

    so to get information regarding Formula this is not a correct Forum. For Formula correct Forum is "Excel It Pro Discussions Forum". you can get Macro related suggestions here.

    now lets discuss about your question.  

    you had mentioned that , "I need to update Date for "Bangladesh/India/Pakistan-Enablement start"(Column B") on (Column D),"

    I did not understand properly. you want to update Date. Date is in Column B or in Column D ?

    How Date is related with "Bangladesh/India/Pakistan-Enablement start" or with "Project kick-off mobilisation meeting" i.e stored on different sheet.

    when and in which circumstances you want to update the Date?

    here I assume you want to update the date in sheet2 based on sheet1 if both the values of text are same. did I understand correctly? if not correct me.

    it is better if you share the snapshots of the sheet and what output you want. it will be easy for us to understand the requirement.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 22, 2016 2:21 AM
    Moderator
  • On one sheet the  column has  'Bangladesh/India/Pakistan-Enablement start'

    On the second sheet it's called 'Project kick-off mobilisation meeting'

    but the dates for both are same, So I need to pick Bangladesh dates and to be put on second sheet "kick off"

    if any time date will change on sheet 1, other one will change too.


    Saniv Sharma

    Friday, July 22, 2016 6:51 AM
  • Hi Deepak

    1) If in Sheet1 Project 14 have 2 Countries, eg 14BD and 14IN. then both contains the "kick off " as shown on column "B", and Date is on Column "D".  Now I want to pick The date of 1st kick off,(Eg= 16/7/15, 14 IN)  if the kick of having 15 dates then I just only need the 1st one,

    2) I need to put that date in front of combination of (Bangladesh/India/Pakistan-Enablement start) on sheet2

    No worries if we can add any column for our help.

    Sheet2:-

    Sheet1:-


    Saniv Sharma

    Friday, July 22, 2016 7:14 AM
  • Hi Saniv Sharma,

    what you can do is loop through all records in your sheet1.

    then use if condition to match the value "project kick-off mobilization meeting" if it find the value then check the date is > 15 then take the value then active sheet2 and find the value 'Bangladesh/India/Pakistan-Enablement start' and add the value in it and exit the sub.

    but here I think that you need to store your data properly. so that you not need to waste much time to arrange it.

    I think you can make a Access database to store your data , normalize your data. and all data having relationship with each other so that you can able to easily access them.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 22, 2016 8:53 AM
    Moderator
  • There are 89 Countries like thi

    Cant we do macro work to this because Dates are not always > 15 for other countries . It can be anything.



    Friday, July 22, 2016 9:12 AM
  • Hi Saniv Sharma,

    off course we create a macro for this.

    above mentioned is a logic to develop a macro.

    if date are not always > 15 then simply remove the if condition in which we are trying to check date is > 15.

    instead of that just directly copy the date from there and paste it in another sheet where you find text  'Bangladesh/India/Pakistan-Enablement start'.

    I had already provided you some example of macros in past some days.

    so you can try to implement this logic with the help of that.

    and if you stuck any where let us know and post your code here with your issue and we will try to help you.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 25, 2016 5:47 AM
    Moderator
  • I am using this code

    Dim lastrow As long.Erow As Long

    lastrow= Sheet1.cells(Rows.count,1).End(xlUp).Row
    For i=2 to lastrow

    if sheet1 .cells(i,2)= Migration End" Then
    sheet1.cells(i,1).copy
    erow = Sheet2.cells(Rows.count,1).End(xlUp).offset(1,0).Row

    Sheet1.Paste Destination = Worksheets("Sheet2").Cells(erow,1)

    Sheet1.cells(i,4).copy
    Sheet1.Paste Destination = Worksheets("Sheet2").Cells(erow,2)

    End If


    Saniv Sharma

    Tuesday, July 26, 2016 9:09 AM
  • Hi Saniv Sharma,

    I will look in to that and let you know.

    you are using a new code now? because I find that you had deleted the old code.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 26, 2016 9:11 AM
    Moderator
  • Hi Saniv Sharma,

    I edited your code and now its working as per your requirement.

    you can see it below.

    Below is the Sheet1 I created for demo.

    below is Sheet2.

    you can see that Date is empty in Sheet2.

    Following is the updated code.

    Sub dem()
    Dim lastrow As Long, Erow As Long
    Dim dt As Date
    Dim i As Integer
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    If Sheet1.Cells(i, 2) = "project kick off mobilization meeting" Then
    dt = Sheet1.Cells(i, 4).Value
    Exit For
    End If
    Next
    'Debug.Print dt
    lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    If Sheet2.Cells(i, 2) = "Bangladesh/India/Pakistan-Enablement Start" Then
    Sheet2.Cells(i, 4).Value = dt
    Exit For
    End If
    Next
    End Sub
    

    Following is the output I got.

    you can see that date is assigned in Sheet2.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 26, 2016 9:49 AM
    Moderator
  • Thanks for the code Deepak.

    but it was not picking up from my end. I'll try to do something with it.


    Saniv Sharma

    Tuesday, July 26, 2016 11:39 AM
  • I am applying the kick offs for Task Data(sheet1) to TMO(sheet2)

    but it was picking the same date of Bangladesh/India/Pakistan for all

    Czech Republic/Poland-Enablement   start
    Russian Federation-Enablement   start
    Eastern Europe  - Enablement start

    Italy-Enablement start

    Cant we take refrence from Column "A" project Number for counting Evry Country. I will check with that if I get somthing


    Saniv Sharma


    Tuesday, July 26, 2016 11:58 AM
  • Hi Saniv Sharma,

    here based on your description I assume that you are not passing the whole string available in the cell instead of that you are just passing the string like "kick offs". in both sheets you have to pass the whole strings to search the exact match.

    if you pass like I had passed in my last example then you not need to set reference for another column. it will work correctly.

    if you still have issue let us know. also try to provide snap shots of the issue will help us to understand the issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 27, 2016 7:01 AM
    Moderator
  • I am Using the folloeing code now, These are both the files "Task Data" and "TMO"

    Dim lastrow As Long, Erow As Long
    Dim dt As Date
    Dim i As Integer

    ThisWorkbook.Sheets("TaskData").Activate

    lastrow = Cells(rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    If Cells(i, 2) = "project kick off meeting" Then
    dt = Cells(i, 4).Value

    ElseIf Cells(i, 2) = "Project kick-off mobilisation meeting" Then
    dt = Cells(i, 4).Value

    Exit For

    End If

    Next i

    Sheets("TMO").Activate

    Debug.Print dt
    lastrow = Sheet1.Cells(rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    If TMO.Cells(i, 2) = "Bangladesh/India/Pakistan-Enablement Start" Then
    TMO.Cells(i, 4).Value = dt
    Exit For

    End If

    Next

    TaskData


    Saniv Sharma

    Wednesday, July 27, 2016 7:39 AM
  • Hlo Deepak,

    Please help me to solve this, I Only have 1 day to complete this.

    Thanks


    Saniv Sharma

    Wednesday, July 27, 2016 12:36 PM
  • Hi Saniv Sharma,

    yes, your code will only assign the values to "Bangladesh/India/Pakistan-Enablement Start"

    because you code like that.

    yes , I can see that the task name is same but the countries are different and some times the date is different.

    so here yes you can take the reference from column A1. code like below.

    I updated in my previous code.

    Sub dem()
    Dim lastrow As Long, Erow As Long
    Dim dt As Date
    Dim i As Integer
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    If Sheet1.Cells(i, 1) = "14BD" And Sheet1.Cells(i, 2) = "project kick off mobilization meeting" Then
    dt = Sheet1.Cells(i, 4).Value
    Exit For
    End If
    Next
    'Debug.Print dt
    lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    If Sheet2.Cells(i, 2) = "Bangladesh/India/Pakistan-Enablement Start" Then
    Sheet2.Cells(i, 4).Value = dt
    Exit For
    End If
    Next
    End Sub

    but here you will face a same problem. because I take the correct date of project related to country from sheet1 but there is nothing in sheet2 to check the project numbers.

    I can't see any column in sheet2 with that I can compare the project number like I did with sheet1.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 28, 2016 1:45 AM
    Moderator
  • Hello Deepak

    Thank You so much, for your help.

    I used macro provided by you, It helped me a lot.

    I added some columns for refrence and then my work have been done.


    Saniv Sharma

    Friday, July 29, 2016 2:23 PM