Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from records to another sheet automatically
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting information from records to another sheet automatic | Excel Worksheet Functions | |||
copy subtotal information to new sheet | Excel Worksheet Functions | |||
automatically delete records w/duplicate address in excel | Excel Discussion (Misc queries) | |||
how can i automatically copy the information in one cell on a she. | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |