checking data…in excel (2000)

Topic

New Reply

I have 14 columns of numeric data. The data is in rows 1-12 columa. It gets updated every day by adding a new column (say column 15). How do I have the data check column 14 to ensure that the data is not the same data I’m getting for my new day. In other words I don’t want to put the same data I just inserted in column 14 within column 15.

Reply | Quote

Replies

You could compare it row by row, or use MATCH. How you do it will depend on how and when you want to check and what the source is.

Could you give us more details?

Steve

Reply | Quote

You could compare it row by row, or use MATCH. How you do it will depend on how and when you want to check and what the source is.

Could you give us more details?

Steve

Reply | Quote

ok this might help… here is the actual code I’m using:
Sub Get12()
Dim LastCol As Long
LastCol = Worksheets(“data entry”).Range(“IV3”).End(xlToLeft).Column + 1
Worksheets(“Sheet1”).[A2:A13].Copy Destination:=Worksheets(“data entry”).Cells(3, LastCol)
End Sub

now I need to be able to check colum+1 against last column to ensure I don’t have two colums with the exact same data in them. If it is the same data I need to inform user that the data is the same data from yesterday (last column).

how do I do this in excel????

Reply | Quote

Here is an ad hoc solution; you could make it more general if you like. It tests before copying, but you can change that.

Function CompareCols(col As Long) As Boolean
Dim i As Long
Dim s As Long
For i = 2 To 13
s = s – (Worksheets(“Sheet1”).Cells(i, 1) = Worksheets(“data entry”).Cells(i + 1, col))
Next i
CompareCols = (s = 12)
End Function

Sub Test()
Dim LastCol As Long
LastCol = Worksheets(“data entry”).Range(“IV3”).End(xlToLeft).Column
If CompareCols(LastCol) = True Then
If MsgBox(“The new data are equal to the last data. Do you want to proceed?”, _
vbQuestion + vbYesNo) = vbNo Then
Exit Sub
End If
End If
Worksheets(“Sheet1”).Range(“A2:A13”).Copy Destination:=Worksheets(“data entry”).Cells(3, LastCol + 1)
End Sub

Reply | Quote

Here is an ad hoc solution; you could make it more general if you like. It tests before copying, but you can change that.

Function CompareCols(col As Long) As Boolean
Dim i As Long
Dim s As Long
For i = 2 To 13
s = s – (Worksheets(“Sheet1”).Cells(i, 1) = Worksheets(“data entry”).Cells(i + 1, col))
Next i
CompareCols = (s = 12)
End Function

Sub Test()
Dim LastCol As Long
LastCol = Worksheets(“data entry”).Range(“IV3”).End(xlToLeft).Column
If CompareCols(LastCol) = True Then
If MsgBox(“The new data are equal to the last data. Do you want to proceed?”, _
vbQuestion + vbYesNo) = vbNo Then
Exit Sub
End If
End If
Worksheets(“Sheet1”).Range(“A2:A13”).Copy Destination:=Worksheets(“data entry”).Cells(3, LastCol + 1)
End Sub

Reply | Quote

ok this might help… here is the actual code I’m using:
Sub Get12()
Dim LastCol As Long
LastCol = Worksheets(“data entry”).Range(“IV3”).End(xlToLeft).Column + 1
Worksheets(“Sheet1”).[A2:A13].Copy Destination:=Worksheets(“data entry”).Cells(3, LastCol)
End Sub

now I need to be able to check colum+1 against last column to ensure I don’t have two colums with the exact same data in them. If it is the same data I need to inform user that the data is the same data from yesterday (last column).

how do I do this in excel????

Reply | Quote

hans – first of all thank you for replying so fast. second of all where do I put your new macro at before my old one or after it. Pleae inform… I’m still a newbe at this thanks p.s. see prior mail for my macro…

Reply | Quote

The code I posted was meant as replacement for the macro you had. If you like, you can rename Test to Get12.

You should copy both the Function and the Sub into your module. The Sub needs the Function to work.

Reply | Quote

LastCol = Worksheets(“data entry”).Range(“IV3”).End(xlToLeft).Column

