finding missing numbers in multiple excel sheets

[kswapnadevi]

I have numbers from 1 to 65,000 in multiple sheets and the same number was not repeated in multiple sheets. The numbers are not in order and some of the numbers are missed in each sheet. If a number missed in one sheet, it will not missed in other sheet. I was given example here.
sheet 1 sheet 2 sheet 3 sheet 4(output)
8 6 16 4
1 10 2 5
5 3 9 7
15 19 14 13
17 18 20
11 12


In the above example, sheet 1, sheet2, sheet 3 consists of missing numbers and sheet 4 is the output consists of missing numbers of all these three sheets. I have to implement this 1 to 65,000 numbers in multiple sheets and the missing numbers of all these sheets are to be displayed in other sheet.

I am a non computer person and unable to do it with existing functions and macros and unable to do with VBA and macros. Please do the needfultable.jpgtable.jpgtable.jpg

[Arkadi]

I guess I don't understand...

Sheet2, row 3 has #3, so why is it in Sheet4? Sheet 1 row 3 has #5, so that too should not be in Sheet4?

[kswapnadevi]

You are correct sir. The output sheet 4 has mistake sir. sorry. It should have the numbers of 7, 12, 13, 18, 20 which are missed in first three sheets.

[Arkadi]

Please provide a better explanation of what you need and please attach a workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Arkadi]

As per my previous request, please provide a workbook with the same structure as the book you plan to use, so that we can know how to write the code. Thanks.

How many sheets will the real workbook have? just 1-3 and an output sheet? Are the columns/rows always the same or will it vary? We need more detail please.

[kswapnadevi]

Missing numbers in a single column which are not in sorted order sir is enough sir.
Input Output
3 1
5 2
9 4
10 6
17 7
15 8
11
12
13
14
16table.jpg

[Arkadi]

kswapnadevi,

Doing across many sheets is not a problem... I just wanted more detail.
But ok... lets focus on one column for now then... Here is my question:

In OUTPUT (in the picture), the missing numbers are the missing ones between 1 and 13. So is that because in INPUT, the highest number is 13?

[kswapnadevi]

yes sir. As per that list it is 13 sir.

[jindon]

Sheet1-3 has data in col.A.

Sub test()
    Dim i As Long, e, x
    x = [transpose(row(1:65000))]
    For i = 1 To 3
        For Each e In Sheets(i).Range("a1", Sheets(i).Range("a" & Rows.Count).End(xlUp)).Value
            If (e > 0) * (e <= 65000) Then x(CLng(e)) = False
        Next
    Next
    x = Filter(x, False, 0)
    Sheets("sheet4").Cells(1).Resize(UBound(x) + 1).Value = Application.Transpose(x)
End Sub

[kswapnadevi]

Sir, I tried the above code on original data which consists of 21 sheets and value upto 70000. It is giving error sir. I also attached the original data sir.

Sub test()
Dim i As Long, e, x
x = [transpose(row(1:70000))]
For i = 1 To 21
For Each e In Sheets(i).Range("a1", Sheets(i).Range("a" & Rows.Count).End(xlUp)).Value
If (e > 0) * (e <= 70000) Then x(CLng(e)) = False
Next
Next
x = Filter(x, False, 0)
Sheets("sheet22").Cells(1).Resize(UBound(x) + 1).Value = Application.Transpose(x)
End Sub

[jindon]

1)
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

2)

I have numbers from 1 to 65,000 in multiple sheets

value upto 70000

Makes big difference in cod

Sub test()
    Dim i As Long, e, x
    ReDim x(1 To 70000)
    For i = 1 To 70000
        x(i) = i
    Next
    For i = 1 To 21
        For Each e In Sheets(i).Range("a1", Sheets(i).Range("a" & Rows.Count).End(xlUp)).Value
            If (e > 0) * (e <= 70000) Then x(CLng(e)) = False
        Next
    Next
    x = Filter(x, False, 0)
    Sheets("sheet22").Cells(1).Resize(UBound(x) + 1).Value = Application.Transpose(x)
End Sub