Excel 2003 :: Extracting And Collating Data From Multiple Columns
Apr 27, 2014
The link to my file is: [URL] ........
I am using excel 2003.
My query goes like this:
I need to find out "total lifted quantity"(i.e. the sum of 'first state lifted quantity'+'second state lifted quantity'+'third state lifted quantity') for each "state" for each "size" and each "grade".
However when I am applying a pivot table (as seen in Sheet1) it is not giving data in lucid form. I don't want to split the data for three states in three different tables (as seen in Sheet 4).
View 1 Replies
ADVERTISEMENT
Dec 31, 2013
I have sheet containing a schedule of data. It is a record of changes on a project, each change is sequentially numbered and contains a row of data (date of change, whether approved, cost of change etc). Each change is given a cost centre reference (1 of 10 cost centres are being used) depending on the type of change being made.
I need to keep that record of the overall schedule of changes but I also want to extract each of the cost centre categories into a sub table on the sheet to give a schedule of changes against each category.
I have almost achieved what I want but one method leaves blanks in the sub table which I want to avoid and the other method I used to remove the blank lines but needs the first method sheet calculated before it then goes to work and seems rather cumbersome and I suspect there is a much easier method. (I also want it to be compatible with excel 2003 so it can be used across several platforms.)
View 7 Replies
View Related
Apr 13, 2007
I have built a timsheet template which I intend to issue to c.140 personnel in my company to fill in for the month of May. On receipt of these timesheets, I will save them all in one, specified folder. I would then like to consolidate the total number of hours worked by adding cell N53 from each timesheet received. Is there any macro/ VB script which can add the contents of a particular cell (N53 in this case) from all the spreadsheets contained within a particular folder?
View 2 Replies
View Related
Jun 27, 2014
I have Excel 2003 and use the following code for 50 consecutive columns, using each column's Row 1 as the multiplier. Each column has over 20,000 rows. It works, but I'd like to know if there's a quicker and / or neater way to do it.
Code:
Range("bo1").Copy
Range("bo2", Range("bo" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Range("bp1").Copy
Range("bp2", Range("bp" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
View 5 Replies
View Related
Jul 14, 2014
I am using Excel 2003. I have a spread sheet and I am trying to use conditional formatting. But I can't figure out how to use conditional formatting across multiple columns and so I have been doing each column individually which has been kind of a hassle. I also am trying to figure out how to put in a formula so that every occurrence of a certain text will show up a certain color. I know that I can just do "cell value is", "equal to" and click on the cell, but the information in this spread sheet is organized alphabetically and is going to be added to frequently therefore every time I add information it re-organizes. This then messes up my conditional formatting because the text value that was in the cell gets bumped up or down.
View 2 Replies
View Related
Apr 4, 2014
I am trying to create a Chart from a Pivot Table. I want the Chart to show my projects and present each months amounts side-by-side comparing (Plan, Actual, Forecast) data each month for the calendar year.
I am trying to figure out how to generate the pivot table showing multiple projects so that the data is grouped by month comparing (Plan, Actual, Forecast) data. Projects are listed down the Rows while months Jan-Dec are along the columns. Each month has 3 columns because the second row contains header for the data sets of Plan, Actual & Forecast. (see sample below) When I try to create a Pivot Table, It shows multiple month fields (Jan, Jan2, Jan3, Feb, Feb2, Feb3) instead of a single month.
{Using Excel 2003}
NameOwnerJan JanJanFebFebFeb
PlanActualsForecastPlanActualsForecast
Project 1Region 1 126.1 119.6 119.6
Project 2Region 2 18.0 0.9 0.9 8.2 8.2
Project 3Region 2 80.0 2.6 2.6
Project 4Region 3
Project 5Region 3 60.0
Project 6Region 4 55.8 55.8 55.8
Project 7Region 4 19.4
View 2 Replies
View Related
May 31, 2014
how to extract data in the following case: I have the following columns:
Employment Name
code1
code2
cost1
[Code]....
how can I do this using formula ? For one sheet and for extracting this data from multiple sheets into a new one in the same workbook?
View 6 Replies
View Related
Nov 24, 2013
I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.
Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?
Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?
View 1 Replies
View Related
Mar 20, 2014
So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.
I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.
I don't have Excel 2007, only 2003 I have.
View 1 Replies
View Related
Sep 17, 2013
Date: 17/09/2013 KO: 19:45 Ref: B Malone Att: 7,574 extraction of text from the above text string which is say in A1. What l need is for Date to go in A2, KO in B2, Ref in C2, & Att in D2. I'm using Excel 2003
View 7 Replies
View Related
Aug 12, 2008
I have attached an excel spreadsheet. I am trying to automatically calculate the data in the boxes, (Without using a Pivot Table) but have tried a variety of ways with COUNTIF, COUNT(IF()) and SUMPRODUCT, with confusing results. I managed to get a query to work with SUMPRODUCT, however it does not accept Wildcards (*) in the text reference. I has spent over 8 hours, and even tinkered with Pivot Tables, all without success - The data I would like to extract in the columns for summary is: Department * Logs <7 Days old *Average Log Age * Logs >7 Days Old * Average Log Age *
View 6 Replies
View Related
Aug 14, 2009
I have a set of workbooks, with multiple sheets within each which I receive each month from field units. The formats are identical.
What I want to do is to extract data from one column on one specific sheet within each workbook to a summary sheet on a new workbook. In the snapshot below, I would like to collect data from the "Actual" column (in yellow) and then paste it on a summary sheet.
Monthly reportingEntity 13. Risk reportingJanuaryMarket Risks - LoansJanuaryTargetActualVarNumber of competitors 110%Market share 1%1%0%Ranking in market 110%Concentration of portfolio – region-wise (number)1%1%0%Concentration of portfolio – region-wise (value)1%1%0%Market growth rate 1%1%0%Inflation rate 1%1%0%
This would appear in a new summary worksheet as below: (the cell labelled "Entity 1" would then appear as the column header in the summary sheet.)
Monthly reporting - summary sheet3. Risk reportingTarget rangeEntity 1Entity 2Entity 3Market Risks - LoansActualActualActualNumber of competitors 1Market share 1%Ranking in market 1Concentration of portfolio – region-wise (number)1%Concentration of portfolio – region-wise (value)1%Market growth rate 1%Inflation rate 1%
The steps I imagine are:
1. Open all workbooks in a specified folder (folder name upon prompt)
2. Search each workbook for a sheet titled "Risk Reporting"
3. Copy the specified cell (for the column header) and the specified column into a new sheet in a new workbook
4. Move on to fill up the next column, and so on ...
Also is there a way to order the copying so that the columns always line up in a specified order? (e.g. Entity 1, followed by 2, followed by 3 ...). One way I guess is to fix the column headers in my summary sheet, and then fetch the data from the corresponding worksheet, by matching the names.
View 9 Replies
View Related
Jan 3, 2013
I have a questions regarding extracting records using a partial text search in multiple columns.
I would like to do a partial text search of the letter "j" in the Name column, and search for the number "1" in the Phone column and extract the corresponding records.
The formula I have been using is as follows: =IF(ROWS(C$2:C2)>$B$2,"",INDEX(C$2:C$6,SMALL(IF(AND(ISNUMBER(SEARCH("J",$K$25:$K$29)),
ISNUMBER(SEARCH("1",$L$25:$L$29))),ROW($J$25:$J$29)-ROW($J$25)+1),ROWS(C$2:C2))))
However, the formula is not working correctly.
find my sample data below:
A
B
C
1
Partial Text Search
[Code]....
View 5 Replies
View Related
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
May 23, 2012
I have come across a problem which delivers 0 as a result, I am doing sumifs which i never had a problem before with, however I am trying to include a cell which is variable by using the & in collating data, the raw file also contains that criteria in 1 column so I have no mismatch.
View 5 Replies
View Related
Jul 3, 2012
how to write a macro to copy a set of multiple columns (non-consecutive) from a specified row, onto a new sheet. Ie, it is always the same columns, while the row changes with the imported data.
Specifically, these are the columns I want to select, Range("B:B,D:D,F:F,H:H,J:J,L:L,N:N,P:P").Select
Moreover, I want to specify which row to copy from, in a cell (ie, "11"). So that when I run it, values from B11,D11,F11...P11, will be copied.
View 4 Replies
View Related
Mar 29, 2012
I have a large number of product descriptions of varying lengths (column A) which I need to split into a maximum of 3 columns depending on the total length of the description. Each description in column A is less than 90 characters. Each column (B, C & D) can only be a maximum of 30 characters including spaces and commas etc. Also words cannot be split. Below is a sample of the result I would expect with the description in column A and the 3 extracted columns in B, C and D. I am using Excel 2003.
View 4 Replies
View Related
Dec 19, 2012
I would like to have Excel 2003 be able to highlight the row if there is data in certain columns. Specifically if there is a number greater than zero.
For example the following could work:
[CODE][=OR(LEN(H9),LEN(I9),LEN(K9),LEN(Q9),LEN(R9),LEN(S9),LEN(W9),LEN(X9),LEN(Y9),LEN(AB9),LEN(AD9))/CODE]
However, there are times when I need to add a column/criteria. Thus I would have to go into conditional format and redo all the rows in the worksheet. Is there a smarter way to do this?
I was hoping to provide a list of cell address from the header or better than that look for specific numbers in the header row and if there is a number greater than zero, then highlight the entire row.
There is no specific pattern as to which columns would trigger the requirement.
Also how do you keep the ranges from moving when copying across and down. Sometimes I run into that issue. Maybe highlight the entire row and then enter the conditional format?
View 9 Replies
View Related
Feb 11, 2014
I would like to loop through cells row wise.
Columns which have data are A:E
modify below code to select the range from columns A to E?
[Code] ........
View 3 Replies
View Related
Aug 8, 2012
I'm using Excel 2010 on Windows 7.
I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.
Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.
It would look something like this
Sheet1
Lastname
Firstname
Device
Johnson
[Code]...
So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:
1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.
View 6 Replies
View Related
May 9, 2012
How do I compare 2 columns and then flag where there is duplication? I want to compare Column B against column C and then flag everywhere there is a match. In this case, Column B may have several thousands of entries (inclusive off all entries found in Column C) and I want to have Excel look through the thousands of entries in Column B and then just place an "x" where there was a match to the values found In Column C. For example:
Excel 2003ABC1Duplicate?MarchApril27899421458347889122254x21458112575445576332147x122258x11257921463Sheet1
View 9 Replies
View Related
Oct 2, 2011
I have two columns. In one people either saw Red, Blue, or Green. In the other people either chose One, Two, or Three.
I would like to get excel to read the first column, find all the times someone picked red, then look to the column next to it and see if they picked the number One. Then I would like it to count that situation. Then I want to do it for Two and Three. In the end I would have something that looked like this
---------One----Two---Three
Red-------5------8-------9
.
Blue-------4------5-------3
.
Green-----4------2-------6
Generated from something that looks like this
Red One
Blue Two
Blue Three
Green Two
Some kind of biconditional countif maybe
View 1 Replies
View Related
Jun 12, 2014
I need to extract numbers from excel cells. For eg-
BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694
^^^ This is one of the cells. I need to pull out 04.00, 2.25, 26.50, 1340 into different cells from one given cell.
I have tried using =MID() function, but I need to make changes into that formula everytime the position of first numeric letter changes while I go along a column. And I need to make changes into it as I go along the row.
It is taking me a lot of time in extracting numbers from one cell into different cells and I am afraid that I will spend the rest of my life getting this stuff done.
View 9 Replies
View Related
Sep 27, 2013
I need to find if there are matching addresses in 2 different excel files. If the same address appears in both files, I would like the new worksheet to return the address along with the sale price from the 1 file and the rental amount from the other file.
I have tried using vlookup but the problem is the exported data file contains the street number in one column and the street name on another column. I have attached a truncated example of both the rental data and the residential sales data.
View 5 Replies
View Related
Mar 8, 2013
I have a long list of data with many columns and I'd like all the information to be in one column without manually copying and pasting each column and adding to the first column. The data has different amounts of rows and columns as well. An Example is below. I'm using Excel 2010. Is there a formula or something for this? This isn't the data I'm using but just an example since I do this frequently.
Data Looks like:
54654
31233
42343
51234
66968
43252
54657
63253
[code]....
Would like to look like:
54654
66968
79282
91596
68185
31233
[code]....
View 5 Replies
View Related
May 8, 2013
My data is in column A. I need to have the data as in column E and F. Column E is the field names.Rows can be up to anywhere and may very.
Excel 2007
A
B
C
D
E
F
View 8 Replies
View Related
Aug 5, 2012
Have Excel 2003. I have
List of email addresses in column a
List of email addresses in column b
** I would like a formula that will take all the values in column A and compare it to the entire list of entries in column B.
Would like it to show in Column C any entries in Column A that ARE NOT in the entire column B.
*Would like it to repeat for entries that are in Column B, but not in Column A and show in D.
*Then would like a count of the differences for each column (that is pretty easy).
It needs to look at the entire list of entries in the column as these will be email addresses. We want to know what is missing from Column A that is not in Column B and what is missing from B that is not in A.
View 3 Replies
View Related
Jan 29, 2014
I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.
formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?
View 2 Replies
View Related
Oct 20, 2012
I have a table in A2:F200 and want to sum the entries in column C for those rows containing both an F in column E and an L in column F. I thought this would be easy but I cannot figure it out.
I use Excel 2003 so SUMIFS don't work.
View 2 Replies
View Related
Jun 6, 2014
I am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:
AAA
AAA - description
BBB
BBB - description
CCC
CCC - description
and so on..
I would like to move the descriptions from column A to column B:
AAA AAA - description
BBB BBB - description
CCC CCC - description
and so on...
What would be the best way to accomplish this?
View 4 Replies
View Related
