software

Dear experts,
I have a list of students with ten columns of data to the right and then an
eleventh column containing textual comments.

I want excel to automatically look at the range of student results
(A5:L300), determine which students have comments (not all students have
comments) and extract the student name and the comment and display this on
another sheet.

I can see how this can be done with an advanced filter however:
I want the results on another sheet and I don't want to have to do it
manually.

Is there any way I can achieve this?

kind regards
MollyActually, Advanced Filter can do what you want:

Here's an approach to try:
Assumptions:
Sheet1 contains your data in cells A5:L300
Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: StudentName
B1: Comment

Insertgt;Namegt;Define
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$B$1

I1: Comment
I2: *

Insertgt;Namegt;Define
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:
Insertgt;Namegt;Define
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$5:$L$3000

(Notice: you are on Sheet2, and creating a Sheet2-level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
Datagt;Filtergt;Advanced Data Filter
Select: Copy to another location
List Range: (press F3 and select Database)
Criteria Range: (press F3 and select Criteria)
Copy To: (press F3 and select Extract)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
.....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt] [F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: Insertgt;Module

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range(quot;Sheet2!Databasequot;).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range(quot;Sheet2!Criteriaquot;), _
CopyToRange:=Range(quot;Sheet2!Extractquot;), _
Unique:=False
End Sub
'---Start of Code-------

To run the code:
Toolsgt;Macrogt;Macros (or [Alt] [F8])
Select and run: PullMatchingData

To test, change the value of I2 and run it again.Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Mollyquot; wrote:

gt; Dear experts,
gt; I have a list of students with ten columns of data to the right and then an
gt; eleventh column containing textual comments.
gt;
gt; I want excel to automatically look at the range of student results
gt; (A5:L300), determine which students have comments (not all students have
gt; comments) and extract the student name and the comment and display this on
gt; another sheet.
gt;
gt; I can see how this can be done with an advanced filter however:
gt; I want the results on another sheet and I don't want to have to do it
gt; manually.
gt;
gt; Is there any way I can achieve this?
gt;
gt; kind regards
gt; Molly
gt;

Thank you so much Max. I had lost hope.
regards
Molly

quot;Maxquot; wrote:

gt; Here's one way, using non-array formulas ..
gt;
gt; A sample construct is available at:
gt; cjoint.com/?cenY5tJ250
gt; Extracting information from records to another sheet
gt; automatically_Molly_wks.xls
gt;
gt; Source table assumed in Sheet1, in cols A to L, data from row2 down
gt; (Col A = Names, col L = Comments)
gt;
gt; In Sheet2,
gt; Labels in A1:B1 : Name, Comment
gt;
gt; Put in A2:
gt; =IF(ISERROR(SMALL($C:$C,ROW(A1))),quot;quot;,
gt; INDEX(Sheet1!A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C, 0)))
gt;
gt; Put in B2:
gt; =IF(ISERROR(SMALL($C:$C,ROW(A1))),quot;quot;,
gt; INDEX(Sheet1!L:L,MATCH(SMALL($C:$C,ROW(A1)),$C:$C, 0)))
gt;
gt; Put in C2: =IF(TRIM(Sheet1!L2)lt;gt;quot;quot;,ROW(),quot;quot;)
gt; Select A2:B2, fill down to cover the extent of data in Sheet1
gt;
gt; Sheet2 will return the required results,
gt; with all lines neatly bunched at the top
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Mollyquot; wrote:
gt;
gt; gt; Dear experts,
gt; gt; I have a list of students with ten columns of data to the right and then an
gt; gt; eleventh column containing textual comments.
gt; gt;
gt; gt; I want excel to automatically look at the range of student results
gt; gt; (A5:L300), determine which students have comments (not all students have
gt; gt; comments) and extract the student name and the comment and display this on
gt; gt; another sheet.
gt; gt;
gt; gt; I can see how this can be done with an advanced filter however:
gt; gt; I want the results on another sheet and I don't want to have to do it
gt; gt; manually.
gt; gt;
gt; gt; Is there any way I can achieve this?
gt; gt;
gt; gt; kind regards
gt; gt; Molly

