number (1-40) is entered into column I. If that same number is in column I above enter "0" in column J on same row as number just entered, if it is not in column I above the new entry, enter a "1" in column J. I cannot get anything to work.

Any help appreciated

Thank, Don

✔ Best Answer

Hi DerbyDad03, Here are the two formulas side by side

I J K 3 I:I $I$4:I4 4 2 0 1 5 1 0 1 6 2 0 0 7 2 0 0 8 3 1 1 9 1 0 0

They give different results when a duplicate number is entered further down the column.Obviously it is up to the OP which is more appropriate, but from what I read,

As soon as I enter a number in a cell that has a duplicate number anywhere above it, BOTH numbers change to a "0" in the J-column for that number.it is an issue.I do not, of course, know whether my revision will meet the OP's requirements.

Regards

Try this in J1 and drag it down as far as you think you will ever need. =IF(I1<>"",IF(COUNTIF(I:I,I1)>1,0,1),"")

If you don't want formula in Column J, then you could use VBA.

Let us know.

Here's the code to do it with VBA. Right click the Sheet Tab, choose View Code and paste this into the pane that opens.

Private Sub Worksheet_Change(ByVal Target As Range) 'Turn off Events Application.EnableEvents = False 'Was change made to Column I? If Target.Column = 9 Then 'If yes, count occurances of new entry If WorksheetFunction.CountIf(Range("I1:I" & Target.Row), Target) > 1 Then 'Set Column J based on result Target.Offset(0, 1) = 0 Else: Target.Offset(0, 1) = 1 End If End If 'Enable Events Application.EnableEvents = True End Sub

Cannot get that to work. I certainly appreciate the fast response and your time. Please allow me to give more detail. Starting at column and cell I4 a number between 1-40 is entered. That is first entry in column I so a "1" goes into cell J4. I have that working with =IF(I4>0,1). Cell I5 then has a number between 1-40 entered into it. IF that number is not the same number as in I4, a "1" goes into cell J5, if it is the same, a "0" goes into J5. I have that working with =IF(I5=I4,"0","1").

The third row is row 6. What I am trying to do is when a number between 1-40 is entered into cell I6, if it is a duplicate of any of the 2 cells above it in column I, a "0" is entered into cell J6. If it is not a duplicate of any cell above in column I above it, I want to enter a "1" into cell J6.

Hope this makes sense and is clearer than previous post.

Thanks for any assistance and your time.

Don

I understood your first post and just tested both of my suggestions based on your second post. They were both modified to start in I4. They both returned 1 for new entries that don't match any of the numbers above it and 0 if a duplicate exists.

Neither of them deal with the 1 - 40 issue, but that is easily added once you get them working in your spreadsheet.

Please be more specific when you say "Cannot get that to work." What actually happens?

Hello DerbyDad03, First thanks for the help and time. You are correct that I should have better explained myself earlier. I enter the formula in J4 changing both of the I:1 to I:4 and enter that into the formula bar for cell J4. Then do a copy and paste for the rest of the page in the J-column. I am getting a "0" in J4 and it should always be a "1" and a "1" everywhere else that there should be a "1". As soon as I enter a number in a cell that has a duplicate number anywhere above it, BOTH numbers change to a "0" in the J-column for that number.

Did I do something wrong???

Again thanks for your time.

Don

Hi DerbyDad03, Shouldn't it be:

=IF(I4<>"",IF(COUNTIF($I$4:I4,I4)>1,0,1),"")

You were counting values in all cells in column I, so whilst it works for the first time a new number is entered in column I, it reverts to 0 when that number is entered again.

The COUNTIF() should only test cells 'above' the cell being tested.

In row 20 for example the formula becomes:

=IF(I20<>"",IF(COUNTIF($I$4:I20,I20)>1,0,1),"")Regards

Humar: I understand the difference in your formula (only count values above the last value) vs. mine (count all values in I:I) but I don't quite see why it is an issue.

I get 0 next to every duplicate, including all new ones, and 1 next single values.

Granted, I am assuming that the OP is not entering values in middle of the column, but only adding them at the bottom, as described in Response # 3. If numbers are being entered in the middle and the OP wants to ignore values below the most recent entry, then your formula fits that need better.

Hi DerbyDad03, Here are the two formulas side by side

I J K 3 I:I $I$4:I4 4 2 0 1 5 1 0 1 6 2 0 0 7 2 0 0 8 3 1 1 9 1 0 0

They give different results when a duplicate number is entered further down the column.Obviously it is up to the OP which is more appropriate, but from what I read,

As soon as I enter a number in a cell that has a duplicate number anywhere above it, BOTH numbers change to a "0" in the J-column for that number.it is an issue.I do not, of course, know whether my revision will meet the OP's requirements.

Regards

Humar & DerbyDad03, Got both to work finally. My special thanks to both of you for your valuable time. It has been a lot of help and I'm learning at the same time.

I'm going to work on the other 2 columns now. To start with I have a table on a new worksheet on the same excell spreadsheet. It is named dxcc. Column A on that worksheet contains things like the following:

3A

3B6, 7

3B8

3B9

3C

3C0

3D2

3D2

3D2

3DA

3V

3W, XV

3X

3Y

3Y

4J, 4K

4L

4O

4S

UA-UI1, 3, 4, 6 RA-RZ

UA2

UA-UI8, 9, 0 RA-RZ

UJ-UM

UN-UQ

UR-UZ, EM-EO

V2

V3

V4

V5

V6

V7

V8

and so on for about 340 rows.I want to do basically the same thing as with columns I and J above. For example if a 4J, 4K, 4L, 3Y, etc. is entered in column G, a "1" will be entered automatically in column H. If it is entered again on down the sheet, a "0" will be entered.

Question: do I first need to go to the other sheet and put every one of the "prefixes" on a separate line in liew of the groups as shown above???

I better get to work and see what I can do.

Thanks much,

Don

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History