I am trying to sort in rows, a large number of rows. I can do text to columns, delimit by colon, and sort individual rows. But I have spreadsheets that are 100s of rows long. Is there anyway to automate?
View 4 RepliesThe following macro deletes blank rows in my project (I have a source sheet that I "transfer" information to various "law practice worksheets", and it works fine. However, once I get past a certain number of rows (my project will include 65,000 rows), the macro does not delete the blank rows any more from the "law practice worksheets" (I previously sent this spreadsheet to the Forum with another question that was not answered yet). Here is the macro for deleting rows:
On Error Resume Next
Sheets("InternatlLaw").Select
Range("A2:D65000).Select
Application.DeleteBlankRows = True
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
ActiveSheet.Range("A2:D65000").Select
On Error GoTo 0.............
I have a log that I regularly use to import text files, after each text file import it leaves a large number of rows blank that I have to delete before I add in the next text file import.
Any VBA method to detect this and remove the blank rows so that I dont have to keep checking to remove?
I'm currently trying to make a sort macro that sorts a row of data that has a combination of numerical data and the text entry "N.A."
e.g.:
My field:
1
2
N.A
4
5
N.A
9
I want to sort the cells by descending value. But the result appears as:
My field:
N.A
N.A
9
5
4
2
1
But I want to numbers to appear first and have the "N.A."s to be after the number, since I have about 100 columns and some rows have like 30 N.A., which is pretty bad for usability if all that appears in the first screen are "N.A."
EDIT: by the way by ascending the data is fine since it orders by smallest number to largest and THEN the "N.A."
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 Relatedwant 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 Relatedhow to return the top 5 results in a set of data.
In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.
If I have the numbers 1-50 repeating in columns, 1-50, 1-50 etc is there a way to determine that number "1" falls 1st, 51st etc in the sequence without doing a manual count? Or that the number 2 falls 2nd, 52nd etc?
I am trying to copy part of one line of pasted information in to a certain place on the line below.
i.e I want
ABCabc
to look like
ABC
abc
This needs to happen for a large number of lines. I have already added a space in between all the original lines.
I'm working on a spread sheet to identify the to ten numbers from a list. I can find thes numbers and use ( =MAX('MHF2'!B:B) )for the function to do so. I'd like to dispaly the data in the cell directly to the left of this number... How do I do this?
11302
21000
3484
4440
5428
6419
7367
8333
9315
10258...........
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
I have an issue where I need to apply certain formats to a number of text strings which are domain names. This is purely for reporting purposes. I don't need to actually visit the url. This video demonstrates a bit more. [URL]
View 1 Replies View RelatedI have problems in PASTING my 19 digit number from the source report into excel.
E.g, the original value is 8321515222222123122 but it always transfer to 8321515222222120000
I have tried the simple cell format setting, that is after pasting, I set the cells to text, but it doesn't help and also try the custom fomat "###################", but it is still the same result with the last 4 digit lost.
What I want the format to be is not in scientfic and have to be full display.
i have a spreadsheet with a certain data (rate of return) associated to a specific day of a year. (ex. 8-dec.-08 0.99865), and i have to calculate the rate of return (r) for every month, which is done by applying a formula (GEOMEAN) to all the rate of returns for the days of a month (ex. GEOMEAN(r[01 dec.]:r[31 dec.])).
i don't have any problems with that part. the hard part is that i have every single trading day from jan 00 to dec 08 (2000+ days), and i do not want to manually select the ranges. also, the dates do not include weekends (trading days per year = 252) which means that i cant (a) automatically determine a range or (b) automatically associate a number of days to a month (ex. oct 08 doesn't have the same amount of days as oct 07, because of the way week-ends and holidays are arranged..)
I have data in F6:AG6 raw filled with numbers as well as dates, i am looking for formula which can calculate a larger value of number format (excluding date format).
View 1 Replies View RelatedThe format of the numbers are:
####-##-###-####
I have 2000+ numbers is coloumn B.
There are a lot of sets of numbers that are still text! It's highly annoying and I need to analyze the data in Access.
I tried the paste special advice and the 3 ways microsoft suggests. I have a feeling its not working because of the required format that I need.
I need to do this project tonight!
Background: these are imported numbers, I had to combine other excel databases... By the way, is their an easier way to combine excel sheets into 1?
Each sheet contains 30,000 rows of data in column A, C, E, G and I.
I want to find each cell of column A, C, E, G and I to another sheets column A, C, E, G and I respectively.
If match found then it should return "True" in column B, D, F, H and J.
I have tried VLookup, but it takes too much of time for calculation (1,50,000 vlookup Formulas needs to calculate in each sheet with If condition) some time calculation gives wrong results.
As well as I want to do filtering on this all the data... so that time calculation gives the problems (I have used application.Calculation=xlCalculationManual)
So now I am using Range.Find method, but that loop excecutes for 30,000 times and each ittration contains 5 Find methods.
What i'm trying to do is to check a large number of cells, if "0" is written down in that cell it stays as it is and if the number is different from "0" i want to put a "1" instead.
View 5 Replies View RelatedI heard there is an efficient way of writing large amounts of data at once to a worksheet. My program is writing alot of data (e.g. 600+ items in each row) and takes over an hour to run because it writes to each cell one-at-a-time. I seached this forum and couldn't find the answer. Here is an excerpt from an old 1998 article by Dermot Balson and William Mercer that used code to write to cells in large chunks but it doesn't work for me - I get a method failed message.
'to read in
Dim A As Variant 'MUST be variant, no brackets
A = Range("SomeRange").Resize(10,20) 'reads 10x20 array starting at range SomeRange
'(NB I've used Resize above but you can specify a range of cells any way you want)
'to write back to sheet
Range("SomeRange").Resize(10,20) = A
'A can be any data type but MUST be two dimensional even if you are only writing one
'column or row - first dimension is used for rows, and the second for columns
'this can be slow - see third question below for workaround..
Here is the link to the article: http://www.avdf.com/apr98/art_ot003.html. A more detailed explanation how this trick works
I have various month's data - forex tick data - each month has between 12 and 14 million records and comes down as a .csv file.
I would like to analyse the data in Excel
How I can breakup the huge .csv file into manageable excel parts?
I have a very large database where I want to filter out numbers that do
not have certain two digits as their last two. For example, the column
based on which I want to filter out data contains numbers like
197301310153. I want to filter out anything that does not end in 53....
I would like to write a macro that links cells in one workbook (working) to cells in another workbook (summary).
I don't want this to be a straight swap thought, I'd like it to contain the following formula:
=IF(Working!G8="a","a","")
The cells in the working workbook are listed vertically, while the cells in the summary workbook are listed horizontally.
ie. link G8-G18 in working to B3-K3 in summary
link H8-H18 in working to B4-K4 in summary
...etc
I'm using nested IF functions to reduce a large number of bands to a smaller set using the following formula:
=IF(LEFT(D4,1)="
I am trying to use a simple formula: COUNTIF($CT$2:$CT:$430749,CT2)
I am trying to fill this down all the rows (430,749 rows). The formula behaves as its supposed to up to around row 650. After this point, all resulting values are all the same, which happens to be the same value that was returned from the original formula in C2. However, this is not correct. It's like the formula just stops working after row ~650. I tried F9 without success.
I have a large *.tab file with around 450,000 records. I opened the file in excel and then filtered two columns where there are values equal to 0.
I then highlight all the rows and try to delete them, but I receive the following error popup box:
"Microsoft Excel cannot create or use the data range reference because it is too complex."
How can I delete all these filtered = 0 rows without getting this error??
I I have a excel document which has a sheet containing a large number of array formulas.
I turned the automatic calculation off.
However, even when I press F9 to calculate the cells it just crunches, gets stuck at “Calculating 0%” and then crashes.
The majority of the array formulas are IF queries with multiple criteria.
Would it speed up the calculation if I didn’t use array formulas?
i was stuck with some mass data which spans over many rows and columns. i am relatively new to excel and wanted to know how i can get this data arranged in a way where i can access all the data from a printable view. i have attached a file explaining basically what i want.
View 5 Replies View Relatedsimplifying a formula which gathers data from about 50 worksheets from within the same work book.
The data to be gathered is in the same cell on each worksheet and is simply a number but i want the SUM of theses numbers carried forward to another worksheet. Each worksheet is named by date i.e. sheet 1 is named "16 June 2014" and sheet 2 is named "23 June 2014" and so on until "30 March 2015" (Each sheet represents one full week Monday - Sunday).
I have a VBA macro that goes through a decently complex system to delete something like 70 old tabs, create new ones, link data from a main page to these tabs, and create 3 plots per tab. When running the script, it will go through something like 50 of the tabs, and then receive an error on something rather trivial that has been done many times before successfully in this macro (like changing the font size on one of the axes on one of the graphs). I tried an On Error Resume Next, and sure enough it ignores the error, but it then does some sort of crazy loop starting over recreating all the tabs incorrectly, and then excel just shuts down. So, that's no good.
Anyway, I can comment out the change of the font size, but it still has the same issue on some other trivial thing. It seems like the system just runs out of room or needs a cache cleared or something. I'm not sure.
I have a problem with VLOOKUP looking in sorted lists of more than 16384 items.
Basically it fails at 16385th item
I have developed a VLOOKUP function that looks up in multiple tabs in
multiple files and returns a value. However I have discovered that it fails
at row 16385. As Excel copes to 64,000 rows I regard this as a bug. Has
anyone else encounteed this problem? is it just my machine, is there a
solution / bug fix?
If I use FALSE when the item to be found is beyond 16384 the function
returns the 16384th value this is a warning to other users who may not have
noticed as I did not initially.
--