receiving RUN time error “9”
subscript out of range and it’s pointing to the command above… what does X1toleft mean…. my columns go from left to right but I want to compare the left (new) column to the right (old column), also what does IV3 mean

Reply | Quote

1) is there a worksheet in the activeworkbook named “data entry”? It would seem that you do not.

It is “xlToLeft” not “x1ToLeft” It is an excel (xl) constant.

The line starts in the “data entry” sheet in cell IV3 (3rd row the very last column) and then moves to the left (it is in the furthest right column) until the first non-blank column. This is the last column with an entry. It then gets the column number of that column and stores it in the variable called “LastCol”

Steve

Reply | Quote

every time I run the macro it put the same data in a different column still I need something that will prevent this. His test function isnt working is there something wrong with it?

Reply | Quote

>> His test function isnt working
Perhaps he doesn’t understand what you want?

Could you post a small workbook that demonstrates what you are working with? Replace sensitive data with dummy data, and indicate in the workbook itself or in the post itself what should go where?

Reply | Quote

how do I do that?

Reply | Quote

3:00 77 65 65
3:30 77 67 67
4:00 77 72 72
4:30 77 62 62
5:00 77 49 49
5:30 79 79 79
6:00 77 91 91
6:30 77 285 285
7:00 77 321 321
7:30 77 196 196
8:00 77 253 253

total jobs 849

the first column is just the time of day. the other columns are filled with data. take notice that the 2nd and third columns have the same data in them. somebody ran the macro before the jobs were finished… hence it put out yesterdays data thats why the two columns are the same. I need to prevent this from happening again. but how?

Reply | Quote

3:00 77 65 65
3:30 77 67 67
4:00 77 72 72
4:30 77 62 62
5:00 77 49 49
5:30 79 79 79
6:00 77 91 91
6:30 77 285 285
7:00 77 321 321
7:30 77 196 196
8:00 77 253 253

total jobs 849

the first column is just the time of day. the other columns are filled with data. take notice that the 2nd and third columns have the same data in them. somebody ran the macro before the jobs were finished… hence it put out yesterdays data thats why the two columns are the same. I need to prevent this from happening again. but how?

Reply | Quote

When you are composing a new post or a reply, there is a box where you can enter the path to a file to attach; you can also click the Browse button. See screenshot.

You can attach files of up to 100 KB in size; if the workbook is larger than that, create a ZIP file from it and attach that.

Warning: previewing a post or reply clears the “Attach a file” box, so you must fill the box just before actually posting.

Reply | Quote

HansV – this is my problem Every time I open the spreedsheet called data entry It not only updates todays date but yesterdays as well. How can I fix this? I need a solution ASAP before saturday night 05/15/04.
St. Louis
10-May 11-May 12-May 13-May 14-May 15-May 16-May
3:00 23 27 26 26 21 21
3:30 43 34 28 27 31 31
4:00 37 33 12 17 26 26
4:30 30 24 10 13 16 16
5:00 27 25 20 15 16 16
5:30 36 33 43 21 25 25
6:00 20 28 17 13 17 17 < ——see how it duplicates
6:30 46 59 39 32 59 59 the whole column
7:00 22 26 27 14 24 24
7:30 17 19 17 15 21 21
8:00 30 28 20 20 25 25

Total Jobs 331 336 259 213 281 281 0

Ohio
10-May 11-May 12-May 13-May 14-May 15-May 16-May
3:00 21 23 27 20 25
3:30 30 53 24 62 29
4:00 43 51 23 46 27
4:30 50 39 57 50 28
5:00 50 31 16 42 23
5:30 49 25 12 31 20
6:00 40 20 7 27 15
6:30 23 17 7 20 21
7:00 38 23 16 26 19
7:30 22 17 21 15 22
8:00 28 23 21 25 21

Total Jobs 394 322 231 364 250 0 0

Reply | Quote

Could you provide a copy of your code, and some test data?

Hans’ original routine works on test samples I created and (i would also assume) on tests that he did.

Steve

Reply | Quote

