Default Extracting value from another sheet based on nested conditions

A

alikhalil

New member
Joined
Jan 29, 2017
Messages
27
Reaction score
0
Points
0
So here's the case. I have two sheets. The DA SR EDW sheet lists all SRs and along with them the different tasks that were opened by different task owners (thus duplicate SRs). The second sheet DA SR Consolidated I have just the SR and the call out employee (equivalent to the task owner name in the DA SR EDW sheet). In very simple terms, I'd like to look up the SR AND the task owner name and if they together match with what's in the second sheet, I'd like the three columns to be filled: Task Creation Date, Task Start Date, Task Closed date, in the DA SR Consolidated if the task is available in DA SR EDW.

Here's sheet 1 : DA SR EDW

dfsfad.jpg



And Sheet 2 DA SR Consolidated

sdfafsdf.jpg


Please help else i have to manually look up every single SR and i have about 500 piled up.


When i wrote nested conditions in the title I meant that first search the SR if it matches then within that SR look for the task owner name.
 
Your thread has been approved on the understanding that you comply with the forum rules on cross-posting. Please provide a link to your identical thread on the Mr Excel forum. Thanks.
 
Please attach your workbook here. We cannot work with an image of one.
 
Code: 
Option Explicit
Sub test()
    Dim cel As Range, sr1 As Range, sr2 As Range, data1 As Range, data2 As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim arr1(), arr2(), a2
    Dim i As Long, x As Long
    Set ws1 = Sheets("DA SR EDW")
    Set ws2 = Sheets("DA SR Consolidated")
    Set data1 = Range(ws1.Cells(1, 1), ws1.Cells(Rows.Count, 1).End(xlUp))
    Set data2 = Range(ws2.Cells(1, 1), ws2.Cells(Rows.Count, 1).End(xlUp))
    ReDim arr1(data1.Count)
    ReDim arr2(data2.Count)
    
    i = 0
    For Each cel In data1
        arr1(i) = cel & cel.Offset(, 16)
        i = i + 1
    Next
    i = 0
    For Each cel In data2
        arr2(i) = cel & cel.Offset(, 7)
        i = i + 1
    Next
    i = 0
    On Error Resume Next
    For Each a2 In arr2
        x = 0
        i = i + 1
        x = Application.Match(a2, arr1, 0)
        If x > 0 And Len(a2) > 7 Then
            ws2.Cells(i, 4).Resize(, 3).Value = ws1.Cells(x, 19).Resize(, 3).Value
        End If
    Next a2
End Sub
 
That did it! I just ran it after googling how to run vb code on excel cause I had no idea it would involve code and would be that complicated. Thank god the steps were easier to follow than what I thought. I considered myself at a fairly intermediate level with excel but clearly I know nothing! I'll test out the results tomorrow morning but for now thank you so much, this will help with even future projects and inspired me to start learning VB and excel a lot more seriously, I'll try dissecting and making sense of the code tomorrow because I want to implement the same solution for the rest of the sheet pairs as well.

Thank you again so much. Would you direct me to an online source that is interactive and useful to learn advanced excel stuff? I really think I ought to after this.
 
I really didn't think anyone would dedicate his personal time to create specific solutions for us. This is really amazing, thank you again.
 
Never gave much thought to a formula solution but gave it a try. Hope they match!
 

Attachments

  • Overtime (2016-2nd Quarter) - Fmla.xlsx
    276.6 KB · Views: 6
@Malcolm , I've tried copying the formula to the original workbook with the exact same formatting and structure to the copy I gave you bar the sheets I deleted to minimize the size but it doesn't work. Have you applied any formatting or are there other steps i'm not aware of?
 
I'm attaching the otherfile I'm working on, i added a pair similar to the ones you worked on but for another team. Anyway I'm trying to duplicate the formula with the pair DA SR EDW/DA SR Consolidated and ICT SR EDW/ICT DR Consolidated in this workbook.

View attachment Overtime (2016-2nd Quarter).xlsx

Not working out so far :(
 
There was an error in the range names in the copied workbook which I've corrected.
Data1 should read =OFFSET('DA SR EDW'!$A$1,0,0,COUNTA('DA SR EDW'!$A:$A),1)
For your second comparison you need 2 additional named ranges as before (Data3, Data4)
The macro solution was reurning dates offset 1 column to the right; the code line should read
ws2.Cells(i, 4).Resize(, 3).Value = ws1.Cells(x, 18).Resize(, 3).Value
I've added a sheet (blue tab) showing the macro result with a simple cross check in columns AC-AE

If you're unfamiliar with dynamic range names, I created this utility a few years ago to assist.
 

Attachments

  • Overtime (2016-2nd Quarter).xlsm
    485.5 KB · Views: 6
Last edited:
Thank you for all your efforts, it completely solved my problem. If you don't mind though, I'd like to learn some of these things so I would be able to manipulate and play around with the formula. This opened my eyes to how much I need to learn, I'll go through your utility and try it out by myself but would it be okay to contact you via pm after that?
 
You must log in or register to reply here.
Back
Top