File That Is 50mb And I Don't Know Why It Is That Huge

Jan 14, 2005

I have an excel file that is 50mb and I don't know why it is that huge. I did not create this file and was asked to look at it and figure out why it takes 10 minutes to open?

There is 3 tabs and very little data. How can it be so huge?

View 9 Replies


ADVERTISEMENT

Huge Data File Delete All Rows With X Value?

Jul 21, 2014

I have a huge data file. I would like to have excel automatically delete all rows with the value of 7 in column b. Can I do this without manually selecting all of the rows (I can sort by that value, but there are 120,000 rows).

View 6 Replies View Related

With Sheet Background, File Size Is Huge

Apr 1, 2007

Is there anyway to put a background on a worksheet, without the file size of the workbook becoming monstrously huge? I have a small jpg file of our company logo, and I think that it would be a nice touch for it to be the background to our spreadsheet. But when I use this 50k file as the background, the workbook suddenly grows to a size of 7MB.

View 4 Replies View Related

Find And Replace On Some Duplicates In Huge Data File

Mar 6, 2014

I have a file with 300k lines of SKU data. This is data by month for 12 months (so roughly 25K SKUs per month, with changes in SKUs each month.) I take this data and pivot it and drop it into a file.

I noticed last month when I prepared that the SKUs have names next to them, and sometimes the information is pull by the name instead of the SKU. Unfortunately, in the master data, someone entered different names in a few of the months. So for example, in some months SKU 0012 is Green B and in other months it's Green Beans. I need the names to be the same.

Is there a quick way to fix this given that I'll have 12 records and maybe 3 will be one name and 9 will be there other? Finding and replacing every one is a chore because on pivot there are 40k. I don't think I can do a quick remove duplicates because it would remove multiple months. I just want each SKU to have the same name.

View 3 Replies View Related

Selecting And Then Importing Data From Huge Tab Text File To Excel

Apr 11, 2012

I wanted to see if there is a VBA code to do the following :

a) Select a TabText Delimited file based on a criteria
b) Import the Selected Data to Excel

I have the vba code where I can open the tab text delimited file in excel, use a selection criteria and then copy the data into excel. But I am having problems with the case where the Tab Text Delimited file exceeds the row limit that excel currently has and wanted to see if the data import can be done without opening the text file into excel at all.

Here is my current query :

Sub TEST()
' TEST Macro

Workbooks.OpenText Filename:="E:Testdata.txt"
ActiveSheet.Range("$A$1:$B$1048576").AutoFilter Field:=1, Criteria1:="1234"
Range(Selection, Selection.End(xlToRight)).Select

[Code] .......

View 3 Replies View Related

How To Go End Of Huge Data Range

Mar 31, 2014

use CTRL+ Navigation Arrow Key on keyboard i.e. if you wanna go to the last Non-Blank row in a column press CTRL+Down Arrow key if your range contains empty cells then use this shortcut SHIFT+CTRL+Arrow key

if you wanna Get the Value of cell in a column you can use this formula

=INDIRECT("A"&COUNTA(A:A))

This formula will work only if there is no Blank Cell in the range.

View 1 Replies View Related

How Do I Create A HUGE Table?

Oct 26, 2008

I have to create a 12 row 14 column table which will be printed out and put onto a (around) 20 by 20 poster board...so it has to be HUGEEEEEE. How do I do this, all the boxes inside the table I want to have control of meaning size and stuff. I will have to write in all boxes.

P.S: Yes. I know you guys prolly think im a idiot but this is my first time and my due date is appraoching fast, if anyone could help that would be great.

View 7 Replies View Related

HUGE List Filtering

Sep 19, 2008

I'm working out some vba script, that could filter up to 300,000 rows long list.

here is sample from that list:

1000999999027FRESH PASTA FETTUCINE WITH TOM102002P N0809100000035000000012810000000+0000000+ 0000000000000000+0203001896409
2000999999027020300189640900000000000005740200070063
1000999999027FRESH PASTA FETTUCINE WITH SPI102002P N0809100000025000000015700000100+0000000+ 0000000000000000+0203001896454
20009999990270203001896454000000000000057010180265125740200070070

As you can see, the rows are starting with 1000 and 2000. And that would be the criteria.

