Help...possibly with LOOKUP

[JuleeM13]

I’m driving myself batty trying to figure out how to do this.

On Sheet2 I have column A and B.
- Every cell in column B has text (a kid’s name).
- Some cells in column A may have a letter (a grade) or may have nothing. Some may have the same letter. Like this…

Sheet2
ColA ColB
A Mary
B Susie
C Johnny
Debbie
C Mark


Sheet1 is were I will put my formulas. I want to be able to specify what letter (grade) I want to look for (in this case C) and then return all the names from Sheet2 column B that have grade C in column A. It could be just one name or it could be 10 names. So in this example on Sheet1 I would want to see this (each name in it’s own cell)…

Sheet1
Johnny
Mark

I can sort of get it to work with the lookup function but that will only give me the first result even if I put the formula in more than one cell. How on earth do I do this?

Then let’s take it a step further. Let’s say my situation is the same as above only I have a different sheet for each class that are all set up the same way. This time however I want to look up which kids have a grade C in all of the classes (worksheets).

Can someone please help me with each of these scenarios? Thanks!

 

[stanleydgromjr]

JuleeM13,

See my Private Message to you (top right hand corner of MrExcel, Welcome, JuleeM13., "Your Notifications:".


Have a great day,
Stan

 

[Aladin Akyurek]

Sheet2, A2:B6

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>A

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Mary</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Susie</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Johnny</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23>Debbie</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>C</TD>

<TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Mark</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR></TBODY></TABLE>

Sheet1

<TABLE style="WIDTH: 42pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=56 x:str><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=56>Grade</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_1882919 class=xl24 height=17>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17>Count</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17>List</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Johnny</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Mark</TD></TR></TBODY></TABLE>

A2 a grade of interest.

A4:

=COUNTIF(Sheet2!A2:A6,A2)

A6:

Control+shift+enter, not just enter...

=IF(ROWS($A$6:A6)<=$A$4,INDEX(Sheet2!$B$2:$B$6,
   SMALL(IF(Sheet2!$A$2:$A$6=$A$2,
     ROW(Sheet2!$A$2:$A$6)-ROW(Sheet2!$A$2)+1),ROWS($A$6:A6))),"")

and copy down.

You could possibly create a series of lists, corresponding to the existing classes. Of course, if all the classes were in a single area the above scheme would immediately apply.

 

[JuleeM13]

Thank you. I don't exactly get it but it appears to be working! Is there a way to do exactly the same thing but instead of specifying "Sheet 2" to have it look for any worksheet other than sheet 1 where I'm doing my calculations? Each sheet would be set up the same. I guess like a sheet wild card? I will be adding other worksheets for different classes and it would be nice if it just searched for the students with the particular grade in any worksheet and list them without having to edit the formulas everytime I add a new worksheet/class. Thanks again.

 

[ossuary]

I think a coded solution might be better than formulas if you will be using more than one sheet (this way, if you add or remove sheets, it will continue to work without changing anything).

Keep your first sheet named "Sheet1", and put a command button on it. In the command button's code, put this:

Private Sub CommandButton1_Click()
Dim s1 As Worksheet
Dim sht As Worksheet
Set s1 = Sheets("Sheet1")

If Sheets("Sheet1").Range("A2").Value = "" Then Exit Sub
myRow = 8

s1.Range("A8:B1001").ClearContents
s1.Range("A5").Value = 0

For Each sht In Sheets
    If sht.Name <> "Sheet1" Then
        myCount = sht.Range("B65536").End(xlUp).Row
        If myCount > 0 Then
            For cnt = 1 To myCount
                If sht.Range("A" & cnt).Value = s1.Range("A2").Value Then
                    s1.Range("A" & myRow).Value = sht.Range("B" & cnt).Value
                    s1.Range("B" & myRow).Value = sht.Name
                    myRow = myRow + 1
                    s1.Range("A5").Value = s1.Range("A5").Value + 1
                End If
            Next cnt
        End If
    End If
Next sht
End Sub

You can name all of your other sheets based on whatever criteria you want to identify the class as (classroom number, teacher's name, whatever).

Cell A2 on Sheet1 is where you will put the grade you want information on.
Cell A5 will give you a count of how many matches were found.
Cell A8 and down will show the matching names, and Cell B8 and down will show the classroom that student was in (in case you have more than one student with the same name - easier to identify them this way!).

The code assumes that your other sheets won't have any header rows, just the names and grades. If you want header rows on the other sheets, change the part of the code that says:

        If myCount > 0 Then
            For cnt = 1 To myCount

to this instead:

        If myCount > 1 Then
            For cnt = 2 To myCount

Let me know if this helps.

 

[stanleydgromjr]

JuleeM13,

I have sent your workbook back. Please read the instructions on Sheet1.


Have a great day,
Stan