Free Microsoft Excel 2013 Quick Reference

Finding Whether Cell Value Already Exists in the Column List

Hi all,

I have a list of alphanumeric IDs and I would like to use a formula to inform me whether there is the same ID later on in the column or not. I know you can find unique or duplicate entries using conditional formatting to highlight the cells in Excel 2010 but I would like it to be in a formula and not highlighting.

I have attempted to come up with this in cell B1: {=IF(MATCH(A1,A:A,0), "Duplicate", "Unique")}, but, of course, all the cells in column B will compute to be "Duplicate" since it will also consider itself (i.e. cell A1).

Could someone suggest a better method or different formula to do this?

Many thanks in advance for anyone who helps me.


Post your answer or comment

comments powered by Disqus
Please help me in solving this

In my scenario i need to find a cell value which is in another workbook (located in another machine).How Can i use FIND function in this scenario for finding the proper value or please guide me in proper way

thanks in regards

If your input array is in A21:H21,
=IF(MAX(A21:H21)

If your input array is in A21:H21,
=IF(MAX(A21:H21)<=0,"NA",LARGE(A21:H21,COUNTIF(A21:H21,">0")))
should return the smallest positive value (or "NA") if there isn't one. The
logic is to count the number of positive values, and use that as an argument
to the LARGE function. --Bruce

"MichaelC" wrote:

> I have an array that is 1 row high by 16 columns wide.
> Each cell may contain a positive value, or a zero.
>
> I need a formula to find the "Minimum value that is greater than zero" in
> columns 1,3,5,7,9,11,13 and 15.
>
> =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
> need the minimum value that is greater than zero.
> If I use nested IF functions to exclude zeroes I run foul of the max of 7
> allowed.
> I would greatly appreciate any help and thank you in advance for any offered.
> MichaelC
>

I have an array that is 1 row high by 16 columns wide.
Each cell may contain a positive value, or a zero.

I need a formula to find the "Minimum value that is greater than zero" in
columns 1,3,5,7,9,11,13 and 15.

=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
need the minimum value that is greater than zero.
If I use nested IF functions to exclude zeroes I run foul of the max of 7
allowed.
I would greatly appreciate any help and thank you in advance for any offered.
MichaelC

I'm trying to do some data validation here. Basically in one table I have a primary key, and then in another table it's a foreign key and I only want the user to be able to enter something in the second table if it already exists in the first.

I checked the Access help and found it a little confusing - it says "For field and record validation rules, the expression can't contain user-defined functions, domain aggregate or aggregate functions, the Eval function, or CurrentUser method, or references to forms, queries, or tables. In addition, field validation rules can't contain references to other fields."

However I did a bit more reading and it seems like I could use dlookup for this. Is that the case?

I have a pie chart where some of the cell values are zero, but the chart will
include all the labels. It makes the pie chart look too cluttered. How can
I not include zero value or blank cell values from appearring in the labels.

When I go to "tools", "options", and "chart", the "plot empty cells as"
area is shaded grey and I can not select options.

I have a pie chart where some of the cell values are zero, but the chart will
include all the labels. It makes the pie chart look too cluttered. How can
I not include zero value or blank cell values from appearring in the labels.

When I go to "tools", "options", and "chart", the "plot empty cells as"
area is shaded grey and I can not select options.

Hi,
I want to highlight all cells in a column based on their previous cell value.
For example....
In the column H, if h2<h1 or h3<h2 or h4<h3.....so on...they must be highlighted with red color indicating the lesser cell value than the previous one.

I can format only one cell based on above condition but unable to do this with all the cells in that column with the above condition.

Please help.

Thanks

Hi all,

I have a workbook with multiple worksheets that hold customer data. Each list is rather large, so i need a way to control data entry with a view to avoiding data duplication.

Is there a way (perhaps via VB scripting) that I can have Excel check an entered value in a cell in real time, and if the value already exists elsewhere in the workbook, a message is displayed?

Million thanks for any feedback.

de049

Hi All,

The code below loops thru a specified range I12:I26 for a duplicate combobox value in each cell. When I enter the same combobox value in range I43:I54 it still finds a duplicate. I'm at a lost.


	VB:
	
 cmbSel_WBS.Value 
Case Is > Empty 
    On Error Resume Next 
    sAdd_WBS = cmbSel_WBS.Value 
    Set rAdd_WBS = ActiveSheet.Range("I12:I36") 
    For Each rCell In rAdd_WBS 
        If rCell.Value = sAdd_WBS Then 
            MsgBox ("The WBS Number selected already exists in this column.") 
            Exit Sub 
        End If 
    Next rCell 
    On Error Goto 0 
End Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance.

Hello

Thanks for ur supports

I have a problem , i have to find in a excel sheet whether a partilcular column exist in the excel sheel or not by VBA code.

if that column name exist in the Excel file then i will use a condistional statement on the basis of results

Please tell me how to do this.

Hi,

I have the following code to send email through vba :


	VB:
	
 sendmail() 
    Dim OutApp As Object 
    Dim OutMail As Object 
    Dim cell As Range 
    Application.ScreenUpdating = False 
    Set OutApp = CreateObject("Outlook.Application") 
    On Error Goto cleanup 
    For Each cell In Columns("d").Cells.SpecialCells(xlCellTypeConstants) 
        If cell.Value Like "?*@?*.?*" And _ 
        LCase(Cells(cell.Row, "e").Value) = "yes" Then 
            Set OutMail = OutApp.CreateItem(0) 
            On Error Resume Next 
            With OutMail 
                .To = cell.Value 
                .Subject = "test" 
                .Body = "This is a test" 
                 'You can add files also like this
                .Attachments.Add (filepathstring) 
                .Send 'Or use Display
            End With 
            On Error Goto 0 
            Set OutMail = Nothing 
        End If 
    Next cell 
cleanup: 
    Set OutApp = Nothing 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The thing is within my list of email, the same email can be there more than once, but I would like to only send one email to that person.

How do you modify the for each loop code so that if the cell.Value in column D was already "analyzed", then skip it.

Thank you for your help.

Hi all,
I want to know VBA code regarding findings multiple value in whole workbook at once. Suppose a workbook consist 12 sheet and every sheet has some value.In 13th sheet i put some new value and try to find this new value is existing in any old sheets? If yes display that value in the next cell in 13th sheets. For example:- i put 610432 in 13th sheet(Sheet1 in attachment) and run programme to find the value. So it should display the value 610432 in 2 column of 13th sheet(Sheet1) because it is existing in Sheet Jan.

I hope it will be clear. If not please let me know on the following mail
anilatjava@sify.com

Thanks in advance

Anil

I am sure this question has been asked in the past but i cant find the answer using the search function.

I am trying to figure out how to put a value and text in the same cell when the value in the cell is used as a variable for a formula in another cell.

For example: cell b3 has a value of 6 and cell c3 is multipying the value in b3 with the value in another cell. I would like to know how to add text to cell b3 and still have the value in that cell used in the formula in cell c3

Any help would be appretiated.

I would like to look-up a cell's value in a range of cells and return 1 if it
exists or zero if it does not exist in the specified range. I have got to
believe this is a fairly common need. Can anyone help me out with this.

Hi,

I need help from you experts on this problem I'm having:

I've attached a sample workbook with the post.

I need a macro to go through the values in column A in the "reference" tab and check if the value already exist in column B in "master" sheet. If it does, then do nothing for that cell and continue on to the next cell, if it doesn't exist then add all three values for that row (column A, B and C) to the end of the table in "master" sheet and then go to the next cell. Go through all the values in column A in the "reference" tab.

Please help.

Hey all,

I have a program where the user selects a number in a cell based on a criteria. This number can be under a group (Baseball), and a sub group (Yankees). If a number, 2 (restricted by data validation), is selected for Baseball: Yankees, it puts Baseball in one column and Yankees in the next column to form a list that meets this criteria. If both Baseball and Yankees are in the same row, I don't want it to do anything. I run into trouble in my code if I have a list such as:
Basketball Yankees
Baseball Devil Rays
Basketball Cavaliers...

If I select the criteria 2 for Baseball Yankees, it does not add it to my list. That is because my code sees Basketball first, so it then searches for Yankees. Since it sees Yankees, it does nothing. I would like my code to say if there "Basketball" exists in column P, look in column Q in the same row where Basketball exists, and if Yankees exists in the column Q, do not move the values to columns P and Q.

If Target.Address
= "$H$9" Then
    Application.EnableEvents = False
    If Target.Value = "2" Then
        Set findit = Columns("P:P").Find(What:="*")
        If findit Is Nothing Then
            Cells(7, "P").Value = Cells(9, "G")
            Cells(7, "Q").Value = Cells(8, "B")
        Else
            Set findit = Columns("P:P").Find(What:="Basketball")
            If findit Is Nothing Then
                Cells(Rows.Count, "P").End(xlUp).Offset(1, 0).Value = Cells(9, "G")
                Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = Cells(8, "B")
            Else
                Set findit1 = Columns("Q:Q").Find(What:="Yankees")
                If findit1 Is Nothing Then
                    Cells(Rows.Count, "P").End(xlUp).Offset(1, 0).Value = Cells(9, "G")
                    Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = Cells(8, "B")
            End If
        End If
End If
Is there possibly a way to incorporate that into the .Find(What:= field?

Thanks ahead of time,

Lucas

Hello,

I have been using the following code to generate a new workbook based on cell value using a template workbook...


	VB:
	
 Range) 
    Dim wbNew As Workbook 
    If Target.Cells.Count > 1 Then Exit Sub 
     
     
    On Error Resume Next 
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 
        Set wbNew = Workbooks(Target.Text) 
        If wbNew Is Nothing Then Workbooks.Open ThisWorkbook.Path & "" & "Template.xls" 
        On Error Resume Next 
         
         
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & (Target.Text) 
         
         
        ActiveWorkbook.Close SaveChanges:=True 
         
         
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


This code work well for me, but there are three issues:

1) When I delete a cell, it tries to create a new workbook
2) When a workbook based on cell value already exists, and we try to create a new cell with the same value, it tries to overwrite the existing workbook ---> I want to make it so if it already exists it does not try to overwrite it
3) I cannot delete a row by right clicking the row and clicking delete, it freezes excel. I can only do it by selecting the row and going into Edit --> Delete.

