Extracting information from records to another sheet automatically
 

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
Molly

Extracting information from records to another sheet automatically
 

Actually, 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

InsertNameDefine
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$B$1

I1: Comment
I2: *

InsertNameDefine
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:
InsertNameDefine
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:
DataFilterAdvanced 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: InsertModule

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!Criteria"), _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=False
End Sub
'---Start of Code-------

To run the code:
ToolsMacroMacros (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-Pro


"Molly" wrote:

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
Molly

Extracting information from records to another sheet automatic
 

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

"Max" wrote:

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:B2, 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
---
"Molly" wrote:

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
Molly

Extracting information from records to another sheet automatic
 

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

"Ron Coderre" wrote:

Actually, 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

InsertNameDefine
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$B$1

I1: Comment
I2: *

InsertNameDefine
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:
InsertNameDefine
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:
DataFilterAdvanced 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: InsertModule

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!Criteria"), _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=False
End Sub
'---Start of Code-------

To run the code:
ToolsMacroMacros (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-Pro


"Molly" wrote:

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
Molly

Extracting information from records to another sheet automatic
 

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