Auto entry in column

Microsoft Microsoft excel 2007 (pc)
March 11, 2010 at 09:49:18
Specs: Windows XP
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

See More: Auto entry in column

Report •

March 11, 2010 at 10:13:47
Try this in J1 and drag it down as far as you think you will ever need.


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

Let us know.

Report •

March 11, 2010 at 10:47:10
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

Report •

March 11, 2010 at 11:00:58
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.


Report •

Related Solutions

March 11, 2010 at 11:19:47
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?

Report •

March 11, 2010 at 12:15:33
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.


Report •

March 11, 2010 at 12:19:31
Hi DerbyDad03,

Shouldn't it be:


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:


Report •

March 11, 2010 at 13:36:47

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.

Report •

March 11, 2010 at 14:27:36
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.


Report •

March 11, 2010 at 14:29:37
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:
3B6, 7
3W, XV
4J, 4K
UA-UI1, 3, 4, 6 RA-RZ
UA-UI8, 9, 0 RA-RZ
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,


Report •

Ask Question