I'm importing the loooooong list file onto sheet1, and i'd like to sort the list so it puts all the codes starting with 1000 onto sheet2 and rows starting with 2000 onto sheet 3.
With regular filtering mode it's impossible and keeps giving errors.
I know it's possible, but i can't figure it out anymore.

I was thinking about to make script that would copy one row from sheet to sheet and loop it. But my mind just crashes doing that.

View 9 Replies View Related

Best Method To Sort Huge Sheet

Aug 7, 2012

Is the a more efficient way of sorting a huge sheet (One Column only)? Currently, I use the below code and sometimes it throws an error (due to insufficient memory/ cannot undo this step)

VB:
Sub sort1()
Rows("1:1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort.SortFields.Clear

[Code] .....

View 3 Replies View Related

Working With Huge Data But Getting Hanged

Apr 27, 2009

When i'm working with huge data on excel say for exam 50000 records and about 25 to 30 columns, everytime i use any formaula and while saving it the file is not responding, what ever the formula like vlookup, countif, converting in values applying filters and removing filters and with that whatever the changes i do that is not seen next time when i open file.

View 9 Replies View Related

Output Huge Number Of Permutations

Jan 8, 2008

I have several columns of info on my worksheet. I want to output a list of all possible permutations from this worksheet (not the number of permutations but the actual permutations themselves).

View 9 Replies View Related

Average With Huge List Of Criteria

Jun 25, 2008

I'm currently stuck with the following problem. I have a fairly large database with 10,000+ companies (in the rows) over 10 years (in the columns). I want to aggregate industry averages of a financial item in so-called SIC codes (there are approx. 1,000 different 4-digit SIC codes).
I've been using the daverage function, which works for one criterium (e.g. SIC = 0100) but not for the entire range of criteria. Does this mean I'll have to manually adjust every daverage function for every single SIC code? Or can this process be automated in some way?

Attached is a simplified version of my database file.

View 9 Replies View Related

Select Huge Range Of Cells - A1:c24999

Aug 29, 2009

I have a sheet that has 65000 rows and 3 columns, I need to load this sheet into a system that will only deal with 25000 rows at a time. How do you do a huge range select with Excel?

I need to select range a1:c24999 cut it out and paste it into another sheet. I have about 75 of these these sheets to do is there a way with a formula, a script or? to do these huge selects?

I am a Mac user, running MS office 2008 for Macintosh.

View 11 Replies View Related

Extracting Top 10 Customers From Huge Amount Of Data

Apr 12, 2013

The project I am currently working on is to extract the from over 25,000 lines of data the top ten of our customers for 2012. Our customers are billed monthly. We have about 2,300 customers. There are several customers who have more than 1 account.

The easy part has been to create a "charges" report and download into an excel format. The information can is alphabetized, but then the challenge comes in. Ideally it would be a snap to total each customer seperately and then somehow be able to extract the top ten from those totals.

View 14 Replies View Related

Getting Data From Huge Table To Smaller And Organized One

Oct 24, 2013

I have this table with some information that I need to work with. The data is simple, but it has many issues, at least for me, to be able to work with it properly.

Basically, I just need to transfer part of the data from this table to another table (in another order). I failed to use vlookup because I need more than one parameter to search the data (Date, Shift and Sector).

The problem I am having to get the codes from the source table is that the same code sometimes shows up more than once per date, per shift and per sector. It's also not following a numeric pattern. I couldn't elaborate any combination of formulas to get the info I need....

I also can't change the format of the source table, because I'm not the one who made it, it has years already, and is constantly updated day by day by a good old man who doesn't know much about computers.

The file is attached : Example.xlsx‎

View 4 Replies View Related

Excel 2010 :: Huge Row Height After Upgrade?

Dec 7, 2011

We just upgraded to Office 2010 and now on many of my regular Excel files, when I open them in the new version, the last row that's visible in the window ends up with some massively huge row height such that you can't even page down past it.

I can resolve it easily by clicking on the row number and re-sizing it, but I'm getting tired of doing that.

What's wrong with Excel 2010?

View 3 Replies View Related

Loop Vs. Simple Function, Huge Difference In Speed

Feb 21, 2010

I have a problem with one of my loops, it takes about 17 seconds to do the job of calculating a simple moving average for 200 periods on 20,000 rows. However, if I do the "FillDown" function for the same type of average, it takes 1 second.

Here is the code for the loop:

View 9 Replies View Related

Nearest Number Match - Huge Data To Compare

Aug 2, 2006

i have two columns in tow seperate workbooks to be compared:

in say inp.xls, there is a column(COL A) containting a lot of entries(numbers) - roughly around 60,000. Now in the other workbook, say mem.xls, i have a smaller set of numbers in a colum(COL B) - roughly around 300.

Now, the problem is, COL B, has numbers which may or may not match with numbers in COL A.

* If there is a match(between COL A and COL B), the row number at the match in COL A.

* If there is no match, the row of the closest number (in COL A) before to the number in COL B.

Example:

ROW COL A-----> COL B---------->
1 2184058252 2184276560
2 2184058280
3 2184058296
4 2184058312
5 2184058320
6 2184773640
7 2184774216
8 2184774272
9 2184774424



Now, i want to find 2184276560 in COLA, since it is not there, i want it to return the row number of the closest(previous) viz. row 5(2184058320).

Now, since this is a huge comparision set, im wondering what is the best way to do it. Im using VBA....

View 9 Replies View Related

Excel 2007 :: Changing Huge Numbers Of Hyperlinks (formulas)

Feb 5, 2014

I've got a main workbook (excel 2007) with a main spreadsheet where i group all the information (text and values) about brands and models linked to 10 workbooks (brands) with several spreadsheets (one spreadsheet=one model).

On the main spreadsheet I've got 16 lines for each model which they have the follow formulas, wich are linked to other workbook / spreadsheet (Mar/Gato)

1)

IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE));"";VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE))

and will return text values


2)

IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;82;FALSE));"";VLOOKUP(V844;[Mar.xlsx]G!$A$3:$CE$78;82;FALSE))

And will return number values


3)

IF(M844<>"";HLOOKUP($V$3;[Mar.xlsx]Gato!$A$2:$CE$78;2;FALSE);"")

And will return number values. The return value (;2 goes from 2 to 16.

This process repeats to all the 10 workbooks e and 250 spreadsheets

4) Table Array of the model spreadsheet

With regard to formula 1) and 2)

The problem is that the table array (on the model spreadsheet) could have diferent dimensions (becouse the launch year of the model) like this:

A$3:$CE$78 (2009 - Launch year)
A$3:$BR$78 (2010 - Launch year)
A$3:$BE$78 (2011 - Launch year)
A$3:$AR$78 (2012 - Launch year)
A$3:$AE$78 (2013 - Launch year)

And the return column (value) for formula 1) is always ;3;

And the return column (value) for formula 2) are like this:

;82; (2009 - Launch year)
;69; (2010 - Launch year)
;56; (2011 - Launch year)
;43; (2012 - Launch year)
;30; (2013 - Launch year)
;17; (2014 - Launch year)

The difference between above values is 13, it corresponds 12 months and the total column (12+1)

For the formula 3) the table array its the same, the only difference is instead of starting A$3, starts in A$2

5) The update of the formulas 1) 2) 3) for 2014

I'm doing this manually... The formulas are updated like this,for 2013, I've got table array A$3:$AE$78 and the return value (;17, then for 2014 i will have a table array A$3:$AR$78 and the return value will be (;30, and so on

5) Change the network drive

For other reasons I've to change my files to another network drive, something like this:

Q:KPI2014Main File

6) Main Goal

I was thinking using Excel's regular Find and Replace feature to change:

- The network drive designation
- the table array area
- The return value

But I don't know if this process ( Find and Replace) is safe and clean. I'm doing this manually and I've got on the main worbook/sheet 4000 lines with 3 formulas its 12000 operations!I'm on the 350 line....!

The main goal is to update the all formulas (links) to "brand" workbooks and inside "model" spreadsheets automatically with a macro.

View 2 Replies View Related

Excel 2003 :: Find Data From Huge Database Of Items Of Equipment

May 23, 2012

Macro to find data from a huge database of items of equipment and find certain ones relevant to an area. I am using Excel 2003.

I have a spreadsheet which has three tabs.

Tab one has a list of equipment with a Ref (Col A), ID (Col B), mile (Col C) and chain (Col D) start and a mile (Col E) and chain (Col F) finish.

There are about 25 different Refs and IDs, but all rows have different Mile and Chains.

The second tab is an identical template of the third tab which is where I want the results to go (see below)

The third tab is raw data which list thousands of items but I want the macro to find the items which are in the first tab using the ref, ID, mile and chain information.

View 3 Replies View Related

Three HUGE Columns, Deduplicate And Dump Back Into Three Separate Email Campaign Databases

Jul 17, 2009

I have three very large columns that I need to deduplicate and dump back into three separate email campaign databases.

Here's the scenario:
First column: Largest (15,000+ records)
Second column: Medium (10,000+ records)
Third column: Small (7.000 records)

I need to know if the first column contains dupes from the second & third columns. If so, I must delete the DUPES ONLY so I have unique email addresses remaining in column 1. Then I need to know if the second column contains dupes from the first and third column. If so, I must delete the DUPES ONLY so I have unique email addresses remaining in column 2.

Then I need to know if the third column contains dupes from the first and second column. If so, I must delete the DUPES ONLY so I have unique email addresses remaining in column 3. I can't just dump all of them in one column and auto filter on "unique records only" because the three columns MUST remain separate from each other.

View 3 Replies View Related

Split Up Huge Macro Using Call Macro But Pivot Table Code Errors Out?

Jul 10, 2012

I'm using a CALL Macro to split up a HUGE macro into different pieces:

Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual

[code]...

The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:

Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2

When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.

Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select

[code]...

View 4 Replies View Related

Huge Table Of Dates, Find Range Of Dates

Aug 16, 2006

I have a masive table of dates (the date is created via a if formula)
what i need is so wheni enter 2 dates in 2 cells the system checks all the dates between the two specified and then returns the contents of them to a small area on the page.

Dates To Test 14/08/2026 19/08/2026

Results

14/08/2026

15/08/2026

16/08/2026

17/08/2026

18/08/2026

19/08/2026

Table..............

i dont mind using script or anything like that, i dont have much knowlage of it but do have coniderable understanding of other langages and usually figure it out :D

View 3 Replies View Related

Extract 30% Of Data From Huge Data?

Dec 15, 2011

I have a huge data, from that i need to extract only 30% of data randomly.

Problem Set Types Numbers PP 8769 MM 8894 PP 9019 PP 9144 PP 9269 PP 9394 PP 9519 SS 9644 SS 9769 MM 9894 MM 10019 SS 10144 MM 10269 PP 10394 PP 10519 PP 10644 PP 10769 PP 10894 PP 11019

30% of Type PP is 3.6,That means i need 4 numbers to be extracted under type PP.And 30% of type is 1.2 That means i need 1 numbers to be extracted under type MM and so on...

View 9 Replies View Related

Save Excel File As Another File Using Current Date As Part Of File Name

Oct 10, 2011

VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.

View 1 Replies View Related

Export Data From A Template File To Total List File Using Macro In Template File

Dec 18, 2012

I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):

Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.

It's the Macro 2 I want to edit.

I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.

Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.

For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.

The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)

File and Folder info:

To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate

Total list file name: total_list.xlsx
Total list file location: \servershared otal

Selection info:

The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).

When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.

View 1 Replies View Related

Open Website - Download CSV File / Save As In Specific Folder Under Different Name And File Type

Jul 6, 2012

I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).

I am successful at opening the website with the following code, but how to do the rest.

Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend

'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub

View 1 Replies View Related

Opening Excel File From List Of File Names Located In Single Sheet?

May 31, 2013

loop and range function to apply in the below code through which I can avoid writing code for all the rows.

I am trying to open excel files located in single folder from files name (along with the path) in single worksheet (Column B and Row 1 to 500).

I have created follwing code which opens the file and then runs a macro in it.

a Sub Test()
Dim strFName As String
strFName = Sheet1.Range("B2").Value

[Code].....

View 4 Replies View Related

Date Function- Open A File, Make An Exact Copy And Save It Under A New File Name

Jun 4, 2009

I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

Date: 2009-06-03

What I'm trying too do is open a file, make an exact copy and save it under a new file name.

My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.

is there a way too get this too work irregardles of what the day might be?

View 9 Replies View Related

Extract Data And File Names From File In Folder And Paste Into One Sheet?

Jan 13, 2013

The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.

how I could add to the code so that it also inserts the file name in column c?

It would make it easier to track the data in column B.

VB:
Sub CopyFromFile()
Dim fPath As String
Dim lRow As Long

[Code].....

I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.

The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved