| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Question, definite Help - Please?
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup Question, definite Help - Please?
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Champ" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup Question, definite Help - Please?
Then goto the vba and unbreak it by clicking on the blue reset square. Perhaps workbook size does have something to do with it although it should only be limited by computer memory. I would NEVER design a wb that big. You are aware that xl2007 has virtually unlimited rows. What is the TSI column with the hyphen. I don't know what is going on there. Here is a sample workbook that does work and tries to do the TSI column also. Or, you may send me a sample workbook, with only a FEW sheets, to look at. Where are you and what is this for. BTW, I am available as a paid consultant/developer. -- Don Guillett Microsoft MVP Excel SalesAid Software "Champ" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Champ" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup Question, definite Help - Please?
This message was NOT intended for the group. I NEVER attach files.
So sorry. -- Don Guillett Microsoft MVP Excel SalesAid Software "Champ" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Champ" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help Please VLOOKUP question | Excel Discussion (Misc queries) | |||
| VLOOKUP Question | Excel Worksheet Functions | |||
| Vlookup with IF question | Excel Worksheet Functions | |||
| vlookup question | Excel Worksheet Functions | |||
| Vlookup question | Excel Discussion (Misc queries) | |||
Linear Mode
