I've got a spreadsheet that uses about 2200 rows and about 15 columns. However, when I try to insert a column, it freezes up the application. My assumption is that this is caused by the fact that there are in excess of 65,000 rows. When I try to highlight those 63,000 rows and delete them, the system freezes up as well.
These excess rows are unnecessary and I'm sure add to the file size.
Running Windows XP with Excel 2003.
I've exported some data from our finance system into Excel and now need to pivot it, problem is that there are 100k plus rows and therefore goes across more than 1 worksheet. Had a read through the forum and a number of topics about this but I'm a bit stuck. I've got as far as
1. Ungroup the 2 sheets, copied the row headers from sheet 1 to sheet 2 and then named the data in each sheet. I therefore have 2 identically arranged data ranges (albeit one is 65k rows the other 40k rows) named 'block1' and 'block2'
2. In a new workbook I've done
Data > Import External Data > New Database Query
3. I followed the wizard and slected Block1 as the table
4. In MSQuery I've edited the SQL to read
SELECT * from block1 union all select * from block2
I'm now stuck and have 2 issues
1 I'm only showing 8192 records is this a limitaion of MSQuery or has something gone wrong ?
2. I still don't know how I pivot the data
i'm working with downloading data from our server. it downloads in excel 3.0 format, and the contents exceed the 65k cap by far.
now what i used to do for this when i had 2003 installed on my machine, i would open the 3.0 format file and convert it to 2003 format so i could import it into access, where i could move it into two 2003 xls files to manage by use of copy/paste from access's table to excel's spreadsheet. The 3.0 > 2003 conversion was necessary because if i try to import a 3.0 excel file into access 2003, it gives me a error that the file contains no data.
so here is my problem now. our office just updated to 2007. it appears that when you run the conversion option to 2007 format, OR just save as... and save it to a new xlsx, it stripps off the remaining data that overstacks the 3.0 format file. everything above row 65536 is lost (and this paticular file has 77k+ rows).
so i decided... "ok then i'll do it my old way". i saved it into 2003 format instead, just as i used to, and imported it into access, and guess what? it stripps it in that format too now! (the majesty of upgrades... buggs to year 2010).
so my question is... how do i get this 3.0 overstacked file format converted to xlsx format retaining ALL 77k rows in a single sheet, or how do i strip it into 2 separate xls 2003 format files? either way will work, i just need all my data so i can work with it.
I have a large dataset with 21 columns and 3000 rows, but to keep it simple, the data looks more or less like the first dataset in the attached excel file.
This results in 3 curves (time vs force). However, there is excess data in there, I only need data as soon as the Force becomes positive (>0) untill the Force drops below 0.02 again (I consider that the end-point of my curve). So, this dataset would look like the second set in the excel file after the macro-manipulation.
I think one of the difficulties is making sure that the macro doesn't cut of my data right in the beginning, when the curve first crosses 0.02. In that case, you get the last dataset. And this is not what I want...
I hope I explained my problem well, if not, please let me know.
So, is there anyone who knows how to do this?
By the way, is there anyway to insert small excel tables in this post? Cause I wasn't planning on having an attachment, but if I copy-paste the data in this post, than the formatting gets undone and it looks like there's no way to fix it...
I am trying to write a function that sums the values in a range in excess of 2. For example, if the cell is 3 add 1, if it is 5 add 3 etc.
Here is what i have so far, it compiles with no bugs, but it does not work in my worksheet. I wrote it in Sheet 1 (VBA) of the worksheet i am using it in.
I have got a cell or cells with certain number of Characters (Alphabetical or Numeric or Alphanumeric). I would like to remove the excess Characters in the Cell from the end.
Example: If a cell contains 234 characters, Excess characters (More than 200) to be removed from the end.
Any formula or Vba program in generic form, so that i can limit the number of Characters to as required.
I need VBA to remove more than 3 blank spaces after the city & replace them with a comma & one blank. For example:
All in one column AB starting with row 7 I have:
Johnson City (13 Blank Spaces) NC 75503 (1 to 3 spaces after state & is OK)
Jacksonville (10 blank spaces) Fl 23854
Henderson (12 blank spaces) NH 28363 (12 blank spaces)
etc
I need all in one column AC starting with row 7:
Johnson City, NC 75503 (,space before state, same space after state ok)
Jacksonville, FL 23854
Henderson, NH 28363
etc
I was wondering if anyone had a formula or Macro suggestion to remove rows with no information. I made a pivot table that feeds to another sheet in order to make it more user friendly. The only thing is when there is no information I have formula to returns a zero. I would rather the row be hidden.
View 9 Replies View RelatedIs there way, short of creating a macro, to have duplicate rows deleted in a spreadsheet?
View 3 Replies View RelatedI have a report that I import from a program, but every time I export to excel, it skips lines. What would be the code to remove empty rows from it?
View 2 Replies View RelatedI have a report that pulls data from another worksheet in Range A26:J58.
What I want to be able to do is assign a button for printing the report but
before printing from A1:J70 removing or hiding any row in the A26:J58 range
where there is no data. The range is filled from row 26 down so it is not a
random fill.
Also, I want to save this workbook as a template so that it can be used over
again, so I guess it would not be good to delete the rows in the range
otherwise I would have to recreate them.
After importing a text file I use a macro to format it, but one part I can't figure out is how to delete the first two empty rows. I need to delete them because the cells in each of these rows are merged together which complicates data manipulation. As you would expect, the row number will be different with each import. Can anyone help with code that would accomplish this?
View 9 Replies View RelatedCurrently i have this to delete entire rows that have cells which contain any words with "Security Market" in it.
How can i extend this to include many more variables like e.g. " Total" "ABC" "XXXX"
Dim rag As Range 'Rows with "Security Market"
Dim sec As String
sec = "Security Market"
Do
Set rag = ActiveSheet.UsedRange. Find(sec)
If rag Is Nothing Then
Exit Do
Else
Rows(rag.Row).Delete
End If
Loop
I want to create a macro that will cut and paste a worksheet out of an existing workbook and tidy up the table by removing all rows where there is no value in one of the fields (product or part number for example).
View 8 Replies View Relatedremoving empty rows from my set of data.
I have a big set of data that I'm copying and pasting into excel. When I paste it there are 2 empty rows between each line of data. I want to remove these empty rows, without affecting the data. I tried 'Go to special...' and deleting all empty cells and stuff but I couldn't get it to work. The problem is that there are blank cells in some of the rows that I want to keep. When I try to remove the blank rows, I also end up removing rows of data that I want to keep.
I've made an example for you to try and explain it better - attached.
remove all of the rows that are completely empty, but leave any row that has any data in it (at any point), that would be awesome!
Is there a quick way to remove blank rows quickly. I have a spreadsheet with over 8500 rows but some are blank.
View 8 Replies View RelatedI'm trying to remove duplicates without deleting the rows they were in (instead just being left with blank spaces).
For example Column A might look like this:
Column A
1
1
1
1
2
2
2
3
I need it to look like this:
Column A
1
2
3
How I can delete these duplicates without doing it one by one?
I have an excel spreadsheet with approximately 10000 rows. There are approximately 10 columns...
Column A - person's ID number
Column B - name
Column C - Street Address
Column D - City
Column E - State
etc...
It being such a large list there are many many duplicates (it was pulled from a bunch of different excel spreadsheets and combined into one via copy and pasting one document into the other).
I figured out a way to have excel highlight the duplicates in a different color... but scrolling through 10k people and deleting the duplicates is obviously very tedious (approx 40% of the spreadsheet is duplicates).
Is there a way to sort out the duplicates (via their ID number, column A) so that there are no repeats? I want to get rid of the 2nd/3rd/4th (etc) occurance of the person's information but I was to obviously keep the first occurance.
I tried using Advanced Filter but I must have done something wrong because it shrunk the list too far down.
I have to download a report through SAP and have written some pretty basic stuff to automate the formatting process, however, i still have to manually remove blank rows. I would like to be able to add the removal of theses rows as part of the code, one of the main problems that i see is that i run this report every week and the empty rows are not necessarilly in the order every time.
View 4 Replies View RelatedI have obtained the following data which I need to put into a spreadsheet to import into an accounts program. I can't change the way I get the data which is as follow
Hrs worked Rate
05:55:00£30.00/Hour
07:40:00£21.00/Hour
05:45:00£30.00/Hour
What I need to have is 5.92 30.00
7.67 21.00
5.75 30.00 ie the time format in decimal and loose all the unnecessary symbols etc for the rate
I have a workbook with a Pivot Table full of user data and security reports. The table is structured in a way that shows, numerically; each username and how man roles are assigned. I need to remove the username/row combo's that are listed as 1 or less. Obviously if I simply highlight the fields I want to delete it won't work. How would I go about removing such rows?
View 2 Replies View RelatedI have a single spreadsheet with a few columns but hundreds of rows. Each row is a property reference (with other data such as address in the columns) with a type of charge in column J and the amount in column K. I'll try to replicate below ...
A B ....... J K
Prop1 Prop1Address Rent 100
Prop2 Prop2Address Rent 150
Prop3 Prop3Address Gas 70
Prop4 Prop4Address Water 60
Prop4 Prop4Address Rent 200
Prop4 Prop4Address Elec 80
Prop5 Prop5Address Service 90
I want the sheet to display a property on a single row so would look like as follows ...
A B ....... Rent Gas Water Elec Service Total
Prop1 Prop1Address 100 100
Prop2 Prop2Address 150 150
Prop3 Prop3Address 70 70
Prop4 Prop4Address 200 60 80 340
Prop5 Prop5Address 90 90
I have 5 worksheets that I currently have to add information to and cut/paste information from one to another. The initial information stays the same but I have to cut and paste it into one of the other worksheets based on wether or not we; need to decide on a job, are working on the job, lost the job, won the job or the job is complete.
I would like to create one master worksheet where the information can be entered with a drop down cell stating the status of the job (listed above). I created the master tab and linked the 5 subordinate tabs using an IF formula, but how can the subordinate tabs filter or sort the references and organize them on the top of the page rather than leaving a bunch of blank rows (because the info in these blank rows went to a different subordinate tab)? I want to enter the info in the master tab and simply change the pull down cell to change which subordinate tab the information shows up on. This should allow me to print the subordinate tabs as reports without having to manually cut and paste the info or filter it, correct?
I have a long grocery order list and only choose about 100 items out of 1000. I would like to send it to the store with only the items I need. There are several category headings throughout the list which I would like to keep...like Produce, Meats, etc. The list total dollar amount is automatically updated as I enter the amount of the items I need. I just have a lot of unused items (10) pages worth.
View 2 Replies View RelatedI have several sheets with about 250,000 rows per sheet.
But, even I sort by Column A, there are STILL hundreds or rows that are total blank interspersed down the page . . . I can't autofilter for blanks because there is too much data . .
How can I get rid of them?
Need to find out how I can restrict a worksheet to show only the columns and rows that I want. For example, I want to show columns A to H, and rows 1 to 25, and nothing else - I don't want there to be a column G, or a row 26, just blank grey space. I know it's possible, because I've seen it done : D . But the closest I can get to is: View > Page Break Preview, which isn't quite what I'm after.
View 2 Replies View RelatedI have this spreadsheet that I am creating schedules for my employees since our scheduling software at work is horrible to work with. It calculates total hours for each employee at the far right of the rows, and calculates total hours for each day in the columns. The formulas that calculate the hours for each day is set up to automatically deduct 30 minutes from a shift, for lunch, if they're scheduled for more than 5 hours (the gray shaded employee rows are for managers and that only deducts 30 mins if they're shift exceeds 8 hours).
I'm familiar enough to put somewhat complex formulas together, but I don't understand it enough to always make them work correctly. What I'm wanting to be able to do is add or subtract entire rows (add new employees or remove old), and have my formulas for calculating the total hours to still work. Right now if I delete a row the cell with the formula in it says #REF!. I can fix this by going through the formula and manually deleting everything in that contains those other cells, but the formula is huge, and it's pretty tedious going through the whole thing when it feels like there should be a simpler way of doing it.
TLDR: I want to be able to add/remove a row and have my cells currently on row 36 update the formula correctly.
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
I have a sheet in an excel workbook which I export to a separate file and then save as a text document, I need to remove the tabs in this file, however the file (example attached) needs to be in a certain format to be imported into a piece of equipment which has a proprietary file format. Part of this format is the 2nd row and 5th row must remain present and empty.
[URL]
I'm looking to combine a number of worksheets into a summary sheet .. but on some the narrative is more than the 255 characters. How can I , where this is the case, insert a row below.. say input "Continued" in column A and then copy the contents of the cell which is more than 255 to the cell immediately below it.. and maybe do this again where users input more than 510 characters.
Alternatively, is there a way of getting around the copy and paste values.. where it only inputs 255 ?