I'm trying to compare the 1st 7 days average this year vs the 1st 7 days average last year. How do I write the formula?
Last YearThis Year
536,212
852,865 417,290
345,981 702,816
850,560 189,808
335,285 691,152
572,165 283,036
842,145 404,916
735,364 528,332
121,475
348,793
697,944
876,360
671,170
928,103
How to align two columns that sometimes have rows that are unequal in number. The data is sourced from a web page that has a table layout and the table lays out the one to many values this way in the table. My data looks like this:
Table ...Term
t1 ..... term1
t2 ......term2
..........term2.a
..........term2.b
..........term2.c
t3 ......term3
..........term3.b
The editor removes leading spaces. In the column Table there are only three rows but in column Term there are multiple rows per single row from column Table. So one row t2 for column Table has three rows in column Term. Are these cells merged and can I in a mcro detect if the cells are merged? How is the addressing of the merged cells in a VBA macro?
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.
N.B. The headings of Columns C & D are :-
C = Value Found in Column A
D = Value Found in Column B
Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)
Does anyone know a formula that calculates the future value of an unequal payment stream? I am attempting to calculate the value of an investment plan where the periodic payment increases by 1% each period. I can get the answer by using the FV formula for each payment and then adding them up, but would like to be able to do it without having to do so.
View 9 Replies View RelatedI have 2 columns D, E and I would like to find cases in column E that are not equal. based on the numbers in D.
For instance, in D I have the numbers
4901780959501
4901780959501
4901780959501
4901780959501
4901780959501
in E respectively I have
MSX-M2GSX
MSX-M2GSX
#N/A
0
MSXL2GFSHX
Well how would I be able to spot these variations and insert all of them in columns G,H ?
I am attempting to program a column of cells so that if say "B3" doesn't match the contents of "A3", then "B3" will be highlighted in some way, maybe with a color. I'm using Excel 2000 and I can't see a way in Conditional Formatting to accomplish this without having to program each cell in column "B". I was hoping to be able to click and drag down in column "B".
View 9 Replies View RelatedColumns E and F can have a 1 or a Zero
If e2, d3, e4 = all 0's or all 1's, count = 3 in col F
If either e2, d3 or e4 is not equal the count is 2 in F
Example
D E F
0
0
0 3
0 1 2
1 1 2
1 1 3
Also, why would this formula work with numbers but not letters?
=IF(B2="x",0,IF(C2="x",1,""))
I am using this formula
=OFFSET(Inputs!$E$124,0,0,COUNTA(E124:E168),1)
to get an array with the names of some inputs excluding the blanks ... hit F9 to see that it works fine...
Now i want to combine the arrays produced by this formula for two different lists.
how to achieve that? please let me know if this is too vague..
how to plot dates which are unequal in interval
the dates that I want to plot are the following:
1/2/13
1/7/13
1/14/13
1/21/13
1/28/13
2/4/13
2/11/13
2/19/13
2/25/13
3/4/13
3/11/13
3/18/13
3/25/13
I want to appear them exactly on my X axis no different dates in between them.
using the ttest formula function
View 2 Replies View RelatedI have a problem finding the correct formula for counting matches with conditions between 2 non-equal ranges in Excel. The sheet is a try at making a working schedule template a bit automated.
For Week 1 each cell in the H16:H25 has a drop-down list (originating from BD30:BD50) where a work position can be chosen. The fixed list in BD30:BD50 starts with “<<SELECT>>” which is the default choice for the cells in H16:H25, and then “HOLD” before continuing with various work position names. K16:K25 is shift number 1 on Monday, L16:25 is shift 2 on Monday, and so on until Shift number 6. Then the rest of the days of the week follow (each with 6 shifts). Monday through Sunday (with 6 shifts for each) ranges over K16:AZ25. In the cells in K16:AZ25 the following can be entered: “x” (work), “o”(off), “-“ (leave).
The issue is the formula in each of the K26:AZ26 cells which are to total each of the shift columns . I want to count all the “x” in each column, but ONLY if the positions chosen in H16:H25 matches one of the positions in the list in BD30:BD50. NOT if a cell in H16:H25 displays “<<SELECT>>” or “HOLD” (even if it has a “x” entered in one of the Shift cells).
For example: .....
How would I select a dynamic range in VBA without using name a range in Excel. I have searched for possible answers, however they use xldown, or xlup which works great for non-empty cells. My problem need to select the area where there are empty cells. Here is a snap shot of the data looks like:
Trade Date A B C D E
2008-Feb-11 450,432
2008-Feb-13
2008-Feb-15
The cell with number is where I got the formulas. Now i wish to fill that formula with the rest of the area. However, the problem is the number of rows and number of columns may change in the future. So I need to select those empty areas in a dynamic way.
2014-07-30_16-52-23.png
Fist I want to compare ColB=ColH, if TRUE, compare colA=colG, if TRUE, again compare colC=colI, IF all this conditions true, then give (colD-colJ) on colL.
all the unmatched rows in 2 tables to populate with different for each table
I need to compare add Column A&B in sheet1 and column A&B in sheet two and compare the two result for the difference(like vlookup).
formula to merge two cells in two sheet and compare.
i've check the other threads on how to compare two columns but for some reason it is still not working for me.
I have 2 columns. Column A is the master column and column B is the comparing list. If the company that is listed in Column B (Anywhere in column B) matches exactly with the company name in column A, I would like it to say "check" in column C.
I have two diffrent sets of columns of data on a spreadsheet one column set is A,B the other is D,E I want a formula or makro that will check columns A,B and compare it to D,E and if it finds a match will paste it in G,H is that possible and how would I do this?
View 9 Replies View RelatedI have two worksheets but have a same column named "Admin No.". I will need to compare the 2 "admin no." column.
Example:
Worksheet 1
Admin No.Name
1111Ali
2222James
3333Janet
4444Lihua
5555Ting Ting
6666Jasmine
Worksheet 2
Admin No.Name
1100Alvin
2200John
3300Mavis
4411Haoyi
5555Ting Ting
6677Jason
I will ike to compare both "admin no." column and when worksheet 1 5555 can be find in worksheet 2, it will return True. 6666 cannot find in worksheet 2 then it will return false.
I have numeric value in columns B, D, F, H.
I want to compare the values of these four cells in each row and update columns M (with the minimum value) and N (with the column of minimum value).
How can I manage this comparison?
I need to compare two columns A and B with a result in column E. BUT I don't want the result to be added together. ( since it's receipt numbers ) SO my formula is incorrect I need a formula that will show the receipt numbers next to each other divided by a comma or forward slash...
View 7 Replies View RelatedI have column A with 228 rows and column D with 314 rows. Both columns have the same data except that D has different data. I would like to line up everything that is the same in A and D and everything else in column D that is not the same move to G. Is there a quick way to do this?
View 5 Replies View RelatedI can find lots of ways to compare two lists with single columns, but I need a very basic guide to comparing multiple ones across two sets of data and then highlighting or extracting the unique records. I would prefer to keep the data in the existing columns for later sorting and other purposes.
I currently have the two datasets as two separate worksheets in an Excel 2003 file. The two datasets consist of the same three columns containing strings of text, but there is quite a difference in the number of rows. Dataset One is c. 3550 records, Dataset 2 is c. 1600. There are no duplicate records within each dataset, but there are duplications across the datasets and the records are in a different order, so it's not a matter of comparing Row 1 to Row 1 and so on. The data look a bit like this (semi-colons to show columns):
DATASET 1
Group;Family;Name
PM;Smith;Mary Anne
PM;Jones;Fred Henry
PD;Wilson;Peter John
PG;Green;William Laurence
...............
I need a Vlookup to look at two columns when searching. here is an example:
Sheet 1 is my report grid to hand out to the employees, A1 has a drop-down list with my employees names and A2 one for the month, when you click an employee name the vlookup formula fills in the information from sheet 2 (data sheet).
Sheet 2 has data like: Col 1= Month, col 2=employee L-name, col 3= production etc...
currently the vlookup formula messes up because the employee name is in there multiple times because of the months column, i have to delete all months except the one i need. formula is: =vlookup(A1,'sheet2'!,B:C,2,false)
this formula will pull the data in sheet two column 3 (production) for the employee that matches.
how can i make it compare first to col 1 (month) and then col 2 (employee).
I want to compare Columns A and B and Insert value in C.
So if Column A is null, then place value of column B in C, otherwise place Value of A in C.
I want to compare two Excel workbooks which have multiple columns and identify the rows only if all the valves match in both the workbooks, i am attaching a file to understand better.
View 3 Replies View RelatedI have two columns that each contain about 100 serial numbers. Most of the serial numbers shoud be in both columns. How to I determine if there is a serial number that is only in one of the columns and not in the other?
View 4 Replies View RelatedI want to compare many columns and find duplication in the last one;
Example:
Column A Column B Column C Column D Column E
1234 1234 1 A OK
1235 1235 1 A OK
1236 1236 3 A OK
1237 1236 0 A OK
1238 1236 1 B Duplication found in different column
1239 1238 0 A OK
To get Column "C" which is calculate how many numbers in that row are repeated with respect to Column "B", I solve it by using : =COUNTIF(B:B,A2) ;
But I want to get a result of duplication in "Column E" by see if "Column B" + "Column D" has the same value regardless of number then say "OK", but if it is not than the before, then write "Duplication found in different column".
The code below compares values on sheet 4 column A to sheet 3 column A and then colors a cell "Green" (Temporary), later I will place data from sheet 4 into sheet 3.
I dont know why but when it finds data on sheet 4 that is not 100% numeric it errors out.
Run-time error '91'
Object variable or With block variable not set.
The data in sheet 4 column A is primarily numeric, there are and always will be some numeric/alpha strings.
I can change the value of sheet 4 A2 to "123x" from "123" and the code stops as described. Leaving sheet 4 A1 as 100% numeric, which works fine.
i'm trying to do some analyzing on an excel sheet and was seeing if there was a function that I could use to speed this up.
So in the file...
I want to Group what's in Column A So the 1's together and the 2's together... Once that is selected I want to know what the largest number in Column C is...
So if the function can select 1 in column A then tell me that 10 is the biggest number in Column C....
I would like to have a macro or a function, which could compare two "A" columns in different workbooks. macro should run from the first one wb, which gonna have less data in a column than wb2. so, if, let's say, A3 in wb1 differs from A3 in wb2, an empty row should be added in wb1, and now we would jump and compare A4 in wb1 with A4 in wb2 and so on..
View 2 Replies View Related