Comparing Values And Returning Different Column
May 31, 2007
I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.
HTML
Column A Column B Column C
Date Number Who
3/23/2007 902-555-2596=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-678-0352=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~
Column A Column B
902-555-7958Big Bird
902-555-5100Daffy Duck
902-678-0352Elmo
902-555-5752Donald Duck
902-555-7420Bert
980-123-6621Ernie
902-555-2596Grover
View 12 Replies
ADVERTISEMENT
Apr 8, 2014
I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.
So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.
View 3 Replies
View Related
Apr 22, 2014
I have an attendance sheet that employees scan their badge number, and returns their name and time/date stamp using a simple VLOOKUP.
I want to create a report that compares the names in the attendance sheet against the all the names in the master sheet, and only returns the names that have not attended the training.
I have tried a few test formulas using COUNTIF function, but with no luck.
I have attached an example. Use the worksheet labeled April 2014 A (2nd shift)
New Six Point Safety with Attendance Sheet Barcode Scanning.xlsx‎
View 3 Replies
View Related
Sep 18, 2013
If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).
Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
D16 (answer cell) =John
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45
D16 (answer cell) =Bob
View 2 Replies
View Related
May 30, 2014
Excel user that has been manually computing a 6000 row spreadsheet. Here is sample data and what I need. Column A goes through the alphabet (A,B, C, etc.) and Column B is numbers from 1-65. I need Column C to count the number of rows for each alphabet/number pair. K, 28 has 3 rows; K, 33 has 1 row; L, 21 has 4 rows; etc.
Column A
Column B
Column C
K
28
3
K
28
3
K
28
3
[code]....
View 2 Replies
View Related
Nov 13, 2009
I'll try my best to explain what I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.
Here is what I'd like to do via a Macro:
Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)
Example:
A
0
300
550
1200
1700
1900
2200
2000
1850
[Code]....
So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.
Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.!
View 9 Replies
View Related
Nov 13, 2009
I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.
Here is what I'd like to do via a Macro:
Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)
Example:
A
0
300
550
1200
1700
1900
2200
2000
1850
1750
1600
1500
1950
2500
4000
So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.
Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.
View 9 Replies
View Related
Feb 24, 2014
I would like compare data using a certain column to key off of and compare whether data has changed.
I have 5,000 rows and 26 columns. Cell C1 is CALLED PMNUM, cell I1 is called CHANGEDATE
I want to find all of the same PMNUMs (C2-C5000), look at the last change date (I2) (sorted in descending order), compare it to the previous change date (I2) and if anything has been changed in columns D2 through G2 and J2 through Z2 compared to the last time the data was reported, list the changes in in AA2.
View 4 Replies
View Related
May 12, 2014
In the attached example I paste in a report from another system into Sheet2 (rough data layout in example).
In column B in Sheet1 I have tried using Match and Index formulas to try and return only the Local Shipment figures from on Sheet2 for each staff member.
The issue i'm having is that the data in Sheet2 can change based on how long the report is run. There is also no way I can change the way the raw data is set out in Sheet2 as it's an external system.
Basically i'm looking for a formula that isn't locked down to certain cells and can work with data moving up and down columns at different stages. The key data never changes from column A and F in sheet2. But what does change is whether each person has one or both of Sales & Local Shipment data in the reporting. This obviously changes based on what is completed each day.
So I need a formula that matches the persons name in column A in Sheet1 with Column A in Sheet2, then looks for the first mention of 'Local Shipment' below it and returns the cloumn F value which is in the same row as 'Local Shipment'.
View 3 Replies
View Related
Aug 7, 2014
I have a table of data-
IDDescriptionDatePax Count
316COACH01/07/20135
323COACH3/07/201410
324COACH18/07/20141
325COACH20/08/201420
333COACH21/09/201445
345COACH22/09/20143
351COACH23/09/20143
362COACH21/10/20142
371COACH22/11/20142
394COACH23/12/20140
416COACH23/12/201418
422COACH23/12/201439
Example, the real data is about 38,000 entries. I want to build a query to return the value of Column D (pax count) based on the values of Columns A (ID) and C (Date)
This would feed into a second, ID specific table, like this-
ID 536 Bookings JULY 2013
1/07/20132/07/20133/07/20134/07/20135/07/20136/07/20137/07/2013
(value) (value) (value) (value) (value) (value) (value)
SUMPRODUCT, usually my mainstay, is for some reason letting me down, returning a value of 0 for everything irrespective.
View 9 Replies
View Related
Apr 14, 2013
I have an excel file with some data in it. I have two columns with one of four words in it. For simplicity I'll call them RED, GREEN, BLUE, and Yellow. A few columns over I have columns "Same" "Good" and "Bad". I'm trying to figure out a way to get excel to put a 1 in the appropriate column, if cell A1 is Red I need excel to look at the word in cell B2 and put a 1 in the "same" column if A1 and B1 are the same word. If the word in B2 is Yellow I need excel to put a 1 in the "Good" column. If the word in B1 is GREEN or BLUE I need it to put a 1 in the bad column. The tricky part is each of the four words RED, GREEN, BLUE, and Yellow have a different set of words that will be called "good" or "bad". So if the word in cell A1 if Green for example it will have a different set of good and bad color words.
So it seems I need four different functions depending on what word is in A1. In the column next to these lists of words I have the date which I'll need to use to give me the sums of same, good, and bad for each day. But first things first. I think I need to use a combination of IF functions and MATCH function, but I can't figure out anything that works. Below is what I'm hoping it will look like when I've got the right formulas.
First column
Second column
Date
Same
Good
Bad
[Code]....
View 9 Replies
View Related
Oct 13, 2006
I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example:
if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on.
Sub Macro1()
Const NumRow As Integer = 3
Dim StartCell As Range
Dim RowNR, NewCnt As Long
Dim RowCount As Long
Dim Count As Long
Dim intRow As Integer
Dim bFmtComplete As Boolean
RowCount = Application.WorksheetFunction.CountA _
(Range("A1", Range("A" & Rows.Count).End(xlUp)))
bFmtComplete = False
RowNR = 2
Range("A1:J1").Select
' Rows("1:1").Select
Selection.Copy................
View 4 Replies
View Related
Feb 10, 2009
I'm quite new to array formulas in excel and was wondering whether or not I can advance on this formula:
=AND($C$5=B7,$K$5=G5)
The problem is I would like to compare the entire colums (or array) and return the number of 'TRUE' statements in the array as a value.
=AND($C$5:$C$65536=B7,$K$5:$K$65536=G5)
however, this returns all values as 'FALSE'
example of arrgument:
IF C5:C65535=customer then count how many cells in K5:K65536=completed
View 8 Replies
View Related
Jan 22, 2010
I need a function to compare two ranges and return a match. For example
Range A: {A56, B43, C98, D44}
Range B: {A33, G89, D44, K123, OB55}
the function would return D44. Is it possible at all with functions or do I need a macro?
View 14 Replies
View Related
Aug 5, 2009
I have data exported as .txt from an external program in the following format:
Sample Name Marker Allele1 Allele 2 ....Allele n
AAA D8S1358 10 11 XX
AAA D16S539 16 19 XX
and so on.
Each "Sample Name" will have 16 different "Marker"s and upto 10 different "Allele" at each "Marker". There could be upwords of 200 Sample Names on each exported sheet. Is there a simple way to compare all of the data from each "Sample Name" to each other and possibly to another sample set that will always be static but of the sample general makup as this?
View 9 Replies
View Related
Feb 19, 2013
I'm trying to find a way to test is the max value in a range, columns A-F, comes after the min.
Example: Columns A-F have the values 4,1,3,5,2 respectively. I'd like a test to see if the min, in this case Column B, comes before the max, column D, and have an output that says yes or no. So in this example yes but if the numbers were instead 4,5,1,2,3 it would return a no.
View 3 Replies
View Related
Oct 6, 2009
My Data
Service 1 | 100 | 1.5$
Service 1 | 200| 2.5$
Service 1 | 300| 3.5$
Service 2 | 100| 11.5$
Service 2 | 125| 21.5$
Service 2 | 300| 30$
I need to extract the value in the third column according to an exact match of the first column (the service name) and a range match of the third column.
I tried SumProduct but the problem with my formula was:
Let's assum I ask for
Service 1, 200
The formula summed up 1.5 and 3.5 and returned 5
When I wanted to return one value from this table.
View 9 Replies
View Related
Mar 21, 2014
I have two lists, one has 250 items, the other 4500, both contain a String
I need to compare any word within a cell in the short list, against any word within a cell in the long list.
The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.
View 4 Replies
View Related
Apr 4, 2014
I am using Excel 2003. I have attached a data file here. getting the values in Q3, R3 and S3.
Scenario:
Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.
Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.
View 10 Replies
View Related
Jan 26, 2010
I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:
Sheet 1
A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase
I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:
Sheet 2
A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes
Does this involve IF, MATCH or VLOOKUP formulas?
View 9 Replies
View Related
Mar 2, 2014
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
The sample sheet has been attached for reference.
View 3 Replies
View Related
Sep 28, 2012
Got a road block on one of the trackers which I am trying to create which compares some of our agreements. Basically we need to compare current production with our contracts and then bring the tier level they are at in order to then calculate how much in overrides we receive. Belwo is an example:
Input Value = 140000
Out Should be = 1.5%
Set of Values 1
T1
T2
T3
T4
[Code]...
In a nut shell it will look for 14000 in set of values 1 and pick T3 to then go in set of values 2 and display corresponding T3 which will be 1.5% in above example as output.
View 2 Replies
View Related
Sep 14, 2008
on a sheet i have 2 column with values.
For e.g
DevID DevNum
123 s123
123 s234
987 sabc
987 sabc
Is there a way where i can compare the values in DevNum with the same DevID
to see if theres any duplicated values using macro?
View 10 Replies
View Related
Nov 5, 2008
Comparing 2 values.
I need to compare the value's on 2 cells. I have a sheet setup for my sales I would like to compare each day of the week. So in other words I would like to compare this Monday with last Monday and know what the percentage of my sales ether up or down is.
Example:
Last Monday = sales $100.00
This Monday = Sales $200.00
100%
So If sales are up I would like the percentage amount font to be in green. But if sales are down I would like the percentage amount to be in red.
View 2 Replies
View Related
May 28, 2014
I have a football score system and wish to compare the values of various cells - 2 if possible, but more if not possible.
Essentially I have a list of predictions for the score of a game (e.g. 2-1, 1-0, 0-0, 2-2 etc) in a spreadsheet, and I have a cell where I enter the correct scoreline (e.g. 2-1). The way the points system works is that if the prediction is correct (i.e. 2-1) then that player gets 3 points. If the result/outcome is correct but the scoreline is incorrect (i.e. 1-0) then the player gets 1 point. If they result/outcome and scoreline are incorrect, then that player gets 0 points.
Ideally I would link to compare 2 cells - predicted score and actual score, where the format of the scores is entered as 2-1.
However, I feel this may be easier having to columns for the score - Column A = 2 Column B = 1. In this way the scores only need to be compared cell by cell, however they would need to be compared by value for the correct outcomes.
View 5 Replies
View Related
Jul 13, 2013
There are two columns A and B and values there in
A B
1 1
2 2
3 3
4 4
9 5
10 6
11 13
15 15
As you can see there are certain numbers that are missing between column B and A. I want to know which of the numbers that are present in column B are missing in column A ...any handy calculation ?
View 3 Replies
View Related
Jun 25, 2014
How would I write a IF statement comparing 3 values. For example the field that it would be referencing would have values cell E51(1,2,3,4,5,6,7,...99). The pull back would be:
1-10 = $250,000
11-15 = $550,000
16+ = $750,000
View 2 Replies
View Related
Dec 13, 2005
I work for a financial firm. Every morning I compare today's cash balance file (sent from our custodian) with yesterday's to see if there is anything different in each portfolio cash balance column. Right now I'm doing it manually checking row by row. It'll become tedious if more and more portfolios are added. I'm looking for an efficient way I can use the portfolio codes column (as a key) to compare with previous day's file’s portfolio codes column , and list only those portfolios(in another workbook) if the corresponding cash balance column doesn't match. This is what I started doing:
Private Sub compare_cash_bttn_Click()
Dim b1 As Workbook, b2 As Workbook, b3 As Workbook
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim i As Integer
i = 2
Set b1 = Workbooks("CashA")
Set b2 = Workbooks("CashB")
'Set b3 = ActiveWorkbook
Set w1 = b1.Sheets(qry4rCash).........................
View 9 Replies
View Related
Apr 27, 2009
I have two columns A and B. I need a count of how many times a value in B is greater than the same row in A. I don't want to insert a new column (e.g. for if statements to add up).
Can I insert a countif at the top to do that? I tried various countif variations but could not find the right one. Additionally any other option that does not involve adding new columns/rows.
View 5 Replies
View Related
Nov 4, 2009
I have a cell (F2) that is auto updated with total sales units for today (Its a simple counter of individual sales). I want to detect when there is a "batch" of sales >= 10. ie. F2 may start at zero for the day and increment by 1,2,5 or 10 units at a time. If the increment is 10 units or more I would like to detect this and store the result in cell G4.
Typical values in C2: 0, 1, 6, 7, 9, 19, 21, 22 etc as new orders arrive. The larger order between 9-19 (viz 10 or more), I need to detect and store in G4. I know that I need to use both the current value of F2 (say 19) and the previous value of F2 (say 9) to perform this, but the solution eludes me.
View 2 Replies
View Related
