Sheet1 contains a large set of data, including a date and a corresponding value.
Sheet2 (Summary) has a column called "Begin Date" and a column called "End Date". How can I use a formula to sum every piece of data that fits within the two dates?
I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.
I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…
A = 2000 text
B = 1234 text
F = 2000 trailing spaces, text
G = 1234 trailing spaces, text
I = 10,000
Answer in column J
I want 20001234 = 20001234 = 10,000 for the whole column.
I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.
I was asked at work to create a report that takes raw data from a questionnaire and compiles it in an easy-to-read report. (Little do they know that this is how I'm such a wiz at excel ;-) )
It seems pretty simple, but I can't seem to figure it out! On the attached workbook, the first spreadsheet,"SUMMARY", is the jist of what the report should look like - the average score for each question by floor. On the next spreadsheet, "RAW DATA", is a sample of what the raw data looks like - just a list of each room number and the score it received by question. the first 2 digits of the room number signify the floor (ex. 201=2nd floor, 1215=12th floor, etc.).
My major issue was with getting the score for each floor without having to manipulate the raw data - this report will eventually become a shell that can be used with new data every month. Is there a way to make a range equal a number (ie. 200-299=2)?
First sheet is comprised of 12 grouped cells. Each group consist of 7 rows and 6 columns
the above mentioned groups are bwoke down with 6 lines of data and 7th row is totals of the 6 boxes.
Each gtoup will have totals, even if they run over to next group
The first column is a task number, which will be the main component. A task number can be used multiple times and can run over to next group of cells
Here is an example of above mentioned sheet
task# Start time end time Description
999.......15:00.......15:05.........Open Mail
999........16:05.......16:55........sort claims
A new task number will move to the next group of cells
748........15:05.......16:05.........23 inches
2nd sheet will be a time tracking sheet. Here is the info from this sheet
999......15:00.....15:05.....open Mail
748.......15:05.....16:05......23 inches
999........16:05.....16:55......sort claims
The 2 sheets look alike but on first mentioned sheet info is broken down by task number
I'm stumped on what I know is a pretty basic problem. Maybe i'm just trying to over think it.
I have a table of sales data...One field is the date it was sold, one field is the amount it sold for. The date field isn't in order and it contains dates over the past 12 months. I need a way to total the amount of sales in each month and not through a pivot table. I am able to count how many entries there are, but I can't find a easy way to do a count of how much was sold in each month.
I have a spreadsheet with 12 tabs, 1 for each month in the year. On each tab I have employee names (column A), followed by the premium they pay for their health insurance (column B). I'm attemting to create a list of each employee and how much they paid for helath insurance over the year. Since employees come and leave thorughout the year, the lists aren't the same on each tab. However, I was able to create a master list of all possible employees and placed this list into a new tab.
I'm vaguely farmiliar with the SUMIF function and I believe that this would be the best approach, or some possibly sort of vlookup.
I have a 5 column document with about 3000 entries that I need streamlining. THe first column is a student's name, then grade. The next 2 columns are for Honour Roll Standing (A or B) and/or an Effort Honour Roll Standing (E). Finally there is also a "term" column indicating if they received this award is Term 1, 2 or 3.
An example source file is found here: [URL]...
As you can see, there are many students who have received an award in all 3 terms, however there are also some students that only received standing for 1 or 2 terms.
I need to manipulate the data so that there are no duplicate names and all the data regarding Honour Roll standings and which term it occured in are all laid out in one row per student.
IDEAL COLUMN HEADINGS:
NAME | Grade | Term1 HonourRoll | Term1 Effort | Term2 HonourRoll | Term2 Effort |
etc etc etc
Here is the attached Excel file and the following is the desired output of the macro:
1.) List the data (Names) of the Columns D (Input), F (Analyze), and H (Output) in Sheet1 to Column A (Name of Person) in Sheet2. There should be no repetition of two names.
2.) Count the number of entries of each person in the Column D (Input) in Sheet1 appears per month (basis is the Input Date column E) and record into the corresponding month in Sheet2 under the Input Header.
3.) Add the total of the 12 months in the YTD column under the Input Header.
4.) Repeat steps #2-3 for the Column F (Analyze) and Column H (Output) of Sheet1 with the results recorded in their corresponding headers in Sheet2.
5.) Note: The data in Sheet1 is a running data and continually adds up as the current year goes by. If there is a way the macro could take that into account it would be much better.
HERE IS THE LINK OF SAMPLE FILE: [URL]
I've got a tab with 32K+ lines of data in six columns. I need to be able to summarize it in a table based on two criteria (e.g. Region, Line of Business). I am currently using 16 sumproduct formulas (=sumproduct((type=a5)*(reg=a4)*revenue)) where type is the line of business and reg is the region column, and 8 vlookups per region. Each tab will have about 8 regions and I'll have 4 tabs. As you can tell, this spreadsheet will quickly get out of hand in terms of calculating speed. I can't really use a pivot table because of: 1) formatting constraints 2) the vlookups refer to a separate tab than the main data dump. What alternatives do I have other than array formulas to get the data in a nice little summary form? The formulas/presentation aren't very complicated, but I have yet to come up with a good alternative to the above.
View 3 Replies View RelatedMy company would receive RFQs from potential customers inquiring the availability of different products, and we are trying to summarise these requests to find a trend.
Each product has several attributes, such as descriptions, keywords, manufacturer's code, etc. The RFQs are all different, with each request providing some attributes while missing others. The only reliably uniform attribute is the product number.
I am trying to build a database with the maximum amount of details on each requested product, therefore consolidating the different requests. I already put the source data on the same sheet and sorted the product numbers. I also know how many instances there are for each product number. In other words, my data look like this:
Product number (sorted)
Repeat count
Attribute 1
Attribute 2
111a
2
aaaa
[Code] .........
Note:
1. The product numbers and repeat count are reliable
2. All the attributes are valid, i.e. as long as one can fill a blank I would take it
3. If none of the instances provide a certain attribute, it is acceptable for it to be left blank
And I am trying to turn it into this:
Product number
(sorted)
Repeat count
Attribute 1
Attribute 2
111a
2
aaaa
bbbb
123456
1
abcde
100x
3
123456
wxyz
I already spent a whole day trying to do it with MATCH, OFFSET, VLOOKUP etc to no avail. It seems I would need some kind of VBS with loop and array functionalities that are beyond me.
Below is a series of sets. Column A is the set number. I need a macro that will insert a row between sets and then put a border around each set. In my spreadsheet the sets are from A1:C500. Sometimes the sets are only 1 row, sometimes multiple rows. It looks like I will be doing one of these sheets every week. So far I have been doing it manually, but a macro sure would save some time.
View 13 Replies View RelatedI have two separate worksheets:
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
Referral
A
B
C
[Code]....
way to do the calculation using Excel 2003
See attached spreadsheet. It's an imaginery list of orders. Raw data on the left, summary on the right.
F is a reduced list of A, G is how many times each appears in A. H is supposed to be the total values of each order, ie quantity in B * value in C.
How do I calculate H?
I am working on large sets of data (more than 50,000 rows of data). I have two sets of data. Set 1 and Set 2 (master data) on the same worksheet. Both the sets of data have three columns each. I am using EXCEL 2007. I was able to accomplish step 1 below.. but I am totally lost with step 2 since i have an additional criteria for the "year".
I have attached the excel sheet as well. This is what I am trying to accomplish:
1) I want to find exact matches in set 1 and set 2 and highlight it or do something to show that a match was found. The challenge is the data in set 1 can occur anywhere in set 2.
2) Add to the complication .. my criteria for matching the year is different. If the Set 1 "year" is equal to or greater by 1 yr or greater by 2 yr when compared to Set 2 "year", I want to treat it as a "match".
For example, from the data attached:
Set 1 data in row 4 is: ATLANTIC ABSECON 2004
Set 2 data in row 3 is: ATLANTIC ABSECON 2003
I want to treat these two data as "MATCH" since ATLANTIC matches ATLANTIC, ABSECON matches ABSECON and according to one of my criteria for year, Set 1 "year" is greater by 1 yr than the Set 2 "year".
I have tried nested ifs and vlookup to compare two sets of data and change the first set of data if it is than the first. But leave it alone if it either is the same or does not exist in the new set of data. It sometimes seems to work but i find it is not consistant. It looks simple but i think i am missing something.
if column A has identifiers and column B has results then it should work if the identifiers in column C are found in column A and it looks to see if column B and D are the same, then change B if different but leave it if either it is the same or not there.
A
code1
B
36
C
code1
D 33
I have two sets of data for students. One set contains all the students with certain test scores, taking up columns A to N. The other set contains about 80% of those same students with a different set of test scores. Students are sorted by ID numbers. How do I combine the second set into columns O to V so that student ID numbers match and it inputs the rest of the student data into the sheet.
I know there will be some blanks because not all students will be there but I need them to match up, even with the blanks.
I created a macro that extracts two columns of numbers from one workbook and need to match them to another set of numbers in another workbook. For instance i have numbers like...
18314 907
18272 64
11005 907
11005 324
..Now i need to match these numbers up with the others to find the hyperlink associated with them.
I'm attempting to create a mailing list for the company I work for, but the software that has the data record is very limiting. The two tables I received from the program are listed:
Customer
Number of Sales
name 1
#
name 2
#
name 3
I tried to illustrate that the address list has a different customer composition than the sales list; the address list has customers that the sales list doesn't have. This means I can't just sort alphabetically and drag the addresses over. My goal here is to create a datasheet that incorporates both the address and number of sales so that we can send the top 500 customers a mailing. How can I do this?
I have 3 seperate tabs of data that i want to put into a combined sheet, how would i do this, i assume it's just like a copy/paste exercise but not sure how to do this using VBA?
My tab names are;
FND Data
UL Data
Life data
I just need the entire range copying from each tab into the 'Combined' tab
In the attached workbook, I have identical sets of data in column A separated by an blank cells. I need a macro for user form that searches for an item indicated in the textbox of the form and then delete the whole row in all sets of data.
It is important to keep the blank cells between the sets constant, 4 blank cells between each set, except the first set that starts with 3 blank cells.
I’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).
In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.
Is there a simple way/formula of doing this?
I manually reconcile two sets of data every month by sorting by PO# and then manually matching up the onse that are the same and then calculate if there is a variance in $ amount.
The 1st set of data is a list of invoice PO#'s and invoice $ amounts and the 2nd set of data is order PO#'s and order $ amounts. What I need to do is reconcile the orders to the invoices by PO#. It is very common to have a PO# on each list but have a different $ amount and I only need to know what the difference is between the order amount and the invoiced amount. I also need to know what is on the invoice list and not on the order list and vise versa....
I am wondering if it is better to do this in excel or access. I am pretty familiar with access...i know this would be easy to do if querying from one table and doing a relationship between identifier, but i'm not sure how to capture data from both tables. But basically this is what I want. I have 2 files, which have identifiers and share amounts...both files will have some like identifiers, some not alike...basically this is what I want to do: ....
View 9 Replies View RelatedI'm trying to compare two sets of data on the same sheet. Each set has 4 columns...A - D and F - I. I would like to take the data from D and I (range 20 to 50) and compare them to see if any differences exist in the two columns. If there is something different I would like to bold and highlight the cells (A-D) in that row, and the same goes for the second set of data (F-I).
I'm trying to do this with macros so I can add it into my existing code.
I have 2 sets of data/array/range. Each set consists of 2 columns with a large number of rows.
I want to compare my 2nd data to a master data. And list if anything is different in 2nd set of data from master set in column A than highlight the difference or copy the value to another place.
Also want to compare the 2nd column if column A was same and consider both column A and column B for that associated row different if column B is different.
The trouble for me comes in because the list is never alphabetical (sort doesnt work cause of funky naming) and never of the same size.
Attached is a photo of an example for maybe an clearer understanding. Also attached an example excel sheet I tried it within excel but cant seem to figure out how to look also for the 2nd column, so im trying to avoid the within excel route and go using vba ...
T3OCcxw.jpg
example.xlsx
I attempted it with a very basic code thats not working =/ just cant seem to figure how to code to get the desired result
VB:
Sub matchdiff()
Dim cell As Range
Dim found As Range
[Code].....
I am trying to compare two sets of data.
I have a set of All zip codes in 10 counties in one worksheet:
ex:
Column 1 / Column 2
30303 / Fulton
30307 / Fulton
30310 / DeKalb
30234 / Cobb
But this sheet has about 300 zip codes
Then in a second worksheet I have dollars by zip
30303 / $10,000
30456 / $56,000
I'd like to make a third column in this sheet that fills in what county. Does anyone know how to search sheet one for the appropriate zip, then stick the word beside that zip into the column 3 secion of the dollars by zip code?
I'd rather not have to search every zip code in the dollars section one at a time. Not all the zip codes in the state are represented in the second sheet, so it's not quite as simple as just lining them up.
Create one X-Y SCATTER graph (with Smooth Lines and Markers) with the Average Movement Time (MT) on the Y axis and the Index of Difficulty (ID) on the X axis for each group member during STATION 1: PAPER BASED VERSION OF FITTS' RECIPROCAL TAPPING TASK and STATION 4: PAPER BASED VERSION OF FITTS' RECIPROCAL TAPPING TASK: WEIGHTED
ID stands for index of difficulty, the graph is supposed to indicate that as the index of difficulty increases (3) movement time increases and as it decreases (2) movement time decreases.
Lab 8 Data Q 4 attempt.xlsx‎
I have horizontal records of data , datewise, if i need to find output for a particular date what should be formula, for instance for 1/21/2011 output is 135 nad for 1/15/2011 is 400. I need a formula wherein if i put the date it should throw the output of the same.
1/10/20111/11/20111/12/20111/13/20111/14/20111/15/20111002001501203004001/16/20111/17/20111/18/20111/19/20111/20/20111/21/20116075901051201351/22/20111/23/20111/24/20111/25/20111/26/20111/27/2011253545556575
Create a macro or some other solution to sorting my data. Just clicking on Sort Ascending isn't good enough because it'll sort everything and remove the empty rows. However, I want the empty rows to stay there. The way that I've been doing this is manually. Basically, after the empty rows were inserted, I'd click on a cell in Column A and click on Sort Ascending, then I'd scroll down and click on the next set of data and click on Sort Ascending, then the next set and the next. I'd end up having to do this hundreds of times.
I've uploaded an example of what I'm talking about, I also labeled the tabs as "I want this" and "to look like this":
Example file.xls
I've got a pretty tough problem I need to solve with excel. So im comparing prices of products from 2 Different Sources, I need the best method of comparing these sets of data. Realistically I need both sets of data to compare and move automatically (or create duplicates elsewhere) to show the product ID, Price A and Price B.
Here's an example
Supplier A
Cheese Grater: 39.99
Golden Spoon: 129.99
Supplier B
Cheese Grater: 59.99
Golden Spoon: 89.99
These values will then combine to read
Supplier A Supplier B
Cheese Grater: 39.99 59.99
Golden Spoon: 59.99 89.99
Not sure if it will matter but both lists are not identical, all I want are the matches not the ones unique to different suppliers.
This comparison list is like 12,000 products long, I'll never be able to do it manually. This has stumped me for a while now I only seem to be able to find formulas which give TRUE / FALSE or Colour changes.