Finding out whether data from a list is duplicated in multi-sheet excel document

Hello,

I'm quite new to excel and I've come up against a challenge.
I'm working on a project where I have an Excel spreadsheet with 12 pages of both arranged and un-arranged IP addresses in different columns and around other data.

Some are just subnet mask ranges i.e from a subnet mask start of 10.3.65.1 in column A to a subnet mask end of 10.3.65.255 in column F; So the in between ranges are not individually written out.
Some just seem to be random lists of IP addresses in no particular order.

What I've been asked to do, is take one list of IP addresses and compare them against all of the sheets to find any duplicates.
The ones that are duplicated would need to be displayed in a separate table in a separate sheet with how many times they are duplicated within the spreadsheet across all sheets and where it is that they are duplicated.

Is this possible to do by writing a formula?
I could possibly write a Macro if I knew how to but I can't import any macros into the spreadsheet due to security restrictions although I could potentially copy and paste code.

If any one has any questions or ideas please let me know. Apologies if I've not explained myself very well.

Thanks Mumps1. I can't really attach the file as it is all sensitive information and I would be breaking the law.
I could try to explain things a little better.

First sheet, column A, row 2 - Subnet start, column A, row 5- first IP address. The IP Addresses go down to row 296.
This is repeated in column F with Subnet ends.


Second Sheet - same as first sheet but IP addresses go down to row 371.

Third sheet - column B starts at row 5 and goes down to row 186 - it is surrounded by text.
There is then a second list of IP addresses in sheet 3 on row I.

4th Sheet - Column A1 to A48 text and IP addresses I.e.(Changed for data protection) "net subnet 10.145.215.8 255.237.255.1 include"
Column D1 to D48 - List of IP addresses.

5th Sheet - same as first sheet but ends at row 337.

6th Sheet - same as first sheet but ends at row 355.

7th Sheet - same as first sheet but ends at row 307.

8th Sheet - 3 lists of IP addresses surrounded by other data in columns E, H and AD from rows 1 to 6933.

9th Sheet - same as 8th sheet.

10th Sheet - same as 8th sheet.

11th sheet - same as 8th sheet.

12th sheet - same as 8th sheet.

Hope this makes any sense at all!