How Filter Macros Work

Hi,
I have been trying to figure out a formula that will allow me to count people in my data set by age, gender, and ethnicity. does anyone know what kind of formula i use and how to go about writing it? For instance, I want to know how many people (out of 679) are 15y/o, Female, and Hispanic. Thank you!

I have a file which is generated in a very poor format. I need to convert this data into a table and i am not quite sure where to start. here is what the data looks like.

Point System Name: C.M.L1_DIS_STAT.PMSE001C Point Name: C.M.L1_DIS_STAT.PMSE001C Point Type: LAI Supervised: Yes Revision Number: 2 Classification: Building Automation Descriptor: L1 DIS STAT Panel Name: MEC05 -5 Point Address: FLN: 0 Drop: 00 Point: 19 Sensor Type: Current Slope: 0.000651 Intercept: 0 COV Limit: 1 Wire Resistance: 0 Engineering Units: Analog Representation: Float # of decimal places: 0 Totalization: None Initial Priority: NONE Enabled for RENO: No Alarm Issue Management: No Graphic Name: Informational Text: > Alarm Type: Not Alarmable

This data set is repeated multiple times on the same worksheet and is seperated by two blank rows b/w each set. I need to convert this entire worksheet into columns of all the parametes listed with a ":" at the end as the column header. and should look like this.

Point System Name: Point Name: Point Type: Supervised: Revision Number: Classification: Descriptor: Panel Name: Point Address: Sensor Type: Slope: COV Limit: Engineering Units: Analog Representation: # of decimal places: Totalization: Enabled for RENO: Alarm Issue Management: Graphic Name: Informational Text: Alarm Type: C.M.L1_DIS_STAT.PMSE001C C.M.L1_DIS_STAT.PMSE001C LAI Yes 2 Building Automation L1 DIS STAT MEC05 FLN: 0 Drop: 00 Point: 19 Current 0.000651 1 Float 0 None No No > Not Alarmable

Any help would be appreciated.

Hello

I got a spreadsheet with about 10 different columns. Is there a way to filter the entire table so that it will show rows with the word "workbook" in any of the 10 columns?

Thanks,
Sam

I've got a table that looks something like this:

GL Code 1 GL Code 2 GL Code 3
Dec 10,000 15,000 23,000
Dec 22,000 13,000 15,000
Dec 60,000 32,000 55,000
Jan 50,000 27,000 66,000
Jan 68,500 47,000 88,000
Jan 77,650 55,000 66,500
Feb 14,000 47,350 28,000
Feb 17,000 48,010 64,000
Feb 65,000 92,000 31,000

And I need to get it into a format like this:

Dec Jan Feb
GL Code 1 92,000 196,150 96,000
GL Code 2 60,000 129,000 187,360
GL Code 3 93,000 220,500 123,000

I don't want to use a pivot table, and sumifs won't work because I need to sum multiple rows and columns. I've tried to use sumproduct, but I don't have that much experience with this formula, and I can't get it to work. Help!

Have table of data

2001 2002 2003
Name # Name # Name #
A 23 A 33 A 29
B 20 B 22 B 29
C 15 D 36 C 12
D 33 E 20 E 48
G 19 F 12 F 19


Each year's data includes a name (A, B, C, etc.) and a number.
However, not all years include all names. How can I match up names in
each row so all "A's" appear in one row, "B's" in one row
etc. along with their corresponding number.

Desired Outcome is:

2001 2002 2003
Name # Name # Name #
A 23 A 33 A 29
B 20 B 22 B 29
C 15 C 12
D 33 D 36
E 20 E 48
F 12 F 19
G 19

or the ideal outcome would be:

2001 2002 2003
Name # Name # Name #
A 23 A 33 A 29
B 20 B 22 B 29
C 15 C 12
D 33 D 36
E E 20 E 48
F F 12 F 19
G 19

I have tried to align these numbers in columns but it is not WYSIWYG.
If this does not make sense, I can email an actual excel table with the
data to better explain or detail the problem.

Thank you.

I figured this would apply to programming - because excel doesnt offer this natively.

Multiple charts over multiple sets of data over multiple worksheets.

This may be a toughie

I have a workbook with about 20 work sheets, I am doing statistical analysis of baseball players.

each chart is roughly like this:

Code:

