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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Reg Group[/TD]
[TD]Art Group[/TD]
[TD]Art Teacher[/TD]
[TD]Art Target[/TD]
[TD]Art EFG[/TD]
[TD]Art EFG-FFT[/TD]
[TD]Art Effort[/TD]
[TD]Art Homework[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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:
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
I need to populate the table below:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Reg Group[/TD]
[TD]Art Group[/TD]
[TD]Art Teacher[/TD]
[TD]Art Target[/TD]
[TD]Art EFG[/TD]
[TD]Art EFG-FFT[/TD]
[TD]Art Effort[/TD]
[TD]Art Homework[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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:
Code:
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 SubI 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
Last edited:
