[RESOLVED] Getting Grade wise names

[abhi51082]

Hi All,

I have an excel sheet attached herewith..

I want a formula that gives me all the names whose grade is "C"..

Can anyone please help?

Thanks in advance...

[westconn1]

that gives me all the names

to do what?
you can use countif to count the number of names with a specific grade
or you could probably use a formula array to put all those name into other cells
you could use conditional formatting to change the colours of the cells for each grade

[abhi51082]

Hi Westconn,

What i want is, I have 2 columns, 1 column is of names of individuals and 2nd column is of Grade. Now in a differently located cell if i write "C" then below that should appear all the names from Column 1 whose adjecent cell in 2nd column shows "C".

I tried using VLookUp formula but it is not giving me the correct answer.

I am not good with array... Could you please help me for that...

Just to tell you that i have 2000 or more rows with such data...

Thanks in advance...

[TerryTee]

If you are happy with doing it directly on the data (and not a different location as you say) then just use AutoFilter. Have a look at AutoFilter in the Help file.

-Terry

[westconn1]

i could create a custom worksheet function to do what you want, but better to use inbuilt functions or methods wherever possible

[abhi51082]

TerryTee...
Auto filter is good and i have also used it but it would not give me an answer in a different column...

Westconn1...
I am not able to find out any inbuilt functions or method for sorting out my problem...
If you have anything which can help me or any function or method which can solve such query then please state the same...

[Siddharth Rout]

Abhi

Howz A'bad doing without me?

Further to our conversation, it is not possible to do this with a native function. You will have to use VBA like Pete suggested. Instead of a function you could write a code for the worksheet selection change event which will capture the letter that you will type and then search for the names meeting that criteria and finally display them in the respective column....

[westconn1]

afaik it is not possible to update any cells from within a worksheet function except the calling cell

i did get this to work reasonably well (just forgot to post it)

'in a module
Public myarr() As Variant
 
Function getgrades(r As Range, grade As String) As String
Dim res() As Variant
 
i = 0
For Each c In r
    If c = grade Then ReDim Preserve res(i): res(i) = c.Offset(, 1): i = i + 1
    If c = grade Then mystr = mystr & vbLf & c.Offset(, 1)
Next
getgrades = ""
myarr = res
End Function
 
' in the worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If InStr(Target.Formula, "getgrades") = 0 Then Exit Sub
On Error Resume Next
Range(Target.Offset(1), Target.Offset(UBound(myarr) + 1)) = Application.WorksheetFunction.Transpose(myarr)
End Sub

this puts the names into the cells below the formula
put formual like =getgrades(C8:C17,"C") change range or grade to suit

[abhi51082]

Hi Westconn,

sorry mate but the code which you uploaded is not working..

please upload the file which you have made for writing this code and let me know how have you done the same..

abhi

[westconn1]

i never saved it, but as the code is here it should be easy enough to do again

edit: don't know why there is error in the code, as i did test it just change the code in the function, the rest stays the same

Function getgrades(r As Range, grade As String) As String
Dim res() As Variant
i = 0
For Each c In r
    If c.Offset(, 1) = grade Then ReDim Preserve res(i): res(i) = c: i = i + 1
Next
getgrades = ""
myarr = res
End Function

[abhi51082]

Hi Westconn,

sorry mate, once again the code which you uploaded is not working..

so only i am telling you to please upload the file which you have made for writing this code and let me know how have you done the same..

abhi

[westconn1]

here it is

[abhi51082]

Thanks mate...its working...