I have the need within a spreadsheet to automatically compare the value of 2 cells and if they match exactly to move the cursor to a group of cells. If they do not match then move to another group of cells. The only way that I can see to do this is via Macros. However I still need to do the automatic compare first.
I have a large number of sheets with data in the following general format:
x ..... y ............ z etc
A Unique value Unique value
A Unique value Unique value
B Unique value Unique value
B Unique value Unique value
C Unique value Unique value
C Unique value Unique value
D Unique value Unique value
D Unique value Unique value
E Unique value Unique value
E Unique value Unique value
In seperate sheets, I want the following:
x y1 y2
A
B
C
D
E
x z1 z2
A
B
C
D
E
And so on for a number of columns - I always want column "x" with another column, however I want the two values for the same value of x to be next to each other, not above one another.
Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this
List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6
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.
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.
I am aware this question must have been asked to death on this forum and I already searched and found some answers but for some reason I can´t get it to work. I have two worksheets (sheet1 and sheet2). In Column E of Sheet1 I have product descriptions that I want to compare cell by cell to the product descriptions in Column F of Sheet 2. If they are not the same, color in the cell in Sheet 2. There are 1810 rows in both columns. I tried this method but it gives me an error (in spanish) that says that I cannot compare ranges of data.
I have also seen some code to do something similar (http://www.ozgrid.com/forum/showpost...85&postcount=3 ):
Sub find_cell()
Range("A:E"). Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate
ar = ActiveCell.Row
ac = ActiveCell.Column
' the two lines above store the row and column values of the cell found
' in your example ar is going to be equal to 229 and ac is 3
' now this following lines are to change the colors to red
With Cells(ar, ac).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End Sub
I don´t quite get the "Range("A:E").Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate" part though.
I have 300 cells filled with around a dozen different names - i.e. the same names crop up multiple times. What I need is automatically creating a list of all the names that appear, ordered by how many times they appear and with the number of times they appear next to them - i.e. ranking the names by their frequency.
View 3 Replies View RelatedI'm trying to compare the values of columns B and G and if any of the cells are G < B, I would like to move the whole row into the second worksheet. And only if possible, it would be awesome if the macro could check column A for duplicates before the value comparison and sum up the column B if there are duplicates. In my example row 6 and 7 on column A have the same suppliers code and the amount totals to 2.
In this case for example, the value of the third row of G is lower than B, and therefore I would like the whole row 3 to be cut out from worksheet 1 and moved to worksheet 2.
Original view of sheet 1:
SUPPLIERSCODE
ORDERAMOUNT
PRODUCTNAME
BARCODE
SUPPLIERSCODE
WAREHOUSE STOCK
And then cut out the rows in which G
The attached workbook is a follow up report that gets run every Friday. There are 1092 cells that contain formulas that reference a file that was created on Wednesday of the same week. eg.
View 5 Replies View RelatedI have a report which has several tabs of data input which is automated.
There are then several columns of formula which I manually fill down at the minute, is they an easy piece of code to automate this?
The attached example may help, on the tab data source I want to automate the copying down of the formulas in columns D&E.
Can this to be automated by the button on the tab front page?
I am trying to automate creation of pivot table, where the first three fields will go into the RowLabel field, and the rest of them will go into the values as "Sum of ____".
Is it possible? Because I could do it one by one; however, those fields are different every time (there are different number of those fields, and their values are also different).
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.
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.
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?
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.
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 ?
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
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).........................
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
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.
I’m attempting to find values that are within a band of .001 of the values of cells in columns L through O and searching columns B through I for values that meet the .001 criteria and copying the values to columns Q, R, S, and T respectively.
I mentioned the values in L, M, N and O are where the comparisons will be made and these values are tagged to a specific date in Column K. I need to find the dates in Column A that are in between the values of K and the cell beneath it. For example, in K4 I have a date of 10/3/99 and the next date I have in K5 is 10/5/99. I would then query Column A for the dates 10/06/99 (A4), 10/07/99 (A5), and 10/08/99 (A6). The values I need to find within the .001 band are in Columns B through I (B4:I6). Lastly, when reach the last value in Column K, query all values in Column A greater than last value in K and copy the appropriate values in B through I to Q through T.
Rules for copying data to Column Q, R, S, and T:
For Column Q-
Find values in B, C, or D that’s within plus or minus .001 of M and copy the value of B, C, or D into Column Q of the same row.
For Column R-.................
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.!
The following formula (thanks to Barrie) determines if the difference between 2 values equals a third value, within a fourth ‘margin of error’ value, and returns either True or False.
=AND(C1-D1
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.
I need to hide the rows in an excel whose value is equal to the array list that has been already hardcoded...How to do this...
View 1 Replies View RelatedI 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‎
I am facing a problem in evaluating a problem. I am referencing from the screenshot of excel file. I want to replace the values of Column-C for which column-A matches with column -G. And, the remaining will remain same.
Capture.jpg
I am also attaching the excel file.
Am looking for a technique to print unique values by comparing 2 columns
Below is example
Col A
Col B
Col C (Output expected)
1
4
1
[Code]...
I am using a formula for weeknumbers and formatting cells based on their values but have just realised it will not work at year end.
The formula in D2 is as follows:
=IF($C2<$O2,"green","blank")
C2 is the weeknumber of an incident based on the actual date in B2 (e.g. 01/06/2014) and uses the formula: =WEEKNUM(B2). O2 is the weeknumber of the current day and uses the formula: =WEEKNUM(NOW()).
So cell D2 states "green" if the incident happened over a week ago, and states "blank" if the incident happened in the current week.
If I used this at year end when the incident weeknumber may be 52 and the current weeknumber may be 1 - the formula would not work as it would state "blank" rather than "green", even though the incident happened over a week ago.
How can I get the year encorporated within the weeknumber?
I am trying to compare to values that a user inputs in a user form. It's ok if they are not the same I just want the user to know it. I am only including the part where the if statement is failing:
VB:
If Me.VSLongQuant.Value <> Me.VSShortQuant.Value Then
MsgBox VerUnBal
If VerUnBal = vbNo Then
Exit Sub
End If
End If
I have VerUnBal as Long and defined as:
VB:
VerUnBal = MsgBox("The Long and Short option Qunatities are not equal." & Chr(13) & _
"Is this intended to be an unbalanced vertical spread?", vbYesNo, _
"Unbalanced Spread Alert")
I can provide more info as necessary. There are two problems 1) the message box is appearing whether the numbers are equal or not, and 2) clicking No in the message box does not cause the Sub to exit.
