Announcement

Collapse
No announcement yet.

Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

    Weekly We Receive Data Via Whatsapp, Emails & Hardcopy List in order to Replace the Results & the Day it was Executed.

    Let's Say there are 980 Items in total in one Category and on a random day around 27 was Conducted with a Test.

    we Have to Access the main File (DTL3 Trains TR2) and Replace them Manually. It is Extremely Time Consuming Because we have to Manually Look up for the Category and Overwrite the Results & Date Executed.

    So i created a Test File Called (ZQ File), Instead of Manually Searching & Overwriting, we type in the Following Requirements such as Alarm ID, Train & Alarm Category Along with their Corresponding Results & Date Executed.

    In the ZQ File, There are Four Green Tabs Highlighted which are the Category for the Alarms , i need a Macro which could Categorize & Match The Three Criteria Above & Overwrite the corresponding Date & Results From ZQ File to DTL3 Trains Files. Please Note that the ZQ File is to be Updated, so the macro has to be able to Lookup Newly Inputted Data & Overwrite them in DTL3 when it runs.

    No Deadline for this Job. Anyone can Take the Task. Transaction ID : 9LX91299SE176464C

    Please Note that i can't upload the DTL3 Excel File here cause its 4MB, So i Uploaded to Dropbox and Pasted it Below.

    https://www.dropbox.com/s/q5fbv6rue6...d%29.xlsx?dl=0
    Attached Files

  • #2
    Re: Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

    I can have a look, given that it's not urgent
    Cheers,

    S M C

    Click To Read: How To Use Tags In Your Threads/Posts
    Please take time to read Forum Rules before posting
    Message To Cross Posters

    Comment


    • #3
      Re: Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

      Sure go ahead take your time. I an raising it up to 20 USD Max cause I think this Task is pretty Tough.
      The ZQ File is just a dummy file. Meant to just key in the value we Obtained from other sources. You can just use a New workBook with the only four important sheet's instead of ZQ File

      Comment


      • #4
        Re: Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

        Most likely going to change the Objective Since It has been a month. Gonna assume that this Task is not worth 15 USD

        Will Put up a New thread in Three Days. Probably going to be About adding one Function in VBA on Adjustable Rows

        Comment


        • #5
          Re: Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

          Hi, apologies this got skipped for so long. Was busy with family matters (new member), and lost track. If you are still looking for this, I will work on it. As compensation for the delay, without any payment. Let me know.

          Oh and Merry Christmas.
          Cheers,

          S M C

          Click To Read: How To Use Tags In Your Threads/Posts
          Please take time to read Forum Rules before posting
          Message To Cross Posters

          Comment


          • #6
            Re: Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

            Oh no please Continue, I still do want this task to be completed. And the 15 USD Offer still stands. Free Labour is Just Distasteful. I just sorta want It Done before 20th Jan 2017.

            Oh and My Biggest Congratulations & Merry Christmas =)

            Comment


            • #7
              Re: Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

              Thank you very much. Fair enough. hopefully the dropbox link is still active.
              Cheers,

              S M C

              Click To Read: How To Use Tags In Your Threads/Posts
              Please take time to read Forum Rules before posting
              Message To Cross Posters

              Comment


              • #8
                Re: Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

                Had to Re-Up the Dropbox Link Again Below.

                https://www.dropbox.com/s/je85o5skbc...2016.xlsx?dl=0

                Comment


                • #9
                  Re: Categorize & Match Criteria's To Replace Results in another Workbook (USD $15)

                  Code sent to OP. Free of charge due to delay from my side, though OP had extended the timelines till 22nd Jan. Just posting it here for posterity.

                  Code:
                  Sub sUpdateDL3TrainScheduleFromZQFile()
                  
                  
                      Dim strCurrentFeedingSheetName As String
                      Dim strDestinationWorkbook As String
                      Dim wbkDestinationWorkbook As Workbook
                      Dim wksDestinationWorkSheet As Worksheet
                      Dim lngLoopSourceSheetItems As Long
                      Dim lngDestinationRow As Long
                      Dim lngDestinationFirstColumn As Long
                      
                      'This routine will skip execution for each row which has a "Y" in column H
                      'So ensure you clear cells in Column H for those rows which needs to be updated
                      With Application.FileDialog(msoFileDialogFilePicker)
                          .AllowMultiSelect = False
                          .Filters.Add "Excel 2003 & above files", "*.xls; *.xlsx", 1
                          If .Show = -1 Then
                              strDestinationWorkbook = .SelectedItems(1)
                          Else
                              MsgBox "This program will now exit.", vbOKOnly, "File not selected by user"
                              GoTo EndRoutine
                          End If
                      End With
                      On Error GoTo EndRoutine
                      strCurrentFeedingSheetName = ThisWorkbook.ActiveSheet.Name
                      Set wbkDestinationWorkbook = Workbooks.Open(strDestinationWorkbook)
                      Set wksDestinationWorkSheet = wbkDestinationWorkbook.Worksheets(strCurrentFeedingSheetName)
                      With ThisWorkbook.ActiveSheet
                          For lngLoopSourceSheetItems = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
                              'Anything other than a "Y" in column H means that it is not updated in the DTL3 file, and needs to be processed
                              If UCase(.Cells(lngLoopSourceSheetItems, 8).Value) <> "Y" Then
                                  lngDestinationRow = fIFERROR(.Cells(lngLoopSourceSheetItems, 1).Value, wksDestinationWorkSheet.UsedRange.Columns(1), 0)
                                  lngDestinationFirstColumn = fIFERROR(.Cells(lngLoopSourceSheetItems, 2).Value, wksDestinationWorkSheet.UsedRange.Rows(2), 0)
                                  If lngDestinationRow * lngDestinationFirstColumn Then
                                      wksDestinationWorkSheet.Cells(lngDestinationRow, lngDestinationFirstColumn).Value = .Range("F" & lngLoopSourceSheetItems).Value
                                      wksDestinationWorkSheet.Cells(lngDestinationRow, lngDestinationFirstColumn + 1).Value = .Range("E" & lngLoopSourceSheetItems).Value
                                      wksDestinationWorkSheet.Cells(lngDestinationRow, lngDestinationFirstColumn + 2).Value = .Range("G" & lngLoopSourceSheetItems).Value
                                      .Cells(lngLoopSourceSheetItems, 8).Value = "Y"
                                      lngDestinationRow = Empty
                                      lngDestinationFirstColumn = Empty
                                  Else
                                      .Cells(lngLoopSourceSheetItems, 8).Value = "Unable to find the combination. Please check"
                                  End If
                              End If
                          Next lngLoopSourceSheetItems
                      End With
                      
                  ExitSub:
                      strCurrentFeedingSheetName = vbNullString
                      strDestinationWorkbook = vbNullString
                      Set wbkDestinationWorkbook = Nothing
                      Set wksDestinationWorkSheet = Nothing
                      lngLoopSourceSheetItems = Empty
                      lngDestinationRow = Empty
                      lngDestinationFirstColumn = Empty
                  
                  
                      Exit Sub
                  EndRoutine:
                  If lngLoopSourceSheetItems Then
                      MsgBox "The program terminated at row number " & lngLoopSourceSheetItems & vbLf & vbLf & "Please check before continuing the match and update process....", vbOKOnly, Err.Description
                  Else
                      MsgBox "Please check what could be the issue...", vbExclamation + vbOKOnly, "Unknown error...."
                      wbkDestinationWorkbook.Close 1 '0 in case you want to close without saving any changes
                  End If
                  Err.Clear: On Error GoTo 0: On Error GoTo -1
                  GoTo ExitSub
                  
                  
                  End Sub
                  
                  
                  Private Function fIFERROR(ParamArray varInputs()) As Variant
                  
                  
                      On Error Resume Next
                      fIFERROR = Application.Match(varInputs(0), varInputs(1), 0)
                      If fIFERROR = "Error 2042" Then
                          fIFERROR = varInputs(2)
                          Err.Clear: On Error GoTo 0: On Error GoTo -1
                      End If
                      
                  End Function
                  Cheers,

                  S M C

                  Click To Read: How To Use Tags In Your Threads/Posts
                  Please take time to read Forum Rules before posting
                  Message To Cross Posters

                  Comment

                  Working...
                  X