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?
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.
I'm working on a spread sheet tracking conference presenter so I'd like to come up with a formula and find any duplicate names. I'd be grateful to learn how to do this without hand sorting.
If value in cell A1=y
and a lastname that appears in G1, H1 or J1 appears more than once in the range of cells in three columns - G1:g10, I1:I10, J1:J10
insert in cell B1
the list of lastnames (up to 3) seperated by a colon in G1, H1 and J1 that are duplicated elsewhere.
I'm so happy to find this forum and eager to learn more about excel.
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.!
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.
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.
Remove duplicate but add values.xlsx
I have about 800 records and the the unique identifier are the names. Some owners are repeated several times and they have different values for the EDU column in my project. I need to remove any duplicated owners but I need to add up the values of each record that had value so I can get a combined value for one owner instead of a repeated owner with smaller values. How would I go by doing that? A formula or is there a tool in excel? I am lost on this one. I uploaded a sample file so you can see what I trying to do.
BTW i found if the values are the same for the EDU then I can not add them since it is also duplicated. I can only add up the unique values for the EDU.
I manage messaging server farm that keeps mailboxes and their replicas on different servers for resilience. I need a way to check the replication status. I try to do it by comparing their sizes. I got all mailboxes and replicas incl their sizes imported into excel (2010) and I need a way to compare them.
Like this:
name
size
John Smith
10
Mary Westwood
20
[Code] ......
My idea was to find matching pair (two records of John Smith) and compare size. If the size differs more than specific value (5% or 2MB for example) the result is true/value/colour by conditional formatting etc.
So, I have some names and values. Rank function give me order for those values.
Small function gives me ascending order. I want to get first n (let say 5) values back next to each other but can't use VLOOKUP function because sometimes I get duplicates (red numbers).
If there is more same numbersthat small function returns... It need to give me all of them, no matter 5 is limit. how to get back values of rank function that are duplicated. Book1.xls
I am currently trying to display a number from a column of data, where the number is the smallest, then the second smallest (third, fourth and fifth where applicable). When using =small, I am able to display the second smallest number, but when the list contains duplicates, the second smallest figure often matches the smallest. I am having the same problem with =large. I have tried to combat this by using an IF statement, but am only able to place so many arguments into the formula before excel is unable to perform the formula. This is also proving quite lengthy :o(
View 4 Replies View RelatedComparing 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.
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.
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 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’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-.................
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 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 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'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
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.
I have a list of over 2000 names and need to establish which names from column A appear in Column B, as well as which do not appear in column B.
I attach an example : NameCheck.xlsx‎
I'm new to macros and VBA so I am wondering if it is possible for a macro to compare values in column B of sheet2 and column B of sheet3 and for the values that DON'T match i.e. unique values to column B of sheet2, copy row from sheet2 to sheet4? This will aid my processing time drastically if I can copy all the rows from sheet 2 to sheet 4 where the model number in column B of sheet 2 doesn't appear in column B of sheet 3!!
View 3 Replies View Related