Fastest Way To Consolidate Large Data
Jun 20, 2014
I have a large data (more than 50k rows) in a spreadsheet and i want to consolidate the information's.
here is the sample source data.
Source Data
codeamt1amt2amt3
123456$16.00 $0.00 $0.00
789011$0.00 $18.00 $0.00
123456$12.00 $5.00 $0.00
123456$0.00 $0.00 $7.00
111111$11.00 $1.00 $3.00
789011$22.00 $0.00 $0.00
and the output should be.
Output
codeamt1amt2amt3
123456$28.00 $5.00 $7.00
789011$22.00 $18.00 $0.00
111111$11.00 $1.00 $3.00
currently i'm using the consolidate function of excel inside my vba (which is working fine) but i took forever before it finished, i'm just wondering if there is an alternative way which is more faster.
View 8 Replies
ADVERTISEMENT
Dec 21, 2012
I have a range of purchase order rows, with the formulas stored in the first row (TemplateRow) which is hidden. The users may add any number of rows to this range, depending upon the number of different products being purchased.
Code:
'Copy the template row into the first newly inserted row
Rows(TemplateRow).Copy Destination:=Rows(insertionPoint)
'fill down from the inserted row down to the last new row
With Rows(InsertionPoint & ":" & NumberOfInsertedRows.Rows.Hidden = False
.FillDownEnd With
The problem is copying the formulas down to the new rows can take terribly long (minutes) in scenarios of thousands of products. Is there a faster method of copying down my formulas?
View 9 Replies
View Related
Oct 17, 2008
I have a workbook that has multiple tabs and need help trying to figure out how to consolidate all the data. I find myself spending hours doing this manually each day.
Here is what I have:
Workbook has tabs labeled....Wk1_Mon, Wk1_Tues, Wk1_Wed, Wk1_Thurs, Wk1_Friday, Wk1_Summary......and repeats all the tabs through Wk5....then I have a Month_Summary tab.
I have 25 users with 25 seperate workbooks each with individual information on each workbook.
I am trying to get a sum of all the data on the Month_Summary tab for each month for each user and as well as a sum of the Month_Summary tab for all 25 users.
The end result I am looking for is to get a Yearly Sum of all the Month_Summary Tabs for all 25 users as well as individual yearly summaries for each users.
I have one main Folder which contains 25 folders (one for each user). Under each user folder there is a seperate Workbook for each month.
View 2 Replies
View Related
Jan 12, 2009
I currently have almost 20,000 rows of data and I am filting down to a particular value in column D. However, as the filter runs you can see that after it finds the fields it keeps running like it should to the end to make sure there are no additional values. Is there some faster way to search this many lines? Would it be faster to sort the worksheet first and then do something with a sorted worksheet or what are my other options?
View 9 Replies
View Related
Nov 28, 2013
I have a plot of data in a chart. I've added a worksheet to the same workbook which has data that I want added to this plot. The data is in the SAME columns/rows, the only difference is the worksheet name. I'm looking for the quickest way to add the data to this chart as I have to do this many number of times. Perhaps if possible to somehow copy the
=SERIES("Title",sheet_name!$A$11:$A$18882,sheet_name!$R$11:$R$18882,5)
And paste it back in with just a sheet name change?
View 5 Replies
View Related
Jan 21, 2010
for example i have this data in rows B and D:
austriaaustria
icelandaustria
austriaiceland
austriagermany
icelandaustria
germanyiceland
germanygermany
germanyiceland
germanyiceland
germanyiceland
germanyiceland
germanyiceland
austriaiceland
austriaiceland
and need to input in rows A (date in this example) and C(number in this example) which are adequate for each country so to look like this in rows A,B,C,D:
19 20 austria1,25austria
20 30 iceland1,25austria
19 20 austria2,2iceland
19 20 austria6,6germany
20 30 iceland1,25austria
22 00 germany2,2iceland.......
View 9 Replies
View Related
Dec 8, 2013
I'm looking for a way of keeping case sensitive data in a range of cells, before using Data Consolidate, which when merged afterwards, Consolidate removes the case sensitivity and combines the quantities into one.
Check out this simple table as an example >
A
B
C
D
E
F
G
H
1
TEXT
QTY
TEXT
QTY
TEXT
QTY
TEXT
QTY
2
and
3
and
2
and
10
and
5
[Code] .........
Cells A1-B7 and C1-D7 are two sets of original data, before consolidation.
Cells E1-F4 are the result of applying a Data Consolidate operation to the A1-B7 and C1-D7 ranges - note how the merged result ignores the case sensitive condition in the original ranges.
Cells G1-H7 is the post-consolidation result that I'm looking for, where the original text case is maintained.
View 3 Replies
View Related
Apr 25, 2013
I have a list of entries, people log how many products they sell a day, at the end of the I need to consolidate that data, to get a sum of how many goods each person sold that week. So on on tab I will have multiple enetries by the same person and on the consolidtaed tab, I want each person's name and Id # to show up on one row, but them I would like a total of how much goods they have sold for that month. I have attached a copy of my file, the consolidated tab shows what I would like to get in the end.
View 2 Replies
View Related
Dec 8, 2008
This should be easy but after searching "consolidate" all that I could find was multiple sheets and complex questions.
I have 5 data columns that I need consolidated into 1 single column. I've tried the consolidate funtion but it appears to only work with formulas and numerical values (unless I cant find the proper command.) I've also tried paste special but it populates the cells following.
I need 5 cells word values pasted into a single cell
View 12 Replies
View Related
Mar 19, 2013
I need to pull a data from 3 different SharePoint lists and present it in a new data sheet. So far I know how to create data connections and I am able to see everything in 3 different worksheets but in the same workbook. Also if we have the same Project and IT number we should pull information just from the SharePoint 1 list.
View 1 Replies
View Related
Jan 25, 2012
I have been trying to find a way to consolidate data in two tables, but can't seem to locate a good response. I have been toying with EXACT, VLOOKUP, HLOOKUP, MATCH, and SUMPRODUCT, but none seem to work for my issue.
Two Files - Call them acrendo.xlsx and office.xlsx
Both have a single sheet and contain first name, last name, and date columns. Office also has other information that I'm trying to merge into the corresponding row of acrendo.
Basically, I am trying to take the combined first name, last name, and date column from a row in acrendo as a single record, look for an exact match in office, and if it finds one, return the corresponding values in Coumns D, E, F back to acrendo. Someone suggested using the following sumproduct formula:
=SUMPRODUCT(--(A2=[ofc.ally.xlsx]ofc.ally!$A$2:$A$9329),--(B2=[ofc.ally.xlsx]ofc.ally!$B$2:$B$9329),--(C2=[ofc.ally.xlsx]ofc.ally!$C$2:$C$9329),E:E)
This always returns a value of 0, even if there is data in the matching row to be copied over.
One small complicating matter is that there may be duplicate entries in office (i.e. one row in acrendo may match to four rows in office). I can cleanse that if necessary, or preferably, if multiple matches are found, it would simply insert the additional rows into the acrendo file.
Also, is there a way to go through the acrendo file and check for duplicate records and delete them (e.g. if there is the same last name, first name, and date, delete the duplicates).
Can't do this manually as I have acrendo has almost 18,000 rows and office has almost 10,000 rows.
View 1 Replies
View Related
Sep 11, 2012
I am trying to consolidate multiple data sets in one worksheet into one list. An example of the data sets is below:
Product1
Company1
Product1
Company2
Product1
Company3
Product2
Product2
Product2
Product3
There are over 50 data sets in the worksheet with exactly the same number of columns. However, when the data is updated, the number of rows for each data set can change.
The output table is below:
Product1
Company1
Product2
Product1
Company2
Product2
Product3
Product1
Company3
Product2
I am assuming it is a loop function in vba to loop through all of the data sets in the worksheet, but I have limited experience with vba to know for sure.
View 4 Replies
View Related
Oct 5, 2006
I have attached a sample workbook to show my layout.
I need to link multiple sheets (but not all, just certain ones) to one "master sheet". More specifically I need to link only certain colums from each certain sheet. The sub sheets have detailed information, but I only want basic information on the master sheet like FirstName, LastName, Ext, Buyer#, etc Not every column from the certain sheets is needed.
I need a way to update this master sheet when I update the sub sheets manually. So if I add a employee record to a sub sheet, the master sheet is updated. These updates aren't often, so running a macro to rebuild the master sheet after I manually delete it wouldn't be out of the question, whatever is convienent.
In the attached worksheet, I believe there is a sheet called Items. I don't need any information from that sheet at all to be on the master sheet so it can't be in the array when the master sheet it built from the sub sheets.
View 6 Replies
View Related
Mar 23, 2007
I would like code to consolidate weekly hours worked, on each project, for each person. I therefore wish to end up with 1 row per person, per date, showing their GRAND Total Hours worked in column P (across all projects, and across all tasks). So, in the example below, everybody's rows would stay as is, apart from Mike Jones's, which I would like to be consolidated into one row, showing a GRAND Total Hours worked of 16.
View 3 Replies
View Related
Apr 3, 2007
I have attached a compressed excel file containing 1,2 and final worksheets. How do i consolidate all the data in their respective B columns of 1 & 2 to the B column of "final". In reality i actually have more than 5 worksheets to be combined to a single final column B.
View 9 Replies
View Related
Sep 7, 2007
I need a macro code to combine data from 2 different workbooks to a new workbook.
See attachments for ease of understanding.
Note:
- Data in sheet1 of both book1 and book2 should be pasted in a new book (eg: book3)
- The result I am looking for is like sheet2 of book1 (colour differentiation done for ease of understanding and not required to be implemented in the code). Also since I could attach only 2 books, I have pasted the desired result in sheet2 of book1.
- Also note that headings of book1, book2 and the resultant book3 will be same.
- Both book1 and book2 are stored in the same folder (im not sure if this info helps)
- It is important to note that data keeps changing and new rows will be added in either book1 or book2. So when I refresh the resultant book3, it should add these rows that were previously missing.
- First it should paste all data from book1 (old and new rows), then it should paste all data from book2 (old and new rows).
- I think the sheet2 of book1 will help you to understand what I mean by the above note.
The above exercise is for me to get a logic. In the actual scenario, there are more than 2 books to pull the data from. And also there are more than 20 columns to pick data from.
View 4 Replies
View Related
Jan 7, 2009
I have a workbook with 7 different worksheets (site_worksheets) containing data about various sites. I need to copy all this data into a single worksheet (worksheet_a). I want worksheet_a to update itself when another row of data is added to any of the site_worksheets.
View 12 Replies
View Related
Jan 31, 2013
Every month I have to do a report that takes information from four other excel spreadsheets. The spreadsheets are all the same format and contain information on community events. I have to run a report that shows what events are happening in the following month. Currently I copy and past from all the relevant data from each spreadsheet into one. Is there and easy way of making all this information to come together every month.
View 4 Replies
View Related
Oct 8, 2008
I have two worksheets and need to consolidate the relevant data into one worksheet.
On both worksheets there is one column that I can relate to the other worksheet, called "TKR", which contains a code in each field, such as "000210 K", or "004170KS". Both worksheets have this column. In the first worksheet, called POS, each "TKR" code tells me that this is a relevant "TKR" code in the DB worksheet. I am not interested in the data in POS, just the code to tell me which records are relevant in DB.
In DB is the information I need to extract. But in this worksheet, there are many irrelevant records, which I need to ignore. I need to reference the data in the TKR column in the POS worksheet with the data in the TKR column of the DB worksheet, then extract the record from the DB worksheet and place it in a new worksheet, along with each other extracted record, compiling a consolodated list. I could do this by cut and paste, but I have 30 different databases, both POS and DB, each containing up to 6000 records.
In the POS worksheet there is more than one record for each "TKR" code. I can run an Advanced Filter to extract a consolidated list of "TKR" codes. I am stumped however on how to then use that list to reference the same codes in the DB worksheet and extract the records to a new sheet.
View 5 Replies
View Related
Jan 29, 2014
I have 2 pivot table in 2 different sheet and i want to consolidate the data in one sheet.
Pivot table:1(order processed) as below
date
MR X
MR Y
MR Z
MR S
[Code].....
I need the data as below in separate summary sheet. 2 column in each employee(1 is processed and completed), i dont have option here to create 2columns here hence i put "/" in between.
date
MR X
processed/Completed
MR Y
Processed/completed
[Code].....
View 2 Replies
View Related
Jan 29, 2008
Here is a sample Excel sheet I have: http://spreadsheets.google.com/pub?k...dnD01VPV8RzOAw
I have over 1,200 rows with duplicate data(sometims 3-4 times) that is filled in some columns and empty in others.
I would like to consolidate the data and then delete the duplicates without loosing any data.
Is there a way to do this? I have Office 2007. I can't just use the Duplicate finder cause that just deletes everything right away.
View 9 Replies
View Related
Aug 20, 2009
I am trying to take a workbook with 10 worksheets and have the contents copied and pasted into a new worksheet without any extra blank rows onto a new spreadsheet. Does anyone know how to achieve this? The purpose is to copy the information off each of the 10 worksheets and be able to use the consolidated information for pivot tables.
Important Facts:
- Each worksheet starts with data in cell A7 and ends at BL7
- The number of rows on each worksheet is inconsistent (e.g. one sheet ends at row 12 another at row 101)
- The columns have uniform titles and data below
View 9 Replies
View Related
Nov 10, 2009
I have a worksheet that was created by converting a pdf file of 40 pages or so. The conversion process created 40 separate worksheets in one workbook. I would like to consolidate all the data on to 1 large worksheet.
View 9 Replies
View Related
Sep 30, 2006
I'am have one data file with so many sheets those need to edit and bring them to final summary sheet.
I dont know how to move between sheets and edit them automatically.
Editing will need to add the brand name next to the variance name and the sheet name in one new column. And bringing those rows with completed data only to All sheet from those sheets in workbook.
View 7 Replies
View Related
Dec 26, 2013
I'm having trouble at getting the overall fastest time to show up. There are tree racers in two heats. Also the times are set up like 0;00.000 because i cant get it to work like MM:SS:MS .
Daytona GTR34.xlsx
View 5 Replies
View Related
Nov 22, 2006
What's the fastest way to accomplish the following:
I have many of these in my code and inside nested loops, so they significantly slow down my code
jRow = Worksheets("Sheet4").Columns("O").Find("orange", _
LookIn:=xlValues, LookAt:=xlWhole).Row
View 9 Replies
View Related
Jan 24, 2014
I have a data that needs to be ranked from highest to lowest. I used the Rank function to do this.After getting the rank, i need to consolidate the data by highest to lowest rank, while simultaneously getting the values of the column on the same row.
I used the index and the match function. And i was able to do that, but the problem is there are items that have the same rank. how do i consolidate the data from highest to lowest even if with same ranks?
View 5 Replies
View Related
Jun 4, 2014
In the attached spreadsheet, I have the original data display horizontally (sheet2). Col A is Patient #. The header in row 1 are the test codes. Each patient took only 1 test and have result reported either neg, pos, pending or not eval. How do I transpose the header and have the test results consolidated in 1 column accordingly as display in sheet 3.
View 4 Replies
View Related
Jul 23, 2014
Trying to consolidate and Merge Data on a Separate Sheet
View 4 Replies
View Related
Jan 27, 2014
how to consolidate all data from all worksheets into a single worksheet(master sheet)?
Untitled.png
In the attachment, data for worksheet 1 will be added to the masterlist and worksheet 2 will follow.
View 3 Replies
View Related
