Originally Posted by
rylo
Hi
If the data on sheet1 is in the range A1:C17 then on sheet2:
A1: =IF(B1="","",SUMPRODUCT(--(Sheet1!$C$1:$C$17=Sheet2!B1),(Sheet1!$B$1:$B$17)))
B1: =IF(ROW()>SUM(1/COUNTIF(Sheet1!$C$1:$C$17,Sheet1!$C$1:$C$17)),"",INDEX(Sheet1!C:C,SMALL(IF(ISNUMBER(MATCH(ROW(Sheet1!$C$1:Sheet1!$C$17),MATCH(Sheet1!$C$1:Sheet1!$C$17,Sheet1!$C$1:Sheet1!$C$17,0),0)),ROW(Sheet1!$C$1:Sheet1!$C$17),""),ROW())))
The formula in B1 is array entered. Hold down the Shift and CTRL keys, then press enter.
Copy A1:B1 and paste down to A17.
HTH
rylo
Bookmarks