How to check whether I have entered all the necessary numbers in excel?
0
I have entered numbers 1-1074 in an excel sheet and I want to check whether I have missed any number in that range. How would I be able to check that?
microsoft-excel worksheet-function
asked Jan 28 at 15:21
KanitaKanita
1
add a comment |
0
I have entered numbers 1-1074 in an excel sheet and I want to check whether I have missed any number in that range. How would I be able to check that?
microsoft-excel worksheet-function
asked Jan 28 at 15:21
KanitaKanita
1
Do you want to know if any are missing, or which numbers are missing. Finding if any are missing is easy. Returning a list of missing numbers is a little more difficult.
– Scott Craner
Jan 28 at 15:23
I assume this is sequential. Did you just do A2=A1+1, then drag it down?
– spikey_richie
Jan 28 at 15:24
Or type1inA1andCTRL-drag down
– cybernetic.nomad
Jan 28 at 16:02
add a comment |
0
0
0
I have entered numbers 1-1074 in an excel sheet and I want to check whether I have missed any number in that range. How would I be able to check that?
microsoft-excel worksheet-function
asked Jan 28 at 15:21
KanitaKanita
1
I have entered numbers 1-1074 in an excel sheet and I want to check whether I have missed any number in that range. How would I be able to check that?
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Jan 28 at 15:21
KanitaKanita
1
asked Jan 28 at 15:21
KanitaKanita
1
asked Jan 28 at 15:21
KanitaKanita
1
asked Jan 28 at 15:21
KanitaKanita
1
asked Jan 28 at 15:21
KanitaKanita
1
1
Do you want to know if any are missing, or which numbers are missing. Finding if any are missing is easy. Returning a list of missing numbers is a little more difficult.
– Scott Craner
Jan 28 at 15:23
I assume this is sequential. Did you just do A2=A1+1, then drag it down?
– spikey_richie
Jan 28 at 15:24
Or type1inA1andCTRL-drag down
– cybernetic.nomad
Jan 28 at 16:02
add a comment |
Do you want to know if any are missing, or which numbers are missing. Finding if any are missing is easy. Returning a list of missing numbers is a little more difficult.
– Scott Craner
Jan 28 at 15:23
I assume this is sequential. Did you just do A2=A1+1, then drag it down?
– spikey_richie
Jan 28 at 15:24
Or type1inA1andCTRL-drag down
– cybernetic.nomad
Jan 28 at 16:02
Do you want to know if any are missing, or which numbers are missing. Finding if any are missing is easy. Returning a list of missing numbers is a little more difficult.
– Scott Craner
Jan 28 at 15:23
Do you want to know if any are missing, or which numbers are missing. Finding if any are missing is easy. Returning a list of missing numbers is a little more difficult.
– Scott Craner
Jan 28 at 15:23
I assume this is sequential. Did you just do A2=A1+1, then drag it down?
– spikey_richie
Jan 28 at 15:24
I assume this is sequential. Did you just do A2=A1+1, then drag it down?
– spikey_richie
Jan 28 at 15:24
Or type
1 in A1 and CTRL-drag down– cybernetic.nomad
Jan 28 at 16:02
Or type
1 in A1 and CTRL-drag down– cybernetic.nomad
Jan 28 at 16:02
add a comment |
2 Answers
2
active
oldest
votes
0
This macro should work even if the entries are not sorted:
Sub DataCheck()
Dim A As Range, i As Long, msg As String, r As Range
msg = ""
Set A = Range("A:A")
For i = 1 To 1074
Set r = A.Find(what:=CStr(i), After:=A(1), lookat:=xlWhole)
If r Is Nothing Then
msg = msg & vbCrLf & i
End If
Next i
If msg = "" Then
MsgBox "nothing missing"
Else
MsgBox "These are missing:" & msg
End If
End Sub
answered Jan 28 at 16:34
Gary's StudentGary's Student
13.7k31730
add a comment |
0
If you just need a Yes/No answer, then this formula should return TRUE/FALSE for you:
=SUMPRODUCT(--(COUNTIF(myRange,ROW(INDIRECT("1:1074")))>0))=1074
answered Jan 30 at 2:37
Ron RosenfeldRon Rosenfeld
2,0242611
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1399302%2fhow-to-check-whether-i-have-entered-all-the-necessary-numbers-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
This macro should work even if the entries are not sorted:
Sub DataCheck()
Dim A As Range, i As Long, msg As String, r As Range
msg = ""
Set A = Range("A:A")
For i = 1 To 1074
Set r = A.Find(what:=CStr(i), After:=A(1), lookat:=xlWhole)
If r Is Nothing Then
msg = msg & vbCrLf & i
End If
Next i
If msg = "" Then
MsgBox "nothing missing"
Else
MsgBox "These are missing:" & msg
End If
End Sub
answered Jan 28 at 16:34
Gary's StudentGary's Student
13.7k31730
add a comment |
0
This macro should work even if the entries are not sorted:
Sub DataCheck()
Dim A As Range, i As Long, msg As String, r As Range
msg = ""
Set A = Range("A:A")
For i = 1 To 1074
Set r = A.Find(what:=CStr(i), After:=A(1), lookat:=xlWhole)
If r Is Nothing Then
msg = msg & vbCrLf & i
End If
Next i
If msg = "" Then
MsgBox "nothing missing"
Else
MsgBox "These are missing:" & msg
End If
End Sub
answered Jan 28 at 16:34
Gary's StudentGary's Student
13.7k31730
add a comment |
0
0
0
This macro should work even if the entries are not sorted:
Sub DataCheck()
Dim A As Range, i As Long, msg As String, r As Range
msg = ""
Set A = Range("A:A")
For i = 1 To 1074
Set r = A.Find(what:=CStr(i), After:=A(1), lookat:=xlWhole)
If r Is Nothing Then
msg = msg & vbCrLf & i
End If
Next i
If msg = "" Then
MsgBox "nothing missing"
Else
MsgBox "These are missing:" & msg
End If
End Sub
answered Jan 28 at 16:34
Gary's StudentGary's Student
13.7k31730
This macro should work even if the entries are not sorted:
Sub DataCheck()
Dim A As Range, i As Long, msg As String, r As Range
msg = ""
Set A = Range("A:A")
For i = 1 To 1074
Set r = A.Find(what:=CStr(i), After:=A(1), lookat:=xlWhole)
If r Is Nothing Then
msg = msg & vbCrLf & i
End If
Next i
If msg = "" Then
MsgBox "nothing missing"
Else
MsgBox "These are missing:" & msg
End If
End Sub
answered Jan 28 at 16:34
Gary's StudentGary's Student
13.7k31730
answered Jan 28 at 16:34
Gary's StudentGary's Student
13.7k31730
answered Jan 28 at 16:34
Gary's StudentGary's Student
13.7k31730
answered Jan 28 at 16:34
Gary's StudentGary's Student
13.7k31730
13.7k31730
add a comment |
add a comment |
0
If you just need a Yes/No answer, then this formula should return TRUE/FALSE for you:
=SUMPRODUCT(--(COUNTIF(myRange,ROW(INDIRECT("1:1074")))>0))=1074
answered Jan 30 at 2:37
Ron RosenfeldRon Rosenfeld
2,0242611
add a comment |
0
If you just need a Yes/No answer, then this formula should return TRUE/FALSE for you:
=SUMPRODUCT(--(COUNTIF(myRange,ROW(INDIRECT("1:1074")))>0))=1074
answered Jan 30 at 2:37
Ron RosenfeldRon Rosenfeld
2,0242611
add a comment |
0
0
0
If you just need a Yes/No answer, then this formula should return TRUE/FALSE for you:
=SUMPRODUCT(--(COUNTIF(myRange,ROW(INDIRECT("1:1074")))>0))=1074
answered Jan 30 at 2:37
Ron RosenfeldRon Rosenfeld
2,0242611
If you just need a Yes/No answer, then this formula should return TRUE/FALSE for you:
=SUMPRODUCT(--(COUNTIF(myRange,ROW(INDIRECT("1:1074")))>0))=1074
answered Jan 30 at 2:37
Ron RosenfeldRon Rosenfeld
2,0242611
answered Jan 30 at 2:37
Ron RosenfeldRon Rosenfeld
2,0242611
answered Jan 30 at 2:37
Ron RosenfeldRon Rosenfeld
2,0242611
answered Jan 30 at 2:37
Ron RosenfeldRon Rosenfeld
2,0242611
2,0242611
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1399302%2fhow-to-check-whether-i-have-entered-all-the-necessary-numbers-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Do you want to know if any are missing, or which numbers are missing. Finding if any are missing is easy. Returning a list of missing numbers is a little more difficult.
– Scott Craner
Jan 28 at 15:23
I assume this is sequential. Did you just do A2=A1+1, then drag it down?
– spikey_richie
Jan 28 at 15:24
Or type
1inA1andCTRL-drag down– cybernetic.nomad
Jan 28 at 16:02