Conditional Formatting or Formulas in a new column to see modifications

Hi,

I have a question/dilemma that I need a little guidance on (no Macros or VBA please). I’m trying to figure out what the best way to do this. Let me explain…

I work on a large spreadsheet on which I need to make modifications (see sample attached spreadsheet) and then upload the modified file to an online database. There is currently some Conditional Formatting in this sheet and I’m trying to figure out if it’s better to use more Conditional Formatting or formulas to see modifications from sheet to sheet.

Only the following Columns will ever have “manual” edits:
- Column B (Listing)
- Column C (Price 1)
- Column D (Price 2)
- Column F (Model)

I want to be able to “pick out” the modified items quickly (via a Custom Sort), so I can upload only that portion of the overall list. In other words, “Sort by” a certain parameter where the modified Items would come to the top of the list, so instead of me needing to upload the entire list of thousands of Items, I can do a “partial update” with only the modified Items.

I’ve made some manual modifications on the “Current Day” tab (different from “Previous Day” tab) to show what are some of the changes that would be made.

Additional Info:
- After I make modifications on the “Current Day” tab and make the necessary upload, that tab becomes “Previous Day”, in which I “copy and paste as text” to “lock in” all the values to use as a “snapshot in time”. I do this for every subsequent tab, so if there is anything that doesn’t go right, I can see exactly when any mistakes were made. Thus, eventually, I will have the following tabs: “Previous Day”, “11-20-15”, “11-19-15”, “11-18,-15”, etc.
- However, the only tabs that are “interacting” at any one time are the “Current Day”, “Data 11-30-15”, “Inv 11-30-15” and “Previous Day”. All the other tabs are just for future data checking.
- I keep all of my previous tabs in the same spreadsheet for about a month or so, when I archive that month’s uploads into a separate “Archive file”.
- I keep this file open in Excel (in the background) all the time, so it can’t take up too much background memory for calculations.

Please let me know any of your thoughts. Thank you very much in advance.

Yury

If you want to be able to sort the data on a particular field, you will have to use a formula to detect a difference and flag if there has been one - conditional formatting will alert you visually that there has been some change, but you can't do much more than that with it. However, you don't have to sort the data - you could use another sheet which brings only the data from "Current" which is flagged as having changed, and then use that to upload to your database.

Hope this helps.

Pete

Hi Pete,

Thank you for your response. Let me make sure that I understand what you're saying...

There is a way that I can see any modifications and the best way to pull out the modifications is to bring them to another tab within the same spreadsheet. Is that correct?

If it is, this is where I'm stuck. When you say, "you could use another sheet which brings only the data from "Current" which is flagged as having changed" -- this is what I don't know how to do. Can you please help me with how to get this done?

Thank you again,

Yury

Hi Yury

VBA would make this incredibly simple, is there any particular reason for not wanting to use it?

If not VBA, you'd have to add an extra column to concatenate the data from the columns that may change with the formula of:

=CONCATENATE(B2,C2,D2,E2)

which you would then need to copy when you do your values copy onto the 'previous day' sheet.

Then a further column to check if it exists on the previous day, using either a VLOOKUP or MATCH.

=IF(ISERROR(MATCH(R2,'Previous Day'!R:R,0)),"X","")

Assuming you put your concatenate formula in column R. You could then use this second new column to filter for 'X' and it would highlight any changes.

Hi brokenbiscuits,

Thank you very much for your solution...very elegant! I wouldn't have thought to use the Concatenate formula along with a Match formula to get this done. I just tried it in my regular process and it worked beautifully! Thank you for your help.

BTW, the reason why I don't want to use VBA is that I don't know it well enough (actually almost at all) to troubleshoot it if there was ever an issue. This is a mission-critical part of our business process and I have to be able to figure any issues out quickly (since I don't know VBA, I couldn't do that). Also, being that I'm the business owner, I don't have a lot of time to troubleshoot something that I didn't create from scratch. Maybe one of these days, I'll have time to learn VBA, but I'm typically very short on time.

Thank you again and I know that I can use this process in other places to "check" for modifications.

Yury