match & extract

[dubdub]

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

[sdckapr]

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

[dubdub]

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.

[sdckapr]

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

[dubdub]

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

[sdckapr]

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....

[dubdub]

Many Thanks Steve,

it works. date as usual needs special care.

dubdub