Thank you so much. I am very grateful for your willingness to share your
expertise.
Many thanks
Molly

quot;Ron Coderrequot; wrote:

gt; Actually, Advanced Filter can do what you want:
gt;
gt; Here's an approach to try:
gt; Assumptions:
gt; Sheet1 contains your data in cells A5:L300
gt; Sheet2 is where you want the extracted data to be displayed
gt;
gt; Using Sheet2:
gt; A1: StudentName
gt; B1: Comment
gt;
gt; Insertgt;Namegt;Define
gt; Names in workbook: Sheet2!Extract
gt; Refers to: =Sheet2!$A$1:$B$1
gt;
gt; I1: Comment
gt; I2: *
gt;
gt; Insertgt;Namegt;Define
gt; Names in workbook: Sheet2!Criteria
gt; Refers to: =Sheet2!$I$1:$I$2
gt;
gt; Still using Sheet2:
gt; Insertgt;Namegt;Define
gt; Names in workbook: Sheet2!Database
gt; Refers to: =Sheet1!$A$5:$L$3000
gt;
gt; (Notice: you are on Sheet2, and creating a Sheet2-level range name, but
gt; the referenced range is on Sheet1)
gt;
gt; The reason: An advanced filter cannot SEND data to another sheet, but
gt; it can PULL data from another sheet.
gt;
gt; Now...set up the Advanced Data Filter:
gt; Datagt;Filtergt;Advanced Data Filter
gt; Select: Copy to another location
gt; List Range: (press F3 and select Database)
gt; Criteria Range: (press F3 and select Criteria)
gt; Copy To: (press F3 and select Extract)
gt; Click [OK]
gt;
gt; Note: if you want to run that Advanced Data Filter repeatedly,
gt; you'll need to re-select Database each time
gt; ....OR...if you're feeling a bit ambitious...
gt;
gt; You can build a simple macro to automatically re-run the filter:
gt; Press [Alt] [F11] to open the VBA editor
gt; Right click on the VBA Project folder for your workbook
gt; Select: Insertgt;Module
gt;
gt; Then, copy/paste this code into that module:
gt;
gt; '---Start of Code-------
gt; Option Explicit
gt; Sub PullMatchingData()
gt; Range(quot;Sheet2!Databasequot;).AdvancedFilter _
gt; Action:=xlFilterCopy, _
gt; CriteriaRange:=Range(quot;Sheet2!Criteriaquot;), _
gt; CopyToRange:=Range(quot;Sheet2!Extractquot;), _
gt; Unique:=False
gt; End Sub
gt; '---Start of Code-------
gt;
gt; To run the code:
gt; Toolsgt;Macrogt;Macros (or [Alt] [F8])
gt; Select and run: PullMatchingData
gt;
gt; To test, change the value of I2 and run it again.
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Mollyquot; wrote:
gt;
gt; gt; Dear experts,
gt; gt; I have a list of students with ten columns of data to the right and then an
gt; gt; eleventh column containing textual comments.
gt; gt;
gt; gt; I want excel to automatically look at the range of student results
gt; gt; (A5:L300), determine which students have comments (not all students have
gt; gt; comments) and extract the student name and the comment and display this on
gt; gt; another sheet.
gt; gt;
gt; gt; I can see how this can be done with an advanced filter however:
gt; gt; I want the results on another sheet and I don't want to have to do it
gt; gt; manually.
gt; gt;
gt; gt; Is there any way I can achieve this?
gt; gt;
gt; gt; kind regards
gt; gt; Molly
gt; gt;

You're welcome, Molly !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Mollyquot; gt; wrote in message
...
gt; Thank you so much Max. I had lost hope.
gt; regards
gt; Molly