this is the code i’m using.

Function CompareCols(col As Long) As Boolean
Dim i As Long
Dim s As Long
For i = 2 To 13
s = s – (Worksheets(“Sheet1”).Cells(i, 1) = Worksheets(“data entry”).Cells(i + 1, col))
Next i
CompareCols = (s = 12)
End Function

Sub Get12()
Dim LastCol As Long
LastCol = Worksheets(“data entry”).Range(“IV3″).End(xlToLeft).Column
If CompareCols(LastCol) = True Then
If MsgBox(” Please Check to ensure that the Jobs ran good. Is it past 10:15am EST? The new data is equal to the last data. Do you want to proceed?”, _
vbQuestion + vbYesNo) = vbNo Then
Exit Sub
End If
End If
Worksheets(“Sheet1”).Range(“A2:A13”).Copy Destination:=Worksheets(“data entry”).Cells(3, LastCol + 1)
ActiveWorkbook.Save
End Sub

this is my data that I import from a text file on another drive. (what you see is what it is).
21
31
26
16
16
25
17
59
24
21
25

The data is imported into sheet1 column A rows 2 through 12. I then on Sheet 2 have the macro (up above) bring the data in and update my columns.
The problem resides in when ever I open the spreedsheet it is automatically updated but it updates todays column and yesterdays column. I just want it to update todays column.

Reply | Quote

I second Hans’ comments. I have just back from a business trip and you haven’t given us anymore to work with than you posted originally even though we have asked for an example workbook on a number of occasions.

Steve

Reply | Quote

I second Hans’ comments. I have just back from a business trip and you haven’t given us anymore to work with than you posted originally even though we have asked for an example workbook on a number of occasions.

Steve

Reply | Quote

this is the code i’m using.

Function CompareCols(col As Long) As Boolean
Dim i As Long
Dim s As Long
For i = 2 To 13
s = s – (Worksheets(“Sheet1”).Cells(i, 1) = Worksheets(“data entry”).Cells(i + 1, col))
Next i
CompareCols = (s = 12)
End Function

Sub Get12()
Dim LastCol As Long
LastCol = Worksheets(“data entry”).Range(“IV3″).End(xlToLeft).Column
If CompareCols(LastCol) = True Then
If MsgBox(” Please Check to ensure that the Jobs ran good. Is it past 10:15am EST? The new data is equal to the last data. Do you want to proceed?”, _
vbQuestion + vbYesNo) = vbNo Then
Exit Sub
End If
End If
Worksheets(“Sheet1”).Range(“A2:A13”).Copy Destination:=Worksheets(“data entry”).Cells(3, LastCol + 1)
ActiveWorkbook.Save
End Sub

this is my data that I import from a text file on another drive. (what you see is what it is).
21
31
26
16
16
25
17
59
24
21
25

The data is imported into sheet1 column A rows 2 through 12. I then on Sheet 2 have the macro (up above) bring the data in and update my columns.
The problem resides in when ever I open the spreedsheet it is automatically updated but it updates todays column and yesterdays column. I just want it to update todays column.

Reply | Quote

Could you provide a copy of your code, and some test data?

Hans’ original routine works on test samples I created and (i would also assume) on tests that he did.

Steve

Reply | Quote

The Lounge is run entirely by volunteers. You can’t demand solutions to be posted before a specified date and time. (I myself was abroad for a few days, so I didn’t even see your post until now.)

You still haven’t posted a sample spreadsheet.

Reply | Quote

The Lounge is run entirely by volunteers. You can’t demand solutions to be posted before a specified date and time. (I myself was abroad for a few days, so I didn’t even see your post until now.)

You still haven’t posted a sample spreadsheet.

Reply | Quote