I appreciate the help in fixing this.

Thanks!

I have a column with 80 cells. Only 8 values can show up in the column although it is possible for as little as one value to show up in all 80 cells or all eight values to appear. They will never show up in any numerical order. I know how to find the min and max values in the list, but I need to create 8 cells elsewhere on the same sheet that will show the different values in the column.

So lets say that the column is F. I need commands in in cells A1-A8 to show the different values that are in the column. Further, lets say that the values that show up are 1200, 1400, 1600 and 2200 as the only values in the column (again, being listed multiple times a piece). What commands could I enter in the A1-A8 that would show those values in the first four cells and leave the rest either blank or with a #NUM! statement? Thanks and please, if this is not clear, let me knowand I will try again.

I have the following data set

Order Order Type Group 1 Group 2 Group 3 OthrGroup 1
782187 SLS PRT EQP
782679 SLS BPR
783014 WWW EQP PRT EQP
783016 WWW EQP PRT EQP

See in the first line, the EQP does not exist in group 1-3 so it must add it
to the groups in the next available slot which is Group 2. However in the
other lines you will see that EQP exists in group1 and therefore should not
be added. There are three "othrGroups" as well. So basically, there were
two worksheets, I used one worksheet that has group1-3, then used vlookup to
get the values from the other worksheet per order number. THen I just need
to combine them but I am having troubles and it shouldn't be this difficult.
Any idea's?

