Dear experts,
I have a long list of students and assessment item results. The last column
is a comments column for my use. I enter short descriptions of
recommendations here. Not every student has a comment. I would like to be
able to extract out the details of the student (id, name, surname) along with
the contents of the recommendation column (text). I have made some attempts
with vlookups but I cannot omit the students without comments with this
method. Do I need an array formula? Your assistance would greatly shortcut
my efforts in summarising this data.
kind regards
Molly

It sounds like you want to see the list but only the rows that contain
comments. If this is correct then maybe what you want is to use
AutoFilter.

Select the comments heading then click on Data - Filter - AutoFilter

Click on the down arrow by the comments heading and select the
(NonBlanks) item in the list.

Thank you for your response. I need however to leave the original list
entirely viewable and automatically extract out the students and their
comments to another sheet in the workbook for further processing.
kind regards
Molly

" wrote:

It sounds like you want to see the list but only the rows that contain
comments. If this is correct then maybe what you want is to use
AutoFilter.

Select the comments heading then click on Data - Filter - AutoFilter

Click on the down arrow by the comments heading and select the
(NonBlanks) item in the list.

"Molly" wrote:
... I need however to leave the original list
entirely viewable and automatically extract out the students and their
comments to another sheet in the workbook for further processing.

Posted the response below earlier ..
--
Here's one way, using non-array formulas ..

A sample construct is available at:
http://cjoint.com/?cenY5tJ250
Extracting information from records to another sheet
automatically_Molly_wks.xls

Source table assumed in Sheet1, in cols A to L, data from row2 down
(Col A = Names, col L = Comments)

In Sheet2,
Labels in A1:B1 : Name, Comment

Put in A2:
=IF(ISERROR(SMALL($C:$C,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C, 0)))

Put in B2:
=IF(ISERROR(SMALL($C:$C,ROW(A1))),"",
INDEX(Sheet1!L:L,MATCH(SMALL($C:$C,ROW(A1)),$C:$C, 0)))

Put in C2: =IF(TRIM(Sheet1!L2)<"",ROW(),"")

Select A2:C2, fill down to cover the extent of data in Sheet1
Sheet2 will return the required results, with all lines neatly bunched at
the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---