How can I search for entries that are repeated in the workbook?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have approximately 2500 properties (address number, street name and tax
bill number) and 25 sheets (each representing a different property
developer). I need to determine if the same property has been allocated to
more than one developer, that is whether an entry is repeated on more than
one sheet or even the same sheet.

I would also like to calculate exactly how many entries are in the entire
workbook.

Thanks,
Ivan
 
Is there any single, unique identifying piece of information that would be
found in all sheets such as the tax bill number? Or would even those
possibly be different (or even missing) for the same piece of property on
either the same sheet or on another sheet?
 
Yes, the tax bill number should serve that purpose, however it is possible
due to date entry mistakes that the tax bill number may be off by a digit as
I have discovered once or twice.
 
Ivan,
Download this file:
http://www.jlathamsite.com/uploads/MultiSheetCrossReferencing_Empty.xls
(Excel 97-2003 format, 34 KB, does have Macros in it)

Open that workbook, open a COPY (don't want to mess things up just in case
this falls apart) of your workbook with the 25 or so sheets in it.

Copy the one sheet that's in the workbook I provided into the copy of your
property records workbook. Close the one-sheet workbook. Everything (code
and all) is contained within the one worksheet.

Set up the worksheet by entering the needed information into cells F1 and G1
- it needs to know the column identifier (as A or BA) that the Tax Bill
Numbers are in on all the other sheets. That goes into F1. In G1 put the
number of the first row on those sheets that the data starts in; i.e. first
row with a Tax Bill #. Then just double-click on cell A1 and it'll do its
thing.

Only problem I see is if the total number of individual Tax Bill Numbers for
all sheets is greater than 65535 (assuming you're using Excel 2000, XP or
2003). That would cause a failure that I haven't trapped for.

What it will do:
It will put all unique Tax Bill Numbers into column A of the MasterList
sheet, and on each row, when it finds a sheet with a match to that number
(and I actually assume it's a mix of numbers and letters) it will put the
name of the sheet(s) that it found the Tax Bill Number on in the columns to
the left of the number itself.

So you can quickly see where a number is only referenced once, or where it
is referenced on several sheets, and by just going to the end of the list,
you'll know how many unique Tax Bill Numbers you have: 25*2500 = 62,500 so
I'm hoping we have room: max rows in Excel 2003 is 65536 and we're using 1 of
those already.

When you do the double-click, nothing may appear to happen for a while.
I've turned off screen updating to improve processing speed, so the worksheet
will continue to look empty until it is completely finished.

Also, examination of the Tax Bill Numbers in column A may show you the ones
that either have a missing digit/character, or have too many. You could go
to the indicated sheet and repair those and then run the whole thing again.
If you run it a second (or 3rd or 4th) time, it will erase all of the
referenced sheet entries, but will leave the Tax Bill Numbers in place. So
the 2nd time thru you may get a few more rows, and those numbers you repaired
will probably not have any sheet name reference next to them. If you want,
you can select all of the data from row 2 all the way to the end, delete it
and start over fresh at any time.
 
My aixelsid is showing again...
"...it will put the name of the sheet(s) that it found the Tax Bill Number
 
Back
Top