VLookup Question, definite Help - Please?

[Champ]

I have multiple pages that have 65,536 rows, same monsterous spreadsheet,
138-MEG and I need to lookup data from each and have a summary sheet accept
the returned answers: (Please read to the optional method question of
gathering this info.)

Fleet Removal Date Install Date TSI CSI
BMW 06/10/2008 06/10/2005 100:03 189
Ford 06/10/2008 06/10/2003 200:03 289
Chev 06/10/2008 06/10/2002 340:03 389
Olds 06/10/2008 06/10/2006 180:03 129
Suzu 06/10/2008 06/10/2007 50:03 89
Hond 06/10/2008 06/10/2001 1,100:03 1189
BMW 06/10/2008 06/10/2005 100:03 189
Ford 06/10/2008 06/10/2003 200:03 289
Chev 06/10/2008 06/10/2002 340:03 389
Olds 06/10/2008 06/10/2006 180:03 129
Suzu 06/10/2008 06/10/2007 50:03 89
Hond 06/10/2008 06/10/2001 1,100:03 1189

I need the return vlookup to get me all of the BMWs, then the Fords, etc.,
and just give me an end result of a count of how many of each, but I need the
TSI and CSI fields to also add up and then average that by the like units:

Fleet TSI CSI
76 BMW 7100:03 5,189
81 Ford 3200:03 3,289
66 Chev 4340:03 389
42 Olds 2180:03 429
21 Suzu 550:03 689
91 Hond 21,100:03 18,189

OR....(optional method of gathering info.)

Can I have the entire row/line come into another spreadsheet, by car type,
instead of doing all of the above?

ex:

BMW 06/10/2008 06/10/2005 100:03 189
BMW 06/10/2008 06/10/2002 500:13 489
BMW 06/10/2008 06/10/2005 100:23 189
BMW 06/10/2008 06/10/2001 448:03 389
BMW 06/10/2008 06/10/2007 100:03 189
BMW 06/10/2008 06/10/2004 225:53 389

Can this be done?

Thanks,
Champ

 

[Don Guillett]

In your summary sheet make a list of the car names starting at cell a2. Copy
this macro and run it. If very large you may want to suspend screen updating
and calculation.

Sub sumemeup()
lr = Sheets("summary").Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Sheets("Summary").Range("a2:a" & lr)
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Summary" Then
ms = ms + Application. _
SUMIF(ws.Columns("a"), c, ws.Columns("e:e"))
End If
Next ws
c.Offset(, 1) = ms
Next c
End Sub

 

[Champ]

Thanks Don,

I keep getting an error:

Can't execute in break mode.

I have listed the cars in a sheet, within this ginormous spreadsheet, from
cells A2 through A11.
I want this to look into all 220 sheets/tabs, within this one spreadsheet.
Is that going to freeze the spreadsheet up?
It is 135-Meg large.

Thanks again for your help.
Champ

 

[Don Guillett]

This message was NOT intended for the group. I NEVER attach files.
So sorry.