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.

## Related Results

### FInd a cell value in a closed worksheet

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

### Finding Min Cell values excluding zero in alternate columns

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

### RE: Finding Min Cell values excluding zero in alternate columns

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
>

### Finding Min Cell values excluding zero in alternate columns

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

### Enter data only if it already exists in another table

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?

### How do I not include empty cell value chart labels in pie charts?

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.

### How do I not include empty cell value chart labels in pie charts?

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.

### How to apply conditional formatting to cells based on their previous cell value.

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.

Thanks

### Check an entered value in a cell in real time, and if the value already exists

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

### Determine If ComboBox Value Exist in Range

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
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

```

### How we can find that whether a particular column exists in the Excel file or not

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.

### If cell value already analyzed in loop, discard it

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
.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.

### Findings Of Multiple Value In Workbook At Once

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

Anil

### Value and text in the same cell

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.

### Find a Cell Value In a Range

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.

### Macro to check if a value exist in sheet and add it if it doesnt

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.

### "Find" method when two columns in the same row

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:
Baseball Devil Rays

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

### Create Worksheet based on cell value

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!

### Need to find the smallest, second smallest etc in a column

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.

### Determining if a value already exists in column(s)

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?

### Change value of cell automatically if entered the wrong number

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!!!

### Find comment replace cell value

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,

### Find a Cell Value (Revised)

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

### Find comment replace cell value

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.