nameLast     nameFirst    year    team    G    AB    R    H      2B    3B    HR    RBI
Kikowski     Rex         1998    MOB    128    299   53    84    28     1    17    67
Kikowski     Rex         1999    MOB    117    303   32    78    35     1    22    87
Kikowski     Rex         2000    TOU    150    506   83    127   38     1    12    99
Kikowski     Rex         2001    TOU    147    553   55    136   57     4    28    100
Kikowski     Rex         2002    TOU    163    591   98    155   34     2    34    109
Kikowski     Rex         2003    SB    131    499    76    166   47     4    42    125
Kikowski     Rex         2004    SB    155    597    87    175   44     1    57    125
Kikowski     Rex         2005    SB    150    505    96    158   56     1    35    138
Kikowski     Rex         2006    SB    157    573    99    143   47     1    24    108

I want to create *separate* charts for each individual stat - the numbers are too far apart to put them on ONE graph the scales would be nuts (3B's range is 1-4 and AB's range goes into the 500's).. they'd all have the same X axis (the year) and scale the Y axis to the stat.

would there be an easy way to do this instead of manually creating each one?

(this would be for multiple players, so I'd be doing this process over and over)

This is from tutorial, I would like to know whats wrong with this line .
oWS.UsedRange.AutoFilter(Field:=2, Criteria1:= "Apple" ,






Sub AutoFilter_WithMultiple_Criteria()

Dim oWS As Worksheet

On Error GoTo Err_Filter

oWS = ActiveSheet

oWS.UsedRange.AutoFilter(Field:=2, Criteria1:= "Apple" , Operator:=XlAutoFilterOperator.xlOr, Criteria2:= "Orange" )

Finally :

If Not oWS Is Nothing Then oWS = Nothing

Err_Filter:
If Err 0 Then
MsgBox(Err.Description)
Err.Clear()
GoTo Finally
End If
End Sub

I have a macro that does an initial autofilter of my data based on a specified date range in column 1. I need to do a second sort and deletion of rows that have dates greater than my end date in column 2 and where column 3 equals --, New, Imported, Assigned or Error. Seems simple enough, but I'm stuck and haven't been able to get this to work.

here's what I have:

Code:

'first sort

Date1 = Range("C2").Value
Date2 = Range("C5").Value

'Filter data based on dates chosen
Windows("DownLoadReports.xls").Activate
Sheets("DownLoadReports").Select
    LR = Range("A" & Rows.Count).End(xlUp).Row

    Range("A2").AutoFilter Field:=7, Criteria1:=">=" & Date1, Operator:=xlAnd, _
        Criteria2:="<=" & Date2
'Copy data ranges and remove autofilter
    Range("A2:CE" & LR).SpecialCells(xlCellTypeVisible).Copy Sheets("QuarterlyLocks").Range("A2")
      
Sheets("QuarterlyLocks").Select
   Columns("A:CE").Columns.AutoFit
    Range("g2") = Format(Date1, "M/D/YYYY")
    Range("g2") = Format(Date2, "M/D/YYYY")
    Range("A2").Select

'second sort not working

With ActiveSheet
  .Cells.AutoFilter Field:=9, Criteria1:=">" & Date2
  .Cells.AutoFilter Field:=82, Criteria1:="=--", Operator:=xlOr, Criteria2:="=New", _
   Operator:=xlOr, Criteria3:="Imported", Operator:=xlOr, Criteria4:="=U/W Assigned"
  
  

 Set DeleteRange = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
  
  DeleteRange.EntireRow.Delete
End With

Hi,

I'm looking to make multiple lines from multiple data sets so I can see them lay over each other.

I can also have the different data sets in the same worksheet to pull from, in fact that's easier.

What I'm trying to do is calculate my hourly spend over a 24 hour period, but have a different line stretching over a 24 period... for each day

Does that make sense?

Cheers

Column A contains names
Column B contains date joined
Column C contains date left

The search criteria area is in b1:c2
The data starts in a5 (headings)

Quirks: not all joining dates are known. Most are empty.

My filter question in English is the following: "I want to see every one who joined before 30/04/2008 and had not left before 30/04/2008.

The problem is in the "had not". It is easy to do a filter for the date joined e.g. =or(b6="",b6<=30/04/2008)

How do I get the "left" filter to work? I want to see all of the emtpys therefore c6="" and those that left after 30/04/2008 but not those that left before.

PLEASE HELP!!!
Txs, il ballerino carino