Subtotals Cause Hang With Large Data
Jun 10, 2008
I have a worksheet that uses Excel 2003's automatic subtotalling feature witSelectihin one of my macros. It works fine on my sample data, but when I try to run it on the larger set of customer data it hangs.
Any suggestions? I've been up all night, maybe this is just something really dumb, but I don't know. I stepped thru the macros, and it is hanging when I press f8 for the line that inserts the subtotals.
According to the task manager it really is hung, not just slow. Waited over 2 hours.
The line of code where it is hanging is:
Selection.subtotal groupby:=3, function:=xlsum, totallist:=array(14, 15, 16, 17, 18, 19, 20, 21, 22), Replace:=true, Pagebreaks:=false, Summarybelowdata:=true
I am a total newbie, so if there is a painfully obvious reason for why this is happening, don't think I'll be upset if you point out how dumb I am. Speaking of dumb, if this issue is posted elsewhere I apologize, I searched for hours before asking, but I may have looked in the wrong place.
View 9 Replies
ADVERTISEMENT
Mar 7, 2014
my attached spreadsheet as I cant seem to get my head around VLookup. What I would like is to fill in the contractor's job titles and type of shift which will then make their rate of pay appear.
View 6 Replies
View Related
Apr 25, 2014
I had this old code which runs very slowly, it's a Sub that's called to clear autofilters and erase data from multiple sheets in the same workbook. It used to look like this:
VB:
Private Sub ClearOldData()
'
' ClearOldData Macro[code]....
View 5 Replies
View Related
Apr 24, 2014
I have sorted my data by three layers. First by Budget Center, then Invoice, and then Account. I am having trouble writing a formula that will total the amounts by account with respect to its invoice and budget center.
excel forum2.xlsx
View 4 Replies
View Related
Jun 25, 2009
I have a table of data input, which I would like to be converted to a certain format. Please see the attached file.
The output is derived as follows:
Field2 Sum of amount1 Sum of amount2
Left(Field1,2) under the above Field2 Sum of amount1 Sum of amount2
Field1 (whose left,2 is the same as above) Sum of amount1 Sum of amount2
for each of the Field2 Field1 combination.
So is it kinda like pivot table with each row field having subtotals, except that the row fields are on the same column, and the subtotal is place on top.
View 9 Replies
View Related
Sep 23, 2009
I have a worksheet that I'm trying to rank program selling. The range has subtotals that I need to exclude, so I'm trying to rank over multiple ranges. I've thought about LARGE in an array function, but can't seem to make that work. Is SUMPRODUCT a possibility? I've attached the worksheet, trying to get the top programs (again excluding the subtotals in bold).
View 4 Replies
View Related
Sep 17, 2008
I can't seem to get the hang of advanced filters with multiple criteria. BTW, I have no problem solving for a single criteria. I've found many instructions on how to do it, but zero specific syntax examples, and I'm obviously doing it wrong. For example: ....
View 9 Replies
View Related
May 25, 2007
I have created a workbook for a quality check and it has about 50+ worksheets in the workbook, which contains data, formula,graphic & vba function. The size of the file is about 4890kb. It's works as planned, except, it takes about 1 mins ++ to load, save & even exit. Is there any suggestion to eliminate this issue, apart from creating a few workbook to accomodate the number of sheets/item in the project?
View 2 Replies
View Related
Jul 29, 2014
I am trying to create a Dashboard in excel (2010) using tables/pivot tables to build it. The data I am bringing into excel has these key fields of data: cost center+cost center description, general ledger account+general ledger account description, and YTD amount.
My problem is the data is from an external source report and the report has subtotals built in at cost center, and the report's format of subtotaling puts the cost center first and then the general ledger accounts below. There is no
formula value in the cell that has the subtotaled amount and the number of general ledger accounts can vary depending on whether there has been general ledger activity.
I want to take this format:
July YTD
Cost Ctr 1050 XYZ$6.00
625110 Supplies$2.00
650150 Postage$2.00
650550 Fees$2.00
Cost Ctr 1052 ZZZ$4.00
670500 Pens$2.00
and have it look like this
Cost CenterCost Center DescrGL AcctGL Acct descYTD Amt
1050 XYZ 625110 Supplies $2.00
1050 XYZ 650150 Postage $2.00
1050 XYZ 650550 Fees $2.00
1052 ZZZ 679200 Pens $2.00
Besides manually doing data moves and assigning a unique sort sequence number to keep the records together, how else can I quickly move my cost centers to a new column and keep the cost center with the gl account and $amount?
View 3 Replies
View Related
Aug 14, 2014
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
View 14 Replies
View Related
Jan 20, 2009
I have a one column spreadsheet. The column contains this data:
1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
View 9 Replies
View Related
Aug 6, 2003
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
View 9 Replies
View Related
Jun 24, 2013
I have two forms...clicking a specific button on the first form should display the second form which works fine.
VB:
Private Sub Image_Employees_Click()
Form_Main.Hide
Form_Employees.Show
[Code].....
When I do this twice, however, the second form seems to freeze/hang and I have to close Excel and then re-open it. I feel like I'm overlooking something obvious...
View 1 Replies
View Related
Mar 8, 2009
Need to produce a formula to reference a large amount of data and pull one month at a time:
View 7 Replies
View Related
Jan 14, 2014
I have a list of IDs that recurs over a time period. It consists of a Start Data an ID Number. These IDs recur over and over again through one month with different start dates.
See Below:
Date ID
12/1/2013 10:00:00 AM 67890
12/6/2013 12:00:30 PM 67890
12/18/2013 06:30:05 AM 67890
From Another List I'd like to pull a max enddate that is within 24 hrs of the start date. There will be multiple end dates. Here is what the other list would look like.
End Date ID
12/1/2013 1:00:30PM 67890
12/6/2013 4:00:45PM 67890
12/18/2013 9:30:00 AM 67890
Seems like using vlookup with the ID as the lookup wouldnt work because it would just pull the first date it found over and over again.
View 9 Replies
View Related
May 29, 2014
I have a very large spreadsheet that was exported from an ecommerce site with close to 1000 products. I have one column that I need to extract some text from. This column holds all of the html from the product description and is huge. I only need to extract the actual description of the product, but am having a very hard time figuring out how to do it. I've tried using the mid, left, and right function; but not all of the html is the same so it's not really working the way I need it to.
I have multiple tags throughout the html that I can use with the mid function, but there is more than one occurrence of them. So, how can I tell it to start at the 4th occurrence? I've spent countless hours searching, but I'm a complete novice when it comes to excel and I don't even know what to search for. I end up looking through sites that explain how to pull the Y out of XYZ, which is what I need, just on a much larger (and more complicated) scale.
It was suggested that I set up a macro that will find the 4th occurrence of the word, and then uses the mid function to pull the data out, but when I try to find the word, it says it doesn't exist even though I can see it right in front of me.
View 10 Replies
View Related
May 29, 2014
I have enormous amounts of data to sort through (as we all do) and I am trying to link them to unique identifiers for easier data maintenance later.
What I want to do is vlookup from the 'solved' sheet a code, which will search the 'data' table and return the 'DFU' code.
Example 2905.xlsx
View 5 Replies
View Related
Nov 11, 2009
I need to find the most frequent number for each name in a large datatable, eg. Name1=12.
ColumnA / ColumnB
Name1 / 12
Name1 / 11
Name1 / 12
Name2 / 100
Name2 / 105
Name2 / 105
Name2 / 98
Name3 / 14
Name3 / 14
Name3 / 2
NameX / ..
Since Pivot tabels cant do the trick as far as I know, i'm clueless of how to proceed.
View 10 Replies
View Related
Dec 3, 2013
I have a large list of in rows with multiple column headings: ie. Name, date, amount. I need to generate a separate list whereby the rows that correspond to a given criteria are extracted and listed sequentially.
I have attached a simple example to demonsrate what i mean.
in the attached spreadsheet, i have a column of colours, and a column of objects- RED book, BLUE hat, etc.
I'd like the criteria that i type in the green cell (a choice of colour), to generate a list of all the items that match that colour, and be displayed in the orange cells. So, if the green cell says BLUE, the list should pick out the BLUE dog, BLUE hat etc.
View 1 Replies
View Related
Jun 20, 2014
I have a large data (more than 50k rows) in a spreadsheet and i want to consolidate the information's.
here is the sample source data.
Source Data
codeamt1amt2amt3
123456$16.00 $0.00 $0.00
789011$0.00 $18.00 $0.00
123456$12.00 $5.00 $0.00
123456$0.00 $0.00 $7.00
111111$11.00 $1.00 $3.00
789011$22.00 $0.00 $0.00
and the output should be.
Output
codeamt1amt2amt3
123456$28.00 $5.00 $7.00
789011$22.00 $18.00 $0.00
111111$11.00 $1.00 $3.00
currently i'm using the consolidate function of excel inside my vba (which is working fine) but i took forever before it finished, i'm just wondering if there is an alternative way which is more faster.
View 8 Replies
View Related
Nov 21, 2011
I have a large amount of data, 97000 rows and 4 columns that I need to sort every 26 rows starting with row 26. I only need 2 columns but I'll take all four, any easy way to accomplish this task. The data is position and time and the time is in seconds from which I need the 0,.5,1,1.5... all the way to 12577 seconds.
View 1 Replies
View Related
Apr 20, 2014
I am facing problem to make summary sheet of top 3 value from large number of value of my excel sheet like-
Sheet1
A
Group
B
Name
C
Value
1
Nashid
200145
[Code] ......
Now I want to make summary in sheet2 top 5 value among the list of sheet1 separately from group 1, 2 & 3.
If group number is 1 in sheet1 then top 3 value will be like this-
Group1
Name
Value
Nashid
200145
Jabed
184198
Eman
156622
If group number is 2 in sheet1 then top 3 value will be like this-
Group2
Name
Value
Rubel
172569
Hasin
152304
Monem
104463
If group number is 3 in sheet1 then top 3 value will be like this-
Name
Value
Romel
168251
Nasim
136357
Hasan
120410
View 1 Replies
View Related
Apr 20, 2007
I have a large list of data in sheet1. The relevant columns contain a unique identifier (column 'N') and a value of either "True" or "False" in column O. Because I don't want users to have to trawl through hundreds of lines looking for the 'False' entries, I have a macro that copies only those records to Sheet3. In this sheet the user can set the 'False' value to 'true' but I don't know how to return the new value to Sheet2 (possibly for multiple records).
View 9 Replies
View Related
Dec 15, 2007
I will try to keep this as short as possible. I have a huge amount of stringed data from dry kiln runs all well organized. I want to make something that will look at all this data (or at lease the ones that I specify) and give me a prediction of what would happen if I was to make a change somewhere in the schedule. I have alot more info if needed.
View 10 Replies
View Related
Oct 25, 2009
I have a large amount of data that I need to be able to summarize certain fields in order to be able to work with the data.
What I have is multiple lines which all pertain to one record and I need to be able to summarize these into one line - which would give me a sum in certain columns. I am working with thousands of lines of data.
There is about 10 columns in the worksheet. I want to be able to tell Excel that if certain columns all match, then to summarize these lines and give me a sum total on a couple of other columns. The data I am working with is invoice data, so I want to know if certain columns - ie - Vendor name, invoice number, cheque number are the same in each row, then to sum these records and provide a total for the invoice amount and cheque amount columns.
This sounds like a complex function to me but I am hoping that someone has a solution for me.
View 10 Replies
View Related
Jan 21, 2010
I want to use the attached Test.xls file below and do a lookup from the Data.csv (see zip file) where "Empl ID" (column A in Test.xls) matches "ID" (column A in Data.csv). I want to bring back "SupvID" (column B in Data.csv) into column D in Test.xls.
The problem I have is that in real life Data.csv is a very large file and contains more than Excel's maximum number of rows. I want to automate this using VBA to lookup the values without opening the csv file.
I have looked at a number of forums for answers and I can see where using an ADO connection will probably help accomplish this, but I can't seem to get it to work on these specific files.
View 4 Replies
View Related
Aug 1, 2006
I have a large amount of data, in columnA a list of cars, columnB a list of engines, in columnC the city built, in columnD the country to ship, in columnE distance, in columnF the time.
Now some makes, city's and destinations match and sometimes the engine, but i would like to be able to say cellX = (time to ship) where cellA = Ford AND cellB = 1.8 AND cellC = London and CellD = Ireland
The key data is the city built and shipping destination, so i'd like to say if cell A3 = London AND Cell B3 = Ireland AND Cell A5 = Ford AND CellA7 = 1.8 then CellA10 = (distance) & CellA11 = (time)
Vlookup's are of no use due to the large amount of data. But is there a way of writting this command in VB?? or am i missing something in Excel?
View 6 Replies
View Related
May 25, 2007
See attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.
Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?
View 2 Replies
View Related
Jan 9, 2008
I have some spreadsheets one worksheet little data and it saved as it a 5MB file when it should only save as something like 500Kb, what would cause this?
View 3 Replies
View Related
Jan 1, 2009
want to be able to take a large quantity of data, sort all the like data together, and then quantify the number of each like data. I need the equations to do that.
View 11 Replies
View Related