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

✔ Best Answer
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.

Regards



#1
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(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.


Report •

#2
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 •

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

Don


Report •

Related Solutions

#4
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 •

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

Don


Report •

#6
March 11, 2010 at 12:19:31
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


Report •

#7
March 11, 2010 at 13:36:47
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.


Report •

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


Report •

#9
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:
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


Report •

Ask Question