Identifying monthly changes in product information & grouping the results

[As I was reading the names of the threads on this forum, I’m a tad embarrassed by how low-tech my question is in comparison, but here goes…]

I work with a mammoth product database where the information, dates, etc. for products can be changed on a monthly basis. What I would like to do is create a spreadsheet that accurately identifies any changes to the information and dates of each product, and which can then group the changes together to minimize the need for humans to check for changes product-by-product (the database contains thousands of unique products).

Presently I’m able to get all of the information for both the current month and prior month onto one spreadsheet. I added the very simple True-False formula to check certain cells in each product row against the same cells for that product row in the prior month.

Where I’m running into a wall, though, is finding a way of easily identifying all of the changes. Ideally I would like to sort all of the rows so that the Falses are grouped together. But, as you’ve probably all realized before even reading this far, this doesn’t work because of course it changes the cells that the True-False formulas measures. I’ve tried adding $ before each cell in the True-False formulas, but same result as without. Obviously a search for False will get a hit on every single formula.

So, if anyone has any suggestions on how to resolve this (or, additionally, if anyone has any suggestions on how to improve on the parts of this that already work), I would greatly appreciate it.

It would help to see a dummy workbook. Do the numbers of products change each month, or should they all pretty much match from month to month? Is there a column that has a unique value for each product that remains the same no matter what? My initial thought is, if the answer to the first question is yes, sorting both tables by that, and then using a simple conditional formatting formula to highlight cells that have changed. If no, vlookups are an option, but if the sheet is truly massive, it might slow things down a bit. How big are we talking?

Originally Posted by darkyam

It would help to see a dummy workbook. - You've pretty ID'd what the workbook would look like with your questions below, though I can try and attach a screenshot if that would be valuable.

Do the numbers of products change each month, or should they all pretty much match from month to month? - They pretty much match from month to month
Is there a column that has a unique value for each product that remains the same no matter what? - Yes, the unique product identifier

My initial thought is, if the answer to the first question is yes, sorting both tables by that, and then using a simple conditional formatting formula to highlight cells that have changed. If no, vlookups are an option, but if the sheet is truly massive, it might slow things down a bit. How big are we talking?

With a conditional formatting formula, wouldn't that still require scrolling through the worksheets to spot the changes? The amount of info is quite considerable--there's so much data that I need to break it up into several spreadsheets, otherwise I would run into the 65K row limit in no time. My hope with a sorting function is that it would both save time and cut down on the potential for human error.

OK, that is huge. To get a list spit out automatically, you're probably looking at VBA, which, unfortunately, I've never learned. I'm sure one of the other gurus here can help you with that, though. Sorry I can't be of more help.

Originally Posted by darkyam

OK, that is huge. To get a list spit out automatically, you're probably looking at VBA, which, unfortunately, I've never learned. I'm sure one of the other gurus here can help you with that, though. Sorry I can't be of more help.

No worries, and thank you for your feedback.

Yes, hopefully a VBA (Visual Basic?) pro can advise.

Re-bumping in hopes that someone will be able to advise....

No additional posts, unfortunately. Oh well.

Darkyam, if you have a moment, would you mind suggesting a conditional formatting formula and/or a starting formula for vlookups? I'll still have to check the for changes manually, but your suggestions would still be a dramatic improvement from what I'm presently working with.

If you have the TRUE/FALSE formula working, you can still use that, combined with a filter on that column, set to (assumedly) False to denote ones that changed. Alternatively, you could copy the formula from row 2 of that column (assuming row 1 has headers) and put it in the next column over. Then, just copy and paste special as values for the first column. Your Trues and Falses will remain the same, allowing you to sort if you wanted. For the next month, simply copy the formula in the next column over back and drag down over the length of the column (starting with cell formula is in, hit Ctrl+down, then Ctrl+D).

The conditional format approach would be similar. If the TRUE/FALSE is in E, the conditional format formula could be =$E2=FALSE, dragged down as necessary.

See the attached workbook for a small example. The table with the TRUE/FALSE statements is on Sheet2. hth

Filter... I NEVER would have thought of that.

Now that I've put myself through a quick crash course in just what "Paste Special" is (was not even aware of it before your post), I'm able to follow your directions on how to use it (and making progress on Conditional Formatting). Just brilliant! Both the Paste Special and Filter will be spectacular help on this spreadsheet and others. Two thumbs up! Thank you!!

A bit afield from my original post, but regarding Filter, when I use Auto Filter on spreadsheets that are crammed with rows and columns of data, the Auto Filter will only search a certain number of rows down. Is this an unavoidable aspect of Auto Filter (and presumably resolved by breaking the info up into numerous worksheets), or no?

Don't know much about autofilter, but I think you can select custom and then specify the number of rows it looks at. And be sure there aren't any blank rows in your data.

Also, the Paste Special and Filter suggestions were more OR than AND ideas. In other words, if you're using the filter, but not sorting, it makes no sense copy and paste special. And, of course, if you're sorting, the filter loses much of its usefulness.

Sure, know what you mean by OR, not AND. But I'm thinking both would be handy for other uses in other spreadsheets as well, hence my excitement.

Thanks again!! All very helpful!!