If value in column x and y is greater than 2 list all names

[Fazila]

I know I have asked this question previously and I really can't figure out a way to do this so thought I would try this amazing forum again

I need to populate the table below:

Last Name	First Name	Reg Group	Art Group	Art Teacher	Art Target	Art EFG	Art EFG-FFT	Art Effort	Art Homework

<tbody>
</tbody>

The base data is on a sheet called "Raw Data excel"

I need to copy the data for the above columns across if the Effort and Homework grade is greater than 2 (i.e. 3 or 4). This means I need to filter out 1, 2, U and blanks.

Is there a way this can be done?

I have tried

=IF(AND(INDEX(RawData,0,MATCH($J$1,RawDataHeadings,0)),INDEX(RawData,0,MATCH($K$1,RawDataHeadings,0)))=">2",INDEX(RawData,0,MATCH(A$3,RawDataHeadings,0)),"")

But no such luck

I do have a macro that copies all data across matching the above columns but I can't seem to add the filter in. The macro is:

Sub CopyStudent() 
Dim intErrCount As Integer
 
' create worksheet objects
Dim shtSource As Worksheet: Set shtSource = Sheets("Raw Data excel")
Dim shtTarget As Worksheet: Set shtTarget = ActiveSheet
 
' create range objects
Dim rngSourceHeaders As Range: Set rngSourceHeaders = shtSource.Range("2:2")
 
With shtTarget
    Dim rngTargetHeaders As Range: Set rngTargetHeaders = .Range("A" & ActiveCell.Row).Resize(, 10)
    Dim rngPastePoint As Range: Set rngPastePoint = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) 'Shoots up from the bottom of the sheet untill it bumps into something and steps one down
End With
 
Dim rngDataColumn As Range
 
' process data
      
Dim cl As Range, i As Integer
   
For Each cl In rngTargetHeaders ' loop through each cell in target header row
   
    ' identify source location
    i = 0 ' reset I
    On Error Resume Next ' ignore errors, these are where the value can't be found and will be tested later
        i = Application.Match(cl.Value, rngSourceHeaders, 0) 'Finds the matching column name
    On Error GoTo 0 ' switch error handling back off
   
    ' report if source location not found
    If i = 0 Then
        intErrCount = intErrCount + 1
        Debug.Print "unable to locate item [" & cl.Value & "] at " & cl.Address ' this reports to Immediate Window (Ctrl + G to view)
        GoTo nextCL
    End If
   
    ' create source data range object
    With rngSourceHeaders.Cells(1, i)
        Set rngDataColumn = Range(.Cells(2, 1), .Cells(1000000, 1).End(xlUp))
    End With
   
    ' pass to target range object
    cl.Offset(1, 0).Resize(rngDataColumn.Rows.Count, rngDataColumn.Columns.Count).Value = rngDataColumn.Value
   
nextCL:
Next cl
 
' confirm process completion and issue any warnings
If intErrCount = 0 Then
    MsgBox "process completed"
    
End If


End Sub

I wish I could claim credit for the above but it was something I found online and considering my woeful ignorance I can't seem to manipulate it very much.

Any help would be appreciated. Ideally I want something that would automate the whole process so if the base data changes it will automatically update the table above.

Thanks again for all your help.

Fazila

 

[KolGuyXcel]

Share the column headings of the "Raw Data excel" sheet. And data from which column in the "Raw Data excel" sheet is going to which column in the Output Sheet!

 

[Fluff]

@KolGuyXcel
Please do not quote whole posts, as it's unneccesary & makes threads harder to read.

 

[KolGuyXcel]

Okay, Fluff!

 

[Fazila]

There are quite a few columns but the main ones I need to look at:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

UPN

Last Name

First Name

Reg

Gender

Ethnicity

FSM

PP

PA

Eng Teacher

Eng Group

Eng FFT Target

Eng EFG

Eng EFG - FFT

Ma Teacher

Ma Group

Ma FFT

Ma EFG

Ma FFT - EFG

Eng Effort

Ma Effort

Eng Homework

Ma Homework

Eng Organisation

Ma Organisation

A1234

Smith

Dan

11.1

M

British

N

Y

Mid

Ms Patel

11a/En

4

5

1

Mr Singh

11a/Ma

6

5

-1

2

3

1

2

2

2

<tbody>
</tbody>

The columns I will need will depend on the department but will be:

A

B

C

D

E

F

G

H

I

J

K

L

M

Last Name

First Name

Reg

Eng Group

Eng Teacher

Eng FFT

Eng EFG

Eng EFG - FFT

Eng Effort

Eng Homework

Action CTL

Action PAL

Action Teacher

<tbody>
</tbody>

Any help would be really really appreciated.

Thanks

 

[Fazila]

bumping this up any help would be greatly appreciated!

Thanks