Results 1 to 7 of 7

Thread: match & extract

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    match & extract

    hi all,

    i have two sheets, the first sheet has two columns for example id & name, the other sheet has the same column but more data like starting date and ending date, what i need is a formula/code that will do the match based in the name & id and eatract the minimum starting date from the starting date column and the maximum end date from the ending date and put these two extra columns in the first sheet.

    TIA
    dubdub
    Attached Files Attached Files
    Last edited by dubdub; 2011-08-04 at 11:50.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You could use an array formula (confirm with ctrl-shift-enter) like in C2:
    =MIN(IF((Sheet2!$A$2:$A$100=A2)*(Sheet2!$B$2:$B$10 0=B2),Sheet2!$C$2:$C$100))

    and in D2:
    =MAX(IF((Sheet2!$A$2:$A$100=A2)*(Sheet2!$B$2:$B$10 0=B2),Sheet2!$D$2:$D$100))

    Presuming that ID/Name in the sheet of interest are in Col A& B in the current sheet and in Sheet2 in A2:A100 you have ID, B2:B100 you have names, c2:C100 you have start dates and D2:d100 you have end dates

    Then you can copy c2 and D2 down the columns...

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks Steve,

    i used the formulas in c2 & d2 but i got 1/0 & 1/0, it seems it does not capture the date format. any suggestion.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am confused by the file you attached as it is nothing like your description. I think you are getting "Jan 0" since it can not find a match since most of the data row you want to extract are blank. Fill in the rows completely with names and IDs and it should work fine.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    sorry for the confusion, but i did fill the blank, i have even change the * name to a 100 and filled the blank, but still no picking of the right date. one other thing any reason why the , in the formula has to change to ; to see output.

    dubdub
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Your "dates" are not numbers but text that looks like a date. Right-click a blank cell and choose copy. Then select all the dates, right-click paste-special, paste-special and choose "add". The text will converted to numbers.

    Steve
    PS make sure you also confirm all formulas with ctrl-shift-enter....
    Last edited by sdckapr; 2011-08-04 at 19:14.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Many Thanks Steve,

    it works. date as usual needs special care.

    dubdub

Posting Permissions

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