I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?
View 9 RepliesI am copying a large sheet into a spread sheet. The problem is one of the columns contain numbers with some cells having an asterisk at the end of the number. This causes problems with the formulas on other sheets that use this data. Can I use a formula or what is the best way to eliminate the "*".
View 4 Replies View RelatedI am currently working on a very large spreadsheet with a current size of 94mb. So obviously I have problems running and using it.
My question is a general one, I need to understand better why a spreadsheet gets so large. It has 55 tabs with the largest having 1000 rows. It also links in a number of places between sheets but also to external locations. I have moved all files that it is linked to in to the same file.
I am at the point where I am thinking about starting again! And I was wanting some tips or even a recommendation of a book that could help me optimize my spreadsheet going forward so to increase the speed but also reduce the size.
I am using the close.workbook command in VBA to close a workbook I opened to copy data from. I am getting a message that says "You have a large amount of data on the clipboard. Do you want it available?" What command do I use in VBA say no to this message?
View 5 Replies View RelatedPhasing Design.xlsx
We have a requirement to spread/phase amounts over multiple columns (representing months) using a weighting (represents working days per month).
We will calculate the weighting result in excel first (represented by a % per month), so the macro will be more of an allocation of row amount against the equivalent column %.
So far i have the following code:
VB:
Sub phasing()
Dim SourceA As Range
Dim Weight_ResA As Range
Dim TargA As Range
Dim i As Long
[Code] .....
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 RelatedI have a large amount of data and am trying to find certain values.
Firstly - I am looking for any cell which has the number 0 in it, as I need to edit that specific cell. However, when I search for the number 0, multiples of 10 (e.g. 10,20,30,etc) come up in my search. Is there anyway to find the number 0, without also finding the multiples of 10?
Secondly - I am also looking for values over 100, is there a search function which allows me to do this?
I am creating a spread sheet with a large amount of data.
In column a I have a list of managers, in B I have a list of employees and C, I have a list of activities that have been recorded throughout the day.
What I want to do is create a list (on a new sheet) so in column A that would bring back all the managers. Then in column B, bring back the employee that is tied to the manager from column A and finally in column C, bring back the activity that is tied to the employee.
I know how I can do this by doing list validation with INDIRECT, but with the large amount of data that I have this would take forever in a day.
Is there an easier and quicker way to do this?
I have some code to open up another workbook, take the data from sheet1 in it, copy it to a sheet in the first book, and then close the opened workbook. My problem is that whenever it closes the workbook it gives me the large amount of data on clipboard window that I must then click 'No' on before it will proceed. I thought that making CutCopyMode = False would fix that, but it hasnt worked. Heres the bit of code that does this:
CutCopyMode = False
Workbooks.Open Filename:= _
"G:InventoryInventory.xls"
Range("A1:M500").Select
Selection.Copy
Windows("Inventory Report.xls").Activate
Sheets("Vis-W").Select
Cells.Select
ActiveSheet.Paste
Range("A1").Select
Windows("Inventory.xls").Activate
CutCopyMode = False
ActiveWindow.Close
Sheets("Main").Select
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.
I was using SUMIFS on a large amount of data, but wasn't getting back the exact sum that I needed.
It turns out that I had cells with values similar to the following
Code:
000123
0123
Basically, text fields with the same numerical values, except for the number of leading zeros. Due to the nature of the table, the number of leading zeros is important.When I did SUMIFS, it counted the sum of the two values together. For example with the following:
Item
Batch
Qty
ABC
123
1
It appears that even if a numeric value is stored as text, SUMIFS treats it like a number.
I am trying to insert three columns within a large amount of data. I am using Excel 2003 edition. The three columns need to measure max, min, and standard deviation of month long ranges and the data goes all the way back to 1993.
Currently, I have a column that has the correct ranges but finds the average for each month
(=AVERAGE($H7214:$H7243))...
And many more ranges as it dates back all the way to '93. Is there a possible way to insert these three columns with their respective commands (=MAX... =MIN... etc.) while keeping all the ranges from the AVERAGE column.
In effect, I am looking to solely switch the begining of the column command
(=AVERAGE($H7214:$H7243)) to (=MIN($H7214:$H7243) etc...
While keeping all of the specified ranges from the AVERAGE column.
I need a little coaching on big data. I have two reports, one from a production system the other from the invoicing system. I need to reconcile the two reports to ensure our invoicing ties out to production. At this time I am focusing on 1 particular item code, but there are potentially hundreds of item codes.
I have 1 Item Code.
I have a around a hundred invoices. (Sample 100)
I have a few hundred different customers. (Sample 100)
I have several hundred lines of billing data. (Sample 650)
I have several thousand lines of production data. (Sample 40,000)
In the billing system, per invoice I might have 1 or more billing lines for this item, usually no more than 2.
In the production system, per invoice I might have 5 to 50 or more production lines for this item per invoice.
Example
Prod System:
30 lines of data has a run amount and a total run amount for each line.
I am looking for tools to reconcile this data. VLookup and Index/Matching is not cutting it. VLOOKUP does not move beyond the first line found. Index/Matching wants to add all the prod together. I can get subtotals easy enough. I have tried Concatenation, but when it takes multiple production data lines to equal 1 billing line, this does not work. I needs some thing that can look at the Qty on a billing invoice and tie it out to various lines of production data to identify which production jobs went on that line of the invoice.
Here is a sample. These are fairly easy to resolve, but others are not so easy due to number of production and billing rows.
Invoice number
Billing item
Est. Quantity
Actual quantity
Billable Qty
Sales order
Position number
Sequence
Agreement
Billing source
Description
Total Billable Qty
[Code] ..........
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
I am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.
I have two objectives that I am trying to reach:
First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date
Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.
If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.
I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.
Start date is column E
Finish date is column F
Owner is column L
Im trying to import data from a plumbing supply website to an excel spread sheet.
On the supply house web site, for each part catgory there are corresponding pages containing a price list data table.
there are from 1 to 3 pages (price list data tables) for each part category.
i can import data from page 1 and page 3 fine. When i try to import data form page 2, it returns no data, or imports page 3 (instead of 2)
I spoke to the person who designed the website and he said that it was a problem within excel and not a problem with his program. (said it was a problem with excel not being able to recognize the page)
Each sheet has the same basic formatting. A1 contains a name. B1, C1, D1 are column headers. B2:B is data. C2:C is data and always stops at the same row B2:B range does. The only differences between the sheets is that they might not stop at the same row. I want a macro that merges A1 vertically as shown in my spread sheet to the end of column B and C. I want a border around the merged data, as well as around the B data and the C data individually.
View 3 Replies View RelatedI need to link 153 cells from one workbook to another
Is there a code that can do this ? The cells are not contiguous.
Something like.
If not intersect.....
SetMyrange......
With tartget.Copy.... ??
I have a large amount of worksheets that require a formula update. Update itself is simple. Each formula in the cell has to be devided by different cell. My problem is that I have a big number of worksheets to do this in and they are in different workbooks. Is there a faster way of doing it besides manually updating each cell?
I have a group trip coming up where we took in a $1000 deposit per person. The total amount is only in the main members cell. There will be two more payment amounts coming. I need to make certain each person has paid the total amount they are required for the trip. There are two possibilities for total trip amount. Double occupancy is $15,500.00 and Triple is $14,500 per person.
There is a unique Identifier for each registrant, except when there is a multiple person under one registration the identifier has all the same number except at the end. I figured out a way to strip the number down to just show the same number in one registered group. There could be up to 10 people under one registration. I might not have the best option for breaking down the Identifier number, but thought it would be a good start. Used the code Left(column, number).
Issue is this. How can I now take the total number of people under one group take the total deposit and split it between just that unique group so I can then determine what is still owed after payments are made.
I want to link a number of cells on one worksheet back to another worksheet within the same workbook. Say everything from Cells A1 to M90.
I find the fastest way to do this link A1 on second worksheet to A1 on first worksheet (by using = and click on worksheet 1 Cell A1).
I then click on cell A1 on worksheet 2, grab small square in bottom right hand side of cell, and drag accross to M1.
While A1 TO M1 is still highlighted, I then grab small square on bottom of cell M1 and drag down to M90.
This makes all cells within this range reference back to worksheet1. In advanced settings I have unchecked for all blank cells to show a zero in woksheet 2.
Therefore I now have what I currently want, although I would like to make all reference absolute?
I tried doing this from the beginning again making A1 absolute at the start, but dragging the cells across and down does not provide the info I am looking for in all cells?
I have included the table. What I want to do is in a user form pick a county ie Devon, Then by clicking "go" all entries that contain "Devon" are then copied to a " sheet 2" I don't want to use the Ctrl-F
View 2 Replies View RelatedFor the below macro.
ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030
A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.
For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.
I have a spreadsheet with names on and I need to check to see if the surname in column C, also appears in column A&B.
I would like to list, in another sheet, the names that are in Column C that are NOT in A&B. Then I can see what data is missing.
I have a spread sheet (sheet2) that runs an add-in and does some calculations and arrives at a result which is a number in cell P6. All I have to do is type in a Stock Symbol in cell A1, and the sheet runs the add-in, calculation automatically. If I change the symbol in A1, the sheet re-runs the add-in/calculation and gives me the result for the new stock in cell P6.
Sheet 1, has a list of stocks, indexed in Column A.
Rather than me changing the symbol manually in sheet 2 cell A1, how can I automate it such that it will run the functionality automatically for each symbol in Sheet1 column A sequentially and then tabulate the results for each stock/symbol in Sheet 1 column B.
I tried to attach the sheet/book herewith, but could not. It is less than 100KB in size
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?
I'm copying large amounts of data from one full sheet in one workbook to the sheet in another book.
Everything is now automated, except that it asks in between books if I need to keep the Clipboard data.
I was trying to compare string from excel spread sheet to a value I am not getting any output. It is not working
View 4 Replies View RelatedIs there a way to lock out a spread sheet if it is sent to an unauthorized e-mail address or forwarded to another e-mail address?
View 9 Replies View RelatedI have a few hundred work orders done in Excel. Each spread sheet is a seperate file but all are identical in structure (basic order form). I need to change the value in the same cell on all the forms (manager's name needs to be changed on all our forms).
Question: How do I do this without having to open and edit all five thousand forms?