I (sometimes) have a row with numbers from 1 to 5...

What I want is: when I enter value = 3, and that value (3) already exists in
the column, it has to correct that automatically into 4, with or without a
warning.

Sometimes the row don't have values in it at all, if the value 2 is entered
then, it automatically has to change to 1.

In short: it has to change automatically in the first unused value closest
to 0...

Thanks for the help!!!

Hi. I've searched through previous posts, and it seems that none is
what I am looking for. Everyone else seems to want to find cells with
specific text in the comment and replace that text within the comments.
What I need is to find all cells with specific text in the comments,
then replace the cell values. "Find all cells with "xxx" in the
comments and change all of the cell values to "123". I'd do it one by
one but there are hundreds. None of my macros seem to work. Any
ideas?

Thanks.
Kind regards,

Hello,

I'm just trying to write code that will find a cell value from one sheet to
another. On Sheet1, I want say cell "B11" to be what I'm looking for(what
ever value that is in cell "B11" is the value that I'm looking to find on
Sheet2). On Sheet2, I need it to search through say range "C1:C5001" and have
it select the first one it finds. How would I go about doing that?

Thanks

Hi. I've searched through previous posts, and it seems that none is
what I am looking for. Everyone else seems to want to find cells with
specific text in the comment and replace that text within the comments.
What I need is to find all cells with specific text in the comments,
then replace the cell values. "Find all cells with "xxx" in the
comments and change all of the cell values to "123". I'd do it one by
one but there are hundreds. None of my macros seem to work. Any
ideas?

Thanks.
Kind regards,


No luck finding an answer? You could always try Google.