I have a large table about 2000 rows x 2000 columns (small extract below) and i need to get all the non-blank data out of it into just 3 columns ignoring any blank cells. Column A is the customer account number and the other columns contain the prices for the part numbers as described in row 1.
a/c - part no
002000
S01000
S01020
S01040
S01060
S01080
[Code] .......
What I need to end up with is list of Account/Part No./Price like this...
A/C
Part No.
Price
00017391
S01020
7.2100017391
S01060
10.4500017391
[Code] .........
There will be about 70,000 lines from the table I have.
The way I have this set up right now is that data is being submitted by an Infopath Web Form and emailed to me, it produced an XML file with values for all the possible entries for each person but only certain departments are filling in certain blocks.
That XML file is uploaded into a worksheet and then I'm trying to process data off that table to get the information I'm needing. I'm trying to divide data if a certain piece of the formula matches (IF CCU & August then Divide F column on same line by G column on same line and display the result)
I'm also trying to ignore empty blocks which is where my problem is at. So far I've come up with
[Code].....
but that is only working if the data happens to match on the same line in the data table.
Added the excel workbook: patientdashboardexperience.xlsx
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
I have two sheets with a table on each. Sheet 1 table 1 has a row of data on jobs we are bidding. If we win the job there is a column where we input a job number. What I would like to happen is that when someone enters a job number in this column, it takes that number and the information that is in two other cells in the same row to table 2 on a sheet 2. This will give us a current jobs sheet. I am using Office365 but some users are using 07 maybe even 03.
The below code deletes blank columns in two work books. Now I would like to add to this code by performing an additional action on the worksheets. Below is the code that I have so far:
Sub Commandbutton2() Dim iCol As Long Dim Isheet As Long Dim Item As Worksheet Dim MFBooks(2) As Workbook Dim lngIndex As Long Set MFBooks(1) = Workbooks("MF BANK EXPOSURE SUMMARY.xls") Set MFBooks(2) = Workbooks("MF CP EXPOSURE SUMMARY.xls") For lngIndex = 1 To 2 For Each Item In MFBooks(lngIndex).Worksheets .....................
So, in general I do have two columns F and G taken from the other xls.
Age is obviously difference between today and open date
Open date is open date.
I made a table like:
Age and due date.png
In this case, I have 3 rows where there is no open date extracted, therefore is no age. The counter stops on them and shows 529 in total instead of 532 or shows the age as far more than 365 days. How can I count the blank cells, but only in the range of the list I do have, not the all blanks I have from the beginning till the end of the column, so I could (for similar in this case) have 3 blanks cells counted?
Sometimes is also stuck in the middle of counting (when blanks are inside there) and the total number is even smaller. What function can I use to count these 3 (or less, more inn the future) as BLANK to have the total numbers realistic?
On my data tab I've got a column for "review date." Some of those cells are blank. When you go to the pivot table, the respective cell for that blank.review date cell displays the date 1/29/14. There is no data in the cell on the data tab, so why would it be displaying 1/29/14? I want it to either say "blank" or just be blank. It does this for every review date cell that is blank.
how to create a pivot table that does not skip through blank data cells. I have a pivot table with data for several dates, but not every date has a data point. I would want the pivot table to show all the dates with the blanks, rather than skip through the days with no data.
I have a table of data 300 rows long with protected formulas in adjacent columns and at the bottom of the column. Some of the data cells are blank but the non-blank cells are contiguous and start at the top of the range (see Before Example).
a macro that would allow users to insert a blank cell at any point in the data table without losing any of the other data or disrupting the formulas. In other words I want to move all the data from that point to the bottom of the range down by 1 row. A before and after example for a smaller 10 row data set is shown below.
Before
AB1data aformula 12data bformula 23data cformula 34data dformula 45data eformula 56data fformula 67 formula 78 formula 89 formula 910 formula 1011formula Aformula B
After
AB14data aformula 115data bformula 216data cformula 317data dformula 418data eformula 519 formula 620data fformula 721 formula 822 formula 923 formula 1024formula Aformula B
I would like to record/create a macros to open the file and paste 4 columns worth of data into another spreadsheet. Making sure the data is pasted in the correct column and doesn't overight data already in a tracker. Ie. paste it into the next available space.
We utilize large data sheets that can be as large as 300K in rows and 10 to 15 columns wide. Because of how we receive the data, we are forced to manipulate things so that all matching data for a record ends up on a single row. (e.g. Record#, Document Type, Husband Name, Wife Name, Wife Maiden Name, Etc.)
Right now here's how the data is received:
a a a b b c c c c d d d e e e e
Using two vba scripts, we first separate the data with row spaces between the unique data as follows:
a a a
b b
c c c c
Then with another script, we transpose the data as follows:
a a a b b c c c c d d d e e e e
When we transpose the data, the end result starts at the top of the page and go down eliminating the original blank rows. Not a huge issue but I would like to be able to maintain the original data format of the rows so that the data matches the original sheet line for line. The end result would give me the data as follows...
a a a b b c c c c d d d e e e e e
where the vertical gaps between the letters matches the original rows. Like I said, not a huge issue since we can rejoin the transposed data to the original data fairly easily. But it would be nice if we could end up with the above format for speed sake.
The two scripts we use, one-to insert the rows and two-to transpose, take a very long time to run with the transpose script taking the longest by far. On a 30K row sheet, it will take on our systems around 30 minutes to transpose and about 15 minutes to insert rows. Because we have several columns that need to be transposed, a 30K row sheet will take at least 2 hours to complete. A 300K row sheet, that will take 10 to 15 hours to complete.
Is there any way to speed up the scripts either by upgrading to a faster CPU and or writing the scripts to preform faster?
My preferred solution would be to write (have) a formula to preform the transposition that gives me the results as noted above since formula's run so much faster than vba. Is this possible? I have tried all kinds of formulas and can not come close and of course the straight transpose function does not give me the solution I need as noted above.
I have enclosed an excel 2010 spreadsheet with 10K rows of data in rows along with the scripts I use (nothing sensitive here). The tabs at the bottom shows you the data before I transpose, then the data after it has been transposed . To speed up the scripts, I have stripped away the all the rest of the data from the original sheet except just what I need to transpose at one time. Once that is completed, we then re-join the transposed data with the original sheet. The six digit number you see to the far left of the data is the record ID number from the original data. We use this to rejoin the transposed data with the original data so that we know everything is back where it should be. (Note: The insert rows script is run on the original data and not the data you see on the enclosed spreadsheet. That is the only way we can generate unique rows with matching ID numbers. We arrive at this by taking the original data, concatenate the record ID with the column we want to transpose and add a # between the two so that we can break things back apart after the transposition using the text to column function using the # as the separator.)
The sheet I have attached is in the 2010 Macro Enabled format...(xlsm format). We use the xlsb (binary) format for the data to reduce the file size as our normal procedure and run the macros from inside that format. Changing from the xlsx to xlsb format did seem to speed up the scripts a bit and greatly improved the file performance as a whole e.g. saving and loading.
One thing I have done to speed up the scripts is to strip all the data away that is not needed for the transposition. That did work but only a marginal amount.
We are using windows 8.0 with 4G memory and your basic processor speed...e.g. nothing fancy.....just your basic stock computer. Nothing else unusual is installed or running on the computer or at the time the scripts are running.
For those of you that process large sheets, how much of a performance upgrade will we see in processing our scripts by either upgrading memory to 8G (or more or much more) and or getting a faster processor? Or have we reached the maximum script speed already? Or is this a limit to Excel.
One other issue to note: As I stated above, on the 30K row sheets, not a super problem with about 2 hours needed to run the scripts on all the data on the sheet. But on the 300K row sheets, it can take 12 or more hours to run and there are times when things 'lock up' running the scripts on sheets this size.
I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):
NAMES-------THIS-------------THAT Mary-------some text--------some more text John-------<blanks>---------just text here Sue--------just some here-----<blanks> Dave-------something--------something else Adam------<blanks>-----------<blanks>
The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.
I have a volumes sheet and based on two criteria (in columns) am trying to pull the corresponding value from the April Results Table. I have product type in C2 (this varies down the column) and depot numbers in D2. The table i am trying to match has depot numbers in column A2:A93, and Product type in Row 2 (B2:P2), where the two criteria are matched, i need it to return the corresponding figure. I have tried various combinations of Index and Vlookups but i cant get it to work!
I have a External Data Table, but I wanted to add a extra column to it for comments. I just need to be able to write down what I did with that particular order or things like that. I did make a column, but the problem is whenever I update the table the comments column fields stay static I mean they don't move with their row. So it ends up the comment belonging to a row is now on another row.
is there a way I can tell excel to move the cell with the record or something like that.
I cant seem to figure out why this sumproduct formula wwill not pick up the datatable on a separate tab. Data table is Column E to AA but it only goes to Column Y. If I try putting AA in - the formula returns the VALUE#. I thought it maybe the cell formatting but it does not seem to the problem.
When i tried to drop another field into data field, by default, the new data field will appear below the field which was already in there. However, i would like the new field showing in an new column - horizontally - rather than showing in a new row.
For example: Rather than the Pivot table appear like this:
I wish to hide some columns that contain data used to update a pivot table in another worksheet. What I want to know is though, will this affect the pivot table? I think that graphs in excel will normally ignore data in hidden cells, so I was wondering if it was the same with pivot tables.
I have a table in which the rows represent cities and the columns represent certain characteristics/ attributes a city can have. In the cells every city & attribute pair receives a "grade"
City EatingBeachSki Barcelona 210 Chamonix 002
I would like to transform this so that every row represents a city & attribute pair and includes its corresponding grade.(I want to do this in order to be able to add more information about city- attribute pairs rather than about cities only)
I am aware that I can use a pivot table to sort out all the pairs with a specific grade. and then I could paste this into a new table. But my data base (number of cities x number of attributes) is huge and I was wondering if there is no automatic process to make this transformation.
I have attached a spreadsheet of an example of a database with a pivot table and a desired report Can anyone give me a pivot tabe from my database that looks like my desired report.
I am willing to seperate the database into two seperate databases with different transaction types if necessary.
I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.
My data is set up with an ID number, financial year and data across the columns e.g
ID F/Y Apples Pears 9999 2004/5 5 4 9999 2005/6 7 8 9881 2003/4 5 3
My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g
ID 9999 FY Apples Pears 2004/5 5 4 2005/6 7 8
I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.
I need to create a vba macro which will sort data according to a code it is denominated by and then will apply specific values from a code key to the data. I really need to have some help with this as although my vba is getting a bit better this is definately beyond me.
I am having trouble parsing the numeric data from the following table into separate columns. The five different numbers (ex. 70, 130,10,5,6 in the first row) represent nutritional info about the product. As you can see, the number length varies for the different categories. How to get these 5 numbers into their own columns using LEFT, RIGHT, MID, FIND, etc. functions.
I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).
I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).
In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.
Code:
Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here Sub FindFields() Dim List1(3), List2(3) Dim Count As Long 'Public variables (declared above)
[Code] ......
So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.
I have an Excel spreadsheet with a query to an Access table. The query pulls data from 2 columns in a table and pastes it to the spreadsheet. I am trying to update the VBA a little on one sheet and now I get the attached error whenever the code tries to run in Excel. Here is the code with the offending portion separated at the top.
I have 29 excel files with some number of worksheets from 1 to 4. The name of the worksheets are the same in all the spreadsheets. Then I've a got a pivot table. I have to compare some data (3 columns) from the pivot table to the numbers from all these sheets from 29 excel files.
I have a table of data which represents data in different categories by week.
My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :
06/04/2009 13/04/2009 20/04/2009 27/04/2009
is summed to make the data for the month of April.
The way I'm doing this at the moment is very long-winded
I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.
So in each "month table" there is the same list of week values:
06/04/2009 13/04/2009 20/04/2009 27/04/2009 04/05/2009 11/05/2009 etc.
but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them
This is an example of the forumula I am using in these tables: =IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")
Then a master table sums the totals for each month.
I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.
I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
