Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 9 of 9

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

  1. #1
    Join Date
    20th October 2016
    Posts
    27

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  2. #2
    Join Date
    2nd March 2010
    Location
    God's Own Country
    Posts
    3,910

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

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

  3. #3
    Join Date
    20th October 2016
    Posts
    27

    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

  4. #4
    Join Date
    20th October 2016
    Posts
    27

    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

  5. #5
    Join Date
    2nd March 2010
    Location
    God's Own Country
    Posts
    3,910

    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.

  6. #6
    Join Date
    20th October 2016
    Posts
    27

    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 =)

  7. #7
    Join Date
    2nd March 2010
    Location
    God's Own Country
    Posts
    3,910

    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.

  8. #8
    Join Date
    20th October 2016
    Posts
    27

    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

  9. #9
    Join Date
    2nd March 2010
    Location
    God's Own Country
    Posts
    3,910

    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.

    VB:
    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 
    
    

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: November 11th, 2015, 23:06
  2. Replies: 1
    Last Post: November 17th, 2011, 22:31
  3. Match 2 Criteria & Return Results
    By Tee in forum Excel General
    Replies: 2
    Last Post: November 3rd, 2006, 19:18
  4. Match criteria then replace data - faster
    By JMAN in forum Excel General
    Replies: 6
    Last Post: June 23rd, 2006, 05:21
  5. multiple criteria match showing mixed results
    By kimberly in forum Excel General
    Replies: 7
    Last Post: November 18th, 2004, 11:22

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno