Need Simple List...Multiple Columns and Multiple Worksheets

[Jane Doe]

I have 3 worksheets (different resources) which all contain 2 to 4 columns
listing the grade level that each of that households child attends.

Example Columns: Name, Address, email, Grade (this may have 2-4 columns
depending on how many children, one may be in pre-k, one in 2nd and so on)

Name, Address, Grade (this could be 2-4 columns)
Jones, 777 Oates, (column 1) -pk, (column 2)- 3rd, (column3)- 6th

I need to pull from these worksheets by grade. A family may appear in more
than one grade list if they have more than one child. What is the best way to
pull this data?

I tried to filter the columns but when I do it will only give me one column
at a time in each worksheet. When I pull from the list say I want all 6th
graders it will only give me those in the 3rd column but not column 1 and 2.
What am I doing wrong?

 

[Otto Moehrbach]

Jane
You're not doing anything wrong. The filter is built to work in only
one column. Having filtered the data in one column by grade, you can then
filter another column by another grade, but the second filter will work with
only the data that is visible from the first filter. So, basically, you
can't get there from here by filtering.
The only way I know that would work for you involves VBA (programming).
This might present a problem for you because, in order for VBA to find, say
all 3rd grade entries, all those entries would have to be exactly the same.
If your data fits this criteria, VBA would work just fine and would do what
you want in only seconds.
If this sounds like what you want, post back with some more detail about the
layout of your data. For instance, what is the maximum number of columns
that you have? Also, when VBA finds each instance of the criteria grade,
VBA will have to copy some/all of that row and place it somewhere. What do
you want copied? Where do you want this data to be placed? Perhaps in
another, maybe blank, sheet? Also, you say you have 3 worksheets. Do you
want VBA to search all 3 sheets for the criteria grade? If so, do you want
the result (the copied rows) separated by sheet or all bunched together?
Also, do you want the result sorted in some manner? What manner? HTH Otto

 

[Jane Doe]

Jane
You're not doing anything wrong. The filter is built to work in only
one column. Having filtered the data in one column by grade, you can then
filter another column by another grade, but the second filter will work with
only the data that is visible from the first filter. So, basically, you
can't get there from here by filtering.
The only way I know that would work for you involves VBA (programming).
This might present a problem for you because, in order for VBA to find, say
all 3rd grade entries, all those entries would have to be exactly the same.
If your data fits this criteria, VBA would work just fine and would do what
you want in only seconds.
If this sounds like what you want, post back with some more detail about the
layout of your data. For instance, what is the maximum number of columns
that you have? Also, when VBA finds each instance of the criteria grade,
VBA will have to copy some/all of that row and place it somewhere. What do
you want copied? Where do you want this data to be placed? Perhaps in
another, maybe blank, sheet? Also, you say you have 3 worksheets. Do you
want VBA to search all 3 sheets for the criteria grade? If so, do you want
the result (the copied rows) separated by sheet or all bunched together?
Also, do you want the result sorted in some manner? What manner? HTH Otto

 

[Jane Doe]

You're not doing anything wrong. The filter is built to work in only

Listing all information in another worksheet pulling from all sources (3
worksheets in same workbook) is fine. I would want to group by grade of
course. Could do sheet for each grade? Will probably begin sorting
alphabetical, but may need to change that later. First priority is to list
row information by grade. I'm aware that some households will appear in more
than one grade goup.

Thank you for your help...greatly appreciated!

 

[Jane Doe]

Sorry Otto,

Not a veteran at posting in forums. Expected that when I replied it would
seperate our text.

Answer to your response questions...

The maximum number of column in any one spreadsheet is 11. The columns are
labled : Parents First Name, Parents Last Name, sStreet Address, City, State,
Zip, columns 7-10 list grade for each household child "Child1" = k,
Child2=pre-k, Child3=6, Child 4=5 (only goes up to 4 children and no grade
greater than 8), column 11 is email address.

Listing all information in another worksheet pulling from all sources (3
worksheets in same workbook) is fine. I would want to group by grade of
course. Could do sheet for each grade? Will probably begin sorting
alphabetical, but may need to change that later. First priority is to list
row information by grade. I'm aware that some households will appear in more
than one grade goup.

Thank you for your help...greatly appreciated!

 

[Jane Doe]

Otto,

Thought I might mention that if there are only 2 children, there will be 2
columns blank...child1=pk, child2=7, child3=blank, child4=blank.

 

[Otto Moehrbach]

Jane

Here is a series of macros that do what you want. I made the
following assumptions:

Your original 3 sheets are named: "Sheet One", "Sheet Two", and "Sheet
Three", without the quotes.

Your destination sheets are named: "Grade pre-k", "Grade k", "Grade 1",
"Grade 2". Etc. to "Grade 8", again without the quotes.

I included code in the macros to handle the situation wherein one family has
twins or triplets or whatever (multiple children in the same grade).

This code may be more than you can handle, so, if you wish, send me an email
and I'll send you the small file I used to develop the code. That file will
have the code properly placed. My email address is
(e-mail address removed). Remove the "extra" from this address. HTH
Otto

Option Explicit
Dim ws As Worksheet, Grade As Variant, DestSht As String
Dim rColA As Range, i As Range, Dest As Range
Dim NumTwins As Long



Sub ArrangByGrade()
Application.ScreenUpdating = False
Call ClearGradeShts
Call FilterData
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub



Private Sub ClearGradeShts() 'Clears all data in all grade sheets
For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name, 5) = "Grade" Then
With ws
If Not IsEmpty(.Range("A2")) Then _
.Range("A2", .Range("A" &
Rows.Count).End(xlUp)).ClearContents
End With
End If
Next ws
End Sub



Private Sub FilterData()
For Each Grade In Array("pre-k", "k", "1", "2", "3", "4", "5", "6",
"7", "8")
If IsNumeric(Grade) Then Grade = CInt(Grade)
Call GetDestSht
Set Dest = Sheets(DestSht).Range("A2")
For Each ws In Sheets(Array("Sheet One", "Sheet Two", "Sheet
Three"))
With ws
Set rColA = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
End With
For Each i In rColA
If Application.CountIf(i.Offset(, 6).Resize(, 4),
Grade) > 0 Then
NumTwins = Application.CountIf(i.Offset(,
6).Resize(, 4), Grade)
i.Resize(, 6).Copy 'copy first 6 columns
Dest.Resize(NumTwins).PasteSpecial
xlPasteValues
i.Offset(, 10).Copy 'copy 11th column
Dest.Offset(, 6).PasteSpecial xlPasteValues
Set Dest =
Dest.End(xlUp).End(xlDown).Offset(1)
End If
Next i
Next ws
Next Grade
End Sub



Private Sub GetDestSht()
Select Case Grade
Case "pre-k": DestSht = "Grade pre-k"
Case "k": DestSht = "Grade k"
Case "1": DestSht = "Grade 1"
Case "2": DestSht = "Grade 2"
Case "3": DestSht = "Grade 3"
Case "4": DestSht = "Grade 4"
Case "5": DestSht = "Grade 5"
Case "6": DestSht = "Grade 6"
Case "7": DestSht = "Grade 7"
Case "8": DestSht = "Grade 8"
End Select
End Sub