Indentify/Flag Duplicates With Formulas
Jun 23, 2006
I have an Excel spreadsheet with 3 columns (A, B, and C) and 600 rows. Each cell in A and B contains a 6-diget number. Column C is empty.
I want to determine whether the number in A1, A2, ….A600 appears anywhere in column B. If A(n) does appear in column B then I want to enter a symbol (say a Y) in C(n). If A(n) does not appear in column B then I want to enter a different symbol (say N) in C(n).
What is the formula I enter (and where) that will do that?
View 9 Replies
ADVERTISEMENT
Nov 19, 2007
I have a worksheet that contains thousands of patient appointment entries, there are more than one entry with the same patient details contained in the worksheet. For example; sometimes a patient will reschedule a appointment, this causes another entry to added for the new appointment. When the patient attends this appointment, the appointment status changes to Seen. But the original entry still stays Rescheduled. This there a way that we can look for identical infomation in some selected columns that match the original entry but have different infomation in some other columns:
E.G. The cells in columns A,B,C,H have identical infomation than the original entry but the cell text in column D equals to "Checked" or "Cancelled" and the cell date in column G is higher than the original entry date. If the result is true then tag the original entry as Invalid
View 5 Replies
View Related
Jun 12, 2014
I am using a CountIf formula to flag duplicate entries in a column of data, in a Column I have "=COUNTIF(P:P,P1)" It returns a number equal to the number of repeats of the data in call "P1". I then sort by that column to isolate all rows which have duplicates in Column "P". The cell has "1" if there are no repeats, "2" if there is one repeat, "3" if there are two repeats, etc. What I would like is a formula which would put a "1" in the cell the first time it sees data, then something else when it sees repeats. That way when I sort, ALL the repeats will be together and can be deleted. Is that possible?
View 7 Replies
View Related
Nov 29, 2006
sorting duplicate e-mails across three columns in an Excel spreadsheet.
Precisely, I have three mailing lists (Column A, B, and C) that I would like to sort.
I would like to know what e-mail addresses appear in more than one Column (Mailing List), and I would like to highlight/flag them somehow.
I have attached an example spreadhseet that contains fake e-mail addresses for test purposes. As you can see, some e-mail addresses are duplicated or in triplicate across the 3 Columns. In other cases, an e-mail address may be unique to a specific Column.
In my real spreadsheet, I have approximately 3,500 rows and 3 columns.
View 4 Replies
View Related
Nov 14, 2013
Columns A, B, and C have data (first name, last name, state) - 125k rows. Columns F, G, and H have data (first name, last name, state) - 5k rows. Some of these individuals are bound to appear on both lists, and I need to know which ones are indeed on both lists. So in other words, let's say I've got "John" "Doe" "TX" in cells F1, G1, and H1, respectively. I want to search through columns A, B, and C to find out if there are any instances in those columns of "John" "Doe" "TX" in the same row.
I'm thinking there will be multiple VLOOKUP formulas involved, but I could be way off.
View 2 Replies
View Related
Feb 6, 2008
I'm dealing with a lot of spreadsheets put together by people who are not that familiar with Excel. Because of this, there are many instances where the formulas created include constants. For example: = A1 * B1 + 200. I want to create a VBA routine that will cycle through the cells I selected and "flag" those cells that have a constant in the formula.
View 3 Replies
View Related
Oct 16, 2008
Ideally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.
View 7 Replies
View Related
Jul 12, 2006
We have a spreadsheet in use at the moment which has various lines of information, each line has a customer name, id, request and various other bits of information which needs looking into.
The infomation is arranged onto weekly sheets and is managed manually. Once the customer request is complete, we grey out the background colour. Some outstanding issues background colours are either left blank, yellow or red, depending on whos doing them.
I was wondering if there is a search criteria that we could use that would search through all the sheets in the workbook and find any customer queries that are not greyed out and display them on the results page.
View 8 Replies
View Related
Oct 16, 2006
On a multipage user form, a combobox control allows the user to select a value from a list, which automatically populates textboxes with other fields from the same list. This works perfectly. I tried to set up another combobox that would work in the same manner, but referencing an entirely separate worksheet and returning the appropriate values from that list to its related textboxes. My problem is that both comboxes are automatically populating ALL the textboxes, ie combobox1 returns the correct information in its related textboxes, but also changes the information in the textboxes related to combobox2. Combobox2 does the same in reverse. I can at least see and understand why this is happening - the combobox code for both is telling ALL textboxes with numerical tags to update.
Private Sub cmbPAYERefFirst3Digits_Change()
Dim rFoundSource As Range
Dim strText As String
Dim tBox As Control
If cmbPAYERefFirst3Digits.ListIndex > -1 Then
strText = cmbPAYERefFirst3Digits.Text
Set rFoundSource = Range(cmbPAYERefFirst3Digits.RowSource). Find(What:=strText, _
After:=Range(cmbPAYERefFirst3Digits.RowSource).Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
For Each tBox In Me.Controls ...........................
View 3 Replies
View Related
Dec 10, 2005
I am looking to produce a formula that shows if a number is "found" or "not
found".
I have list of over 1000 numbers in one column (1 number per row) . I am
trying to identify if those values are present in a range of numbers from
another worksheet(also in a single column). My challenge is that the range of
numbers that I am looking in has multiple values, separated by commas in a
single cell. Looks like it was from an Access data dump.
View 9 Replies
View Related
Jul 22, 2006
I am just looking for a way to use the find function but only to display results that end in what I am looking for. For example i am trying to find a sequence of numbers/letters that end in a. If I do a find it displays results for anywhere there is a . in the sequence. I would like just the ones that end in the.
View 2 Replies
View Related
Sep 26, 2006
changing two variables a various amount of times and running the same procedure and copying the resutls into another sheet. Seems like a perfect place for a macro. However, these variables can be chosen from a list that the user wants. So why not build in a listbox for each one. Now I have two listboxes one for variable A and one for variable B.
The procedure in theory goes something like this we change variable A from the base case and then run the procedure for variable B, get the results, then run the scenario again but changing only variable B abnd repeat. Then once, all of the variable B scenarios are done, I want to change the variable A and then repeat and so forth.
That is the background and my main problem at this point, is that have these values in two listboxes, I know how to do the for each loops and such, however, I do not know how to do them for values in the listbox.
How do I identify the values selected in the respective listboxes and then pull them so I only use them for the for each loop?
View 7 Replies
View Related
Aug 19, 2006
why when a column is copied do blank cells no longer become blank. i have a macro below that writes data to a column based when cells are balnk it skips them. it works great except for when you copy a column the cells are no longer blank.
Sub RET_DATA()
For i = 1 To rs.Range("L65536").End(xlUp).Row
Lastrow = Range("A65536").End(xlUp).Row
Cells(Lastrow + 1, 1) = rs.Cells(i, 17)
Next i
End Sub
View 5 Replies
View Related
Jun 13, 2009
I need to search through a worksheet for a value located in the first A column eg. and then put down a flag ( or bettar the searched text ) to che previous row in B column
Es:
I search the text 'xxx' in A column
if I found the searched text 'xxx' in A5 i need to have a flag or better 'xxx' in B4
View 6 Replies
View Related
Dec 2, 2006
Sorry for posting a related question earlier, where I forgot to specify something. I need to find the max in a column of data without doing any filtering or rearrangment of the column -- can this be done, either indicating the max across many columns by highlighting the max box with color or by writing its address in a new box in the bottom?
View 2 Replies
View Related
Jun 10, 2007
I am working on a big data spreadsheet 166 columns X 2500 rows. I need to flag rows that have 12 consecutive cells with a zero.
View 9 Replies
View Related
Nov 17, 2006
Our school system is trying to clean up student records. They have a demographic worksheet of hundreds of records. Each student has a student ID number. We are trying to flag students whose ID numbers in 9th grade do not match an ID number for 10th grade. so we are left with only students who have matching ID numbers for both 9th and 10th grade. We are trying to find the right function(s) to make this work without VBA. I am attaching a small sample file
View 7 Replies
View Related
Jan 8, 2007
I have many small spreadsheets of data organised into 4 columns and anything upto 250 rows. I want to be able to test if all the cells along each row have the same data in each of the 4 cells, so 4 occurences of the same thing. Each row of data will be different. (I'm not intertested in matching the data going down the column).
However, I won't know with each spreadsheet what is the exact data I will be looking for - if all 4 cells don't have the same data, that is fine as this will then require my manual attention which is the purpose of my project.
I just want to avoid having to check through lots of rows of data where all the data does already match.
The data will usually be text but it can be IP addresses and numbers too. I need a formula or set of operators to use for is exercise.
View 7 Replies
View Related
May 20, 2007
Is it possible to run a macro from an excel cell formula?
ie.
=IF(A1=1, RUN MACRO, 0)
View 9 Replies
View Related
Jun 1, 2007
I am trying to write some VBA code for Excel that looks at a several different cells for a specific value and if that value is there returns a prompt message outlining to the user specific cell info. For instance, if some Excel cells in a column show that someone has not paid their bill yet, I would like a message to appear as soon as one opens the spreadsheet showing the bills for what customers are still unpaid.
View 4 Replies
View Related
Aug 18, 2007
to set a conditional format and include a function code in it as well?
I want a spreadsheet to change the color/font of a cell/column based on a comparison of a "start date" cell and a "due date" cell. This is to automate the process of highlighting items that have gone past the due date in the second cell. I was trying to do this by looking at the "now()" function and comparing it to the due date.
View 9 Replies
View Related
Jan 8, 2008
Is there a way to flag data that hasn't been used in another worksheet. For example, a second worksheet consolidates the data into two different groups, but someone spelled the name of one of the groups wrong in the first worksheet and the data was not added via a sumif function.
View 2 Replies
View Related
Nov 13, 2009
1. In neighborhoods that have zero units in a given price range I have it to display "-" , because this unit is not actually zero, the data is not available. Therefore a #VALUE! is displayed for the percent because it cannot calculate the "-". How do I get excel to glance over "-" and flag it for no calculation?
2. For the percentages I am having to manually do them row by row. I would like to set it up in a manner that allows me to copy the formula down by column and across by row correctly.
For instance in the percent for Mira Lagos I have =B4/N3 where b4 is the units for mira lagos and n3 is the total. I can drag that formula across by rowto get all the correct percentages for mira lagos price ranges only, but I cannot copy this formula down by column to any of the other neighborhoods. In otherwords I have to do a new formula for each subdivision.
e.g.
Grand Peninsula=B5/N3
Meadow Glen(Mansfield)=B6/N3
...etc
Again I would like to make it so I can copy the formula across by row and down by column so excel will automatically compute it.
View 3 Replies
View Related
Feb 14, 2008
Assist me in developing a macro that would search for unique values in column A and then place a "1" in column N of that row to signify it as the unique value. I am trying to do this in order to get a unique count of projects listed because the list I receive has multiplie occurences of the same project # in column A.
View 9 Replies
View Related
Jun 27, 2006
I am trying to set up a spreadsheet to track certification expiration dates and things of that nature at my volunteer fire department. At my career job, we use specialty software like Firehouse, but that is way too expensive so I must settle for Excel..or maybe Access.
Basically, I need a formula that will look at todays date and compare it to the date in a given cell and then somehow differentiate thise which are expiring at a given intercal.
For example, if someones EMT was expiring in 6 months it would turn yellow, then at 3 months turn blue, then red at 1 month then black if it expires.
It don't necessarily have to be just like that, just some way to differentiate depending on the how close to expiration it is.
View 3 Replies
View Related
Oct 4, 2006
I have 2 named ranges that are one besides the other - let's name them tTableA and tTableB. I also have a strValue, which holds a String I will be searching for in tTableA.
Now, tTableA contains names (strings), while tTableB contains quantities (numbers) for the corresponding names.
I need a Button that, when clicked, will do this:
1) Check to see if the strValue string is present in tTableA:
1.1 If NOT present, tell the user "Error".
1.2 If present continue
2) Now that we know strValue exists in tTableA, find the corresponding quantity located in tTableB (this quantity would be on the same 'row' as the row in tTableA which contains strValue)
2.1 If quantity <= 0 then tell the user "Nothing left"
2.2 else REDUCE that quantity by 1 unit.
View 8 Replies
View Related
Jan 9, 2007
I am currently trying to work out what the best way would be to search between two sheets and find out where the differences are. IO have sheet 1 and 2 which both have account numbers and details on. I searching off the first column on both sheets and trying to identify which records exist on sheet 1 but not on sheet 2. The code I have so far is
Sub check()
data_sheet = "Sheet1"
target_sheet = "sheet2"
rowcn = 2
Do
If Sheets(data_sheet). Cells(rowcn, 1) <> Sheets(target_sheet).Cells(rowcn, 1) Then
Rows(rowcn).Select
Selection.Font.ColorIndex = 3
End If
rowcn = rowcn + 1
Loop While Sheets(data_sheet).Cells(rowcn, 1) > 0
End Sub
This currently works 50%. Only problem is that this code is not independant to each list, it simply looks at the same cell reference on both sheets and check whether the value is there. It doesnt actually go down the whole list and checks whether it is there.
View 5 Replies
View Related
Aug 9, 2007
I have been trying to work on this thing for like 3hours and i could not make even a single line since I dont get what loops I should make and how I should do it. Could someone suggest me how to do this.
1. check the date.Calculate two weeks backwards.
2. Check for id no which is scanned more than once in that two weeks from the current date.
3. Check for type of ppe if its different then leave it if they are same and exceeds 3 of the same type it should BOLD the badge id.
Have attached the sample file.
View 5 Replies
View Related
Mar 9, 2008
I have stock data values being updated on my sheet every 10 seconds in one Cell (E5).
I am looking to indicate a change in that cell value by changing cell color (or could be any easily programmable indicator). I want to change the cell color on first change of value but not subsequent refreshes. Stock data is numeric in string form. colors used not important as long as there is a visible change.
I Have tried playing with sheet change events but no progress as data is not manually entered.
View 3 Replies
View Related
May 7, 2008
I have some data stored in a database and when it is extracted into excel each entry's data is presented in both horizontal and vertical formats. e.g.
Entry1 value1 value2 value3
value4
value5
value6
Entry2 value1 value2 value3
value4
value5
value6
I have a macro that will transpose the vertical data into horizontal format but if there are not the expected number of 'value' items below the entry it will not work correctly. If in the above example Entry2 only had value4 and value5 below it the macro would fail.
What I need is a way to validate that each entry has the correct number of values below it before the macro is run and some way of highlighting or drawing attention to these problems.
View 9 Replies
View Related