HansV – this is my problem Every time I open the spreedsheet called data entry It not only updates todays date but yesterdays as well. How can I fix this? I need a solution ASAP before saturday night 05/15/04.
St. Louis
10-May 11-May 12-May 13-May 14-May 15-May 16-May
3:00 23 27 26 26 21 21
3:30 43 34 28 27 31 31
4:00 37 33 12 17 26 26
4:30 30 24 10 13 16 16
5:00 27 25 20 15 16 16
5:30 36 33 43 21 25 25
6:00 20 28 17 13 17 17 < ——see how it duplicates
6:30 46 59 39 32 59 59 the whole column
7:00 22 26 27 14 24 24
7:30 17 19 17 15 21 21
8:00 30 28 20 20 25 25

Total Jobs 331 336 259 213 281 281 0

Ohio
10-May 11-May 12-May 13-May 14-May 15-May 16-May
3:00 21 23 27 20 25
3:30 30 53 24 62 29
4:00 43 51 23 46 27
4:30 50 39 57 50 28
5:00 50 31 16 42 23
5:30 49 25 12 31 20
6:00 40 20 7 27 15
6:30 23 17 7 20 21
7:00 38 23 16 26 19
7:30 22 17 21 15 22
8:00 28 23 21 25 21

Total Jobs 394 322 231 364 250 0 0

Reply | Quote

When you are composing a new post or a reply, there is a box where you can enter the path to a file to attach; you can also click the Browse button. See screenshot.

You can attach files of up to 100 KB in size; if the workbook is larger than that, create a ZIP file from it and attach that.

Warning: previewing a post or reply clears the “Attach a file” box, so you must fill the box just before actually posting.

Reply | Quote

how do I do that?

Reply | Quote

>> His test function isnt working
Perhaps he doesn’t understand what you want?

Could you post a small workbook that demonstrates what you are working with? Replace sensitive data with dummy data, and indicate in the workbook itself or in the post itself what should go where?

Reply | Quote

every time I run the macro it put the same data in a different column still I need something that will prevent this. His test function isnt working is there something wrong with it?

Reply | Quote

1) is there a worksheet in the activeworkbook named “data entry”? It would seem that you do not.

It is “xlToLeft” not “x1ToLeft” It is an excel (xl) constant.

The line starts in the “data entry” sheet in cell IV3 (3rd row the very last column) and then moves to the left (it is in the furthest right column) until the first non-blank column. This is the last column with an entry. It then gets the column number of that column and stores it in the variable called “LastCol”

Steve

Reply | Quote

LastCol = Worksheets(“data entry”).Range(“IV3”).End(xlToLeft).Column

receiving RUN time error “9”
subscript out of range and it’s pointing to the command above… what does X1toleft mean…. my columns go from left to right but I want to compare the left (new) column to the right (old column), also what does IV3 mean

Reply | Quote

The code I posted was meant as replacement for the macro you had. If you like, you can rename Test to Get12.

You should copy both the Function and the Sub into your module. The Sub needs the Function to work.

Reply | Quote

hans – first of all thank you for replying so fast. second of all where do I put your new macro at before my old one or after it. Pleae inform… I’m still a newbe at this thanks p.s. see prior mail for my macro…

Reply | Quote

Couldn’t you simply add conditional formatting which would highlight the new entry if it equals the cell to its left?

Seems simple enough unless I am missing something here.

-yoyo

Reply | Quote

ok but to a newbe how would I do that?

Reply | Quote

Based on your original question, I don’t think conditional formatting will do what you want.

It could highlight the cells in the new column, that are equal to the old column, they would be highlighted if any were the same, you are looking for whether all would be the same. You woul have to manually add the cond formatting after each copy or add it via macro code. It would not prevent the dupes only highlight them.

Hans’ code prevents the dupes, so I think that is a better approach, since you want to prevent them, not deal with after the fact (which is what your original question was).

Steve

Reply | Quote

Based on your original question, I don’t think conditional formatting will do what you want.

It could highlight the cells in the new column, that are equal to the old column, they would be highlighted if any were the same, you are looking for whether all would be the same. You woul have to manually add the cond formatting after each copy or add it via macro code. It would not prevent the dupes only highlight them.

Hans’ code prevents the dupes, so I think that is a better approach, since you want to prevent them, not deal with after the fact (which is what your original question was).

Steve

Reply | Quote

ok but to a newbe how would I do that?

Reply | Quote