0

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.

2
  • 1
    Without concrete examples, the only way to answer is for someone to invent a spreadsheet, which won't match yours, and solve that different problem, which may not have the conditions and constraints you face. A good answer would be hard within the intended scope. jrichall invested a lot of time trying to develop an answer for you. If that doesn't answer your question, it illustrates the point. If it does, consider accepting it (or a better one if one is posted), by clicking the checkmark next to it. That will indicate that the question is solved, and award a little rep for the effort.
    – fixer1234
    Jun 14, 2016 at 3:09
  • Was it something I said? Haha.
    – jrichall
    Jun 15, 2016 at 18:06

1 Answer 1

0

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.

Here's a screenshot of something I threw together: enter image description here

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. enter image description here

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.

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.