Compiling Large Sums Of Data
Oct 25, 2009
I have a large amount of data that I need to be able to summarize certain fields in order to be able to work with the data.
What I have is multiple lines which all pertain to one record and I need to be able to summarize these into one line - which would give me a sum in certain columns. I am working with thousands of lines of data.
There is about 10 columns in the worksheet. I want to be able to tell Excel that if certain columns all match, then to summarize these lines and give me a sum total on a couple of other columns. The data I am working with is invoice data, so I want to know if certain columns - ie - Vendor name, invoice number, cheque number are the same in each row, then to sum these records and provide a total for the invoice amount and cheque amount columns.
This sounds like a complex function to me but I am hoping that someone has a solution for me.
View 10 Replies
ADVERTISEMENT
Mar 8, 2006
I have 5 sheets in 1 wookbook with about 15 columns of varying rows of data. What I need to do is create a macro which collates all the rows of data, which with their own heading, onto 1 sheet so that it can be printed and distributed.
The problem is of course is that normal copy and paste won't work because the rows may vary and the macro need to take that into consideration. I guess probably an 'If not or' type command that would copy and paste the row so long as it had values then move onto the next sheet and continue from the last one.
View 9 Replies
View Related
May 13, 2014
I have 3 different sheets with data that I want to combine into one sheet using a formula. Here is an example:
Sheet A
Date Amount
10/1 $5
10/2 $10
10/7 $5
Sheet B
Date Amount
10/2 $7
10/3 $10
Sheet C
Date Amount
10/5 $5
10/6 $10
I want a fourth sheet that fills with all the data from these three sheets. Ideally it would be in order by date, but I understand that might not be possible. So the last sheet should look like this:
Date Amount
10/1 $5
10/2 $10
10/2 $7
10/3 $10
10/5 $5
10/6 $10
10/7 $5
The reason I want to use a formula of some sort is because if I add more rows to sheet A for example, I want the fourth sheet to automatically add it (whether it's possible to do in date order or not).
View 2 Replies
View Related
Sep 3, 2013
I've been asked to compile and sum weekly sales data. The problem is that the data was supplied in 52 separate tabs, for each week of the year (I don't know who on earth thought that would be the most appropriate way to send the data).
Is there any quick way to import all the data from each tab into one worksheet? The data is laid out the exact same way on each tab. All the row and column headings are the same, and in the same cells. Only the numbers are different (obviously).
View 2 Replies
View Related
Jul 7, 2014
I am working on a spreadsheet where I need to have data from different sheets added but based on the instructor whose data I want to see in the summary sheet. I want to be able to type the name of the instructor in a cell and have all of the data appear below. I believe that an index match formula would work, but I do not know how to have the data from the different sheets added nested in the formula.
View 2 Replies
View Related
May 17, 2009
I have been trying to compile some monthly and annual data from our main sheet to a FY09 sheet (for the fiscal year 2009). The 2 tabs in the uploaded example are the Distribution tab which we use to track the status of every item and the FY09 tab where I need the totals to be calculated for each month as well as the entire year. I have tried several formulas I found while searching the forum but I can not seem to get any of them to work, (I am sure it is because I don't understand them very well).
Since any formulas used will need to be copied 200 or so times, I would really like a VB solution which should also reduce the physical size of the file. I also tried a pivot table but I do not think it will show everything I need.
Basically, I need all the items separated by month on the FY09 tab. Then column 'F' on the Distribution tab needs to be summed up for each item in column 'D' of the FY09 tab for the respective month and multiplied by the respective item price in column'C' with the total value going in column 'E'. Column 'D' and 'E' need to be summed up for each month and cumulative for the entire year. There needs to be an average items and value for each month as well as for the year.
View 8 Replies
View Related
May 6, 2014
Here is my code thus far - it seems to go through the directory and copy data (single columns of 101 data-points) from the workbooks, but does not successfully paste it into the master-workbook (error message reads: 'paste method of worksheet class failed').
VB:
Sub LoopThroughDirectory()
Dim MyFile As String
Dim ecolumn
MyFile = Dir("C:Documents and SettingskbriemMy DocumentsErindi_i_vinnsluEXCELprufa1")
[Code] ....
View 3 Replies
View Related
Aug 11, 2009
I've been given the task of automating a spreadsheet to assess whether work has been done by each employee. Every employee has their own spreadsheet, where column A is a job code, and column H contains either y or n dentoting whether they have completed the task (y) or not (n).
The main spreadsheet is designed to show any outstadning tasks across everybody so it has a list of everybodies names in column A and then any incomplete tasks will be listed from columns b onwards next to the appropriate person. Here is what I have done at the moment.
View 3 Replies
View Related
Jul 22, 2014
I'm trying to put together a year-over-year retention report, with amount spent by new customers and also by returning customers. I have 2014 data, and 2013 data. Data for each year is a list of orders, consisting of a company number along with the amount spent.
What it basically boils down to is a count of different companies that have placed orders in 2014, along with the same for 2013, and then of the different companies who have orders in 2014, how many also have orders on the books for 2013 and how many are new.
And as a next step, how much have the retained companies spent, and how much have the new companies spent.
As an added wrinkle, the company field can also include blanks.
I've included an example below of the data I'm working with, along with the result I'm looking to recreate with formulas.
2014
2013
Company
Amount
[Code].....
View 2 Replies
View Related
Mar 30, 2013
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
View 1 Replies
View Related
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
Jun 22, 2006
i need code to find name and compile the value cell next to it...This is how it goes..
File A (2 columns) consist of name,Cash and the name tends to have many repeats (for some reason)
i want my file B to let user first key in a name(which should be the same as File A) and Compile all the cost of that particular name and display in a cell in file B
View 9 Replies
View Related
Feb 19, 2009
I have a row of text (for example say they are cells c3, d3, e3, f3) that have one of four text strings (red, yellow, green, blue). In cell b3, I need it to report back the column heading of all the cells that have the text "blue".
So if cells c3 and f3 have text "blue", then I need their column headings to be inserted into cell b3.
View 9 Replies
View Related
Aug 10, 2006
I have several tabs in a spready, and I want to be able to compile a range of cells from each tab (constant selection of cells from each tab) together as a list in a new sheet.
The list needs to be able to be populated in any order and with repetition.
I'm thinking some sort of sheet for selecting which tabs to bring in info from would be useful, and then a command button to compile all the info into one list on a new tab...but I have no idea how to write the code for it!
View 3 Replies
View Related
Aug 14, 2014
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
View 14 Replies
View Related
Jan 20, 2009
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.
View 9 Replies
View Related
Aug 6, 2003
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
View 9 Replies
View Related
Jul 27, 2009
I have a sheet called "Usage Data" and in that sheet Column A is called "DTL_LOC" (Warehouse #)
I need to pull all data from this column and create a list with no duplicates. Data is both Text & Numeric.
I need to use this information in a Validation List on my "Inventory" sheet in C1
The list is currently located in L3:L102 on the "Inventory" sheet. However this list may increase or decrease as the company grows.
View 14 Replies
View Related
Jul 30, 2009
I'm creating an InputBox that pulls from various cells in my worksheet to create a sentence as the default value in the text-space. For example:
A1 = Policy Number
B1 = Credit Amount (i.e. $10)
C1 = $ Info [i.e. if credit is for one month (1 x 10), if two months (2 x 10)]
D1 = Date Policy Ended
E1 = Months Excluded from Bills
Thus, the sentence that the InputBox would display would be:
Today's Date + A1 + B1 + C1 + D1 + E1
Date - Policy # - Credit Amount - Info - Date Ended - Months Excluded
A text example would be:
30JUL2009 - 1234 - $10 - 1x10 - 01JUN2009 - Exc Jul,Aug09
The issue I'm having is how to have the InputBox macro ignore one of the cells if it's empty. My current code is:
View 6 Replies
View Related
Mar 6, 2012
I have a file with 26 worksheets, each have four columns and the last column is averaged at the bottom, and I'd like to make a summary worksheet tab showing the name of the table, the number of records, and the average score of all 26 sheets.
The problem is, some worksheets have 20 rows, some have 1400 rows. I could go choose the data manually, but isn't there an easier way to do this?
The table name is merge/centered across the four columns, and is the worksheet name (using a formula I found on here). Then the four columns have headers, then data. The last row of each worksheet doesn't have the normal data in it, but says "Records Counted," then has a COUNT of the rows, then has "Average," then has an average of the column above.
View 1 Replies
View Related
Apr 23, 2009
I have looked all over the place but nothing seems to work for me.
In detail, I have a quote book i made with two sheets for different types of items on them. For example:
Sheet 1 is for wooden items, and sheet 2 is for metal items.
So after i enter all the items on sheet one and sheet two, I want to be able to combine all the items from both sheets into one summary sheet that i can send to the customer.
I tried consolidate, and messed around with pivot tabels but it didnt seem to be what i'm looking for.
View 9 Replies
View Related
May 2, 2013
I am compiling an Excel programme with two sheets, One sheet is constantly being updated with data from a website. The download from the website is quite messy and I have to sort through the relevant data. I need to compile two lists on Sheet two that will take the Name from Column A1 and Value from B1 on Sheet 1 but only If the value of C1 is above 5 or below 0 (negative).If the value isn't correct, it will ignore that row and skip down to A2 B2 C2 etc. Hoping to get two tidy lists on Sheet 2 with no blanks/gaps.
View 1 Replies
View Related
Aug 22, 2013
I do not know what function will do what I need. I am compiling info from different sources to make a mailing list. Basically it boils down to the following. Sheet 1 has Names and Parcel Numbers, sheet 2 has Parcel Numbers and Addresses. I am trying to combine it in a way that I have names and addresses on the same sheet.
View 2 Replies
View Related
Mar 8, 2009
Need to produce a formula to reference a large amount of data and pull one month at a time:
View 7 Replies
View Related
Jan 14, 2014
I have a list of IDs that recurs over a time period. It consists of a Start Data an ID Number. These IDs recur over and over again through one month with different start dates.
See Below:
Date ID
12/1/2013 10:00:00 AM 67890
12/6/2013 12:00:30 PM 67890
12/18/2013 06:30:05 AM 67890
From Another List I'd like to pull a max enddate that is within 24 hrs of the start date. There will be multiple end dates. Here is what the other list would look like.
End Date ID
12/1/2013 1:00:30PM 67890
12/6/2013 4:00:45PM 67890
12/18/2013 9:30:00 AM 67890
Seems like using vlookup with the ID as the lookup wouldnt work because it would just pull the first date it found over and over again.
View 9 Replies
View Related
Aug 1, 2014
I have several workbooks (5) with the same variables (columns- A:Q) but with a changing amount of rows (2:n, not including the headers). Each row corresponds to a date range (usually a week) for a particular person (up to 40 people) plus a few other values.
I would like to have a way of "merging" or "compiling" the 5 "seed" workbooks into 1 "master" worksheet. Where rows 2:n of each of the 5 "seed" workbooks are added to the master without any duplication of the same name-date range combination. Also, the master worksheet should not include the rows which only contain a name and date range but for which all the other variables are zero or missing.
Each "seed" workbook would have a button that sends the data over to the "master" worksheet.
Is this a really difficult project? Feasible for someone with near to zero VBA experience?
I attached 3 files to show you what I mean. The 2 "seed" files are merged into the "master" file. Please note that in the files only 2 names are used, but the "seed" files could contain any combination of 40 some names. Also note that the length of rows which contains data in the "seed" files is variable, although it should not be longer than 16 rows + the header row.
View 14 Replies
View Related
May 29, 2014
I have a very large spreadsheet that was exported from an ecommerce site with close to 1000 products. I have one column that I need to extract some text from. This column holds all of the html from the product description and is huge. I only need to extract the actual description of the product, but am having a very hard time figuring out how to do it. I've tried using the mid, left, and right function; but not all of the html is the same so it's not really working the way I need it to.
I have multiple tags throughout the html that I can use with the mid function, but there is more than one occurrence of them. So, how can I tell it to start at the 4th occurrence? I've spent countless hours searching, but I'm a complete novice when it comes to excel and I don't even know what to search for. I end up looking through sites that explain how to pull the Y out of XYZ, which is what I need, just on a much larger (and more complicated) scale.
It was suggested that I set up a macro that will find the 4th occurrence of the word, and then uses the mid function to pull the data out, but when I try to find the word, it says it doesn't exist even though I can see it right in front of me.
View 10 Replies
View Related
May 29, 2014
I have enormous amounts of data to sort through (as we all do) and I am trying to link them to unique identifiers for easier data maintenance later.
What I want to do is vlookup from the 'solved' sheet a code, which will search the 'data' table and return the 'DFU' code.
Example 2905.xlsx
View 5 Replies
View Related
Nov 11, 2009
I need to find the most frequent number for each name in a large datatable, eg. Name1=12.
ColumnA / ColumnB
Name1 / 12
Name1 / 11
Name1 / 12
Name2 / 100
Name2 / 105
Name2 / 105
Name2 / 98
Name3 / 14
Name3 / 14
Name3 / 2
NameX / ..
Since Pivot tabels cant do the trick as far as I know, i'm clueless of how to proceed.
View 10 Replies
View Related
Dec 3, 2013
I have a large list of in rows with multiple column headings: ie. Name, date, amount. I need to generate a separate list whereby the rows that correspond to a given criteria are extracted and listed sequentially.
I have attached a simple example to demonsrate what i mean.
in the attached spreadsheet, i have a column of colours, and a column of objects- RED book, BLUE hat, etc.
I'd like the criteria that i type in the green cell (a choice of colour), to generate a list of all the items that match that colour, and be displayed in the orange cells. So, if the green cell says BLUE, the list should pick out the BLUE dog, BLUE hat etc.
View 1 Replies
View Related
