Which Function do I use?
Hi,
the following array formula was tested. It can be made simpler if
necessary. Its range and sheet name assumptions are according to your
post.
It is an *array* formula, so you need to commit it with
Ctrl+Shift+Enter.
Place this formula in B1 of Sheet2 and copy down and accross.
=IF(ISNUMBER(SMALL(IF(T(OFFSET(Sheet1!$A$2:$A$256, ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2),MATCH($A2,Sheet1!$B$1:$G$1,0)))=" X",ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2)+1),COLUMNS($B$1:B$1))),INDEX(Shee t1!$A$2:$A$256,SMALL(IF(T(OFFSET(Sheet1!$A$2:$A$25 6,ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2),MATCH($A2,Sheet1!$B$1:$G$1,0)))=" X",ROW(Sheet1!$A$2:$A$256)-ROW(Sheet1!$A$2)+1),COLUMNS($B$1:B$1))),"")
HTH
Kostis Vezerides
|