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 Repliesuse 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.
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 RelatedThe 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.
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
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.
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....
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] .......
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.
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...
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.
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?
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.
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] .....
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 RelatedI'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.
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.
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?
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 RelatedI 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:
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.
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.
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]...
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
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
For some reason my data validation cell is not working when I first open up the spreadsheet.
I have to go to data validation, open it and just hit ok, then I am able to use my drop down list.
My data is this
"=OFFSET(name,MATCH(LEFT(B2,LEN(B2)),LEFT(address,LEN(B2)),0),0,SUMPRODUCT(--(LEFT(address,LEN(B2))=B2)),1)"
I have a data validation list does nothing upon start up (not working). The formula is:
=OFFSET(E$1:E$5,MATCH(C94,LEFT($B$3:$B$87,LEN(C94)),0)+2,0)
If I go into data validation and just click OK (leave formula as is), it starts working again, but the next time I start up the same thing happens.
I'm trying to add code to validate the format in which users are entering in a date within my userform, and I am also trying to validate if they enter in a specific value within a combobox, they will be unable to enter data within a textbox further down in the userform. Here is how my code is currently written, but neither my date or data validations are working. I have bolded the new coding I added to an existing code that was created by someone else at my job prior to it being given to me.
[code]
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
[Code].....
How well does Excel / VBA deal with data in a binary format? I am working on a Base64 decoder, which in basic terms means I will have 4 numbers, which I need to convert into their binary representations (6 bits each). I then need to combine these binary groups together, and then divide them up differently to give me 3 numbers (made of 8 bits each).I'm just wondering if anyone has any examples on how best to work with binary in Excel? Also if there are an gems of builtin library functions?
View 4 Replies View RelatedMy V-lookups are not working. I have data with extra spaces before or after the word. The table I am looking up does not have extra spaces. I'm trying to to a TRIM(data with spaces) and then copy and paste the values so that the spaces go away and the TRIM is not taking my spaces out.
View 9 Replies View Related