I've got a a document that entails recommendations for my place of business, specific to site (we own several smaller facilities) with several sheets. The first is a full, all-inclusive list. The columns include site location, type of recommendation (very general), actual recommendation, detailed recommendation, and observations. The detailed and observations may not be filled in for each row, but they all have site, type of rec, and actual rec. If the same rec occurs for multiple sites, I added the recs together and deleted the duplicate on a second page. Now, they want me to compare the master list and the second, consolidated page, and determine which rows have been sent over, and which are still left that haven't been included on the final page. I can't sort exclusively by actual rec because some sites have the same actual rec with several different detailed recs nested underneath it. I've thought of trying an IF function or a VLOOKUP, but I can't quite nail down a good way to compare the two pages. They're about 700 and 500 rows respectively, so manually comparing would be very difficult. Can anyone help with a formula that would compare the two pages and display whether or not they exist in the second page? Thanks for any help you can give.
There are probably other ways to accomplish this, but here's an option. You might be able to use a COUNTIFS function. However, that will only let you search by 3 criteria.
If you're saying every record has Site, Type, and Actual Rec, then you might be able to search with those three criteria.
On your master list, create a new column titled "Copied" (or whatever you want.) In cell F2 I entered the following formula:
=IF(COUNTIFS('Duplicates removed'!A:A, A2, 'Duplicates removed'!B:B, B2, 'Duplicates removed'!C:C, C2)>=1, "Duplicate", "")
The COUNTIFS function matches your criteria by a range. In this case, the first criteria is "Site", or A2 on the master list. It is searching for matches in Sheet "'Duplicates removed'!A:A" which is the corresponding column of sites on the sheet where the duplicates have been removed. B2 matches Type and C2 matches "Rec". >=1 tells COUNTIFS that you want to find entries that are greater than or equal to 1 (you may have multiple entries on your reduced sheet). "Duplicate" returns what the cell will display if a match is found. Change this to your liking. "Copied" "Done" etc.
Hover over the bottom right of Cell F2 until the cursor turns into a black cross. Click and drag down to copy the formula into every row on your sheet.
To get the formatting to show which rows have been copied, you'll use conditional formatting.
Select Cell A2. Then go into conditional formatting, click on "new rule", then select "Use a formula to determin which cells to format". In the "Format Values Box" type
=$F2="Duplicateand replace the cell and phrase with what matches your needs. Then click on "Format" and choose your cell shading.
To apply the formatting to you're whole table, go to "Manage Rules" in conditional formatting, and click on the cell selector next to the "Applies To" field.
Click on the top right of your table and drag it to select all of your information. This should highlight all the rows where the Copied column indicates the info was copied.
Hope this helps. Try changing your criteria around in the formula to best match your data.
*Note - based on what you're doing, a database would be more efficient than excel. A DB would assign a unique ID number to each entry, which would make it much easier to search and manage duplicate entries. I manage a database of about 12,000 (and growing) records. I have a column in the table where I can enter "Duplicate" ID numbers. If an entry closely matches a previous entry, I enter the former ID number into the duplicate field. It makes it easy to find which submissions are recurring issues, but just running a query to match the original idea number against the duplicate field.