Matching data between two data "dumps"

[razza]

Hello,
Looking for some help with an issue. It might be an impossibility but I know there are some clever people on Excel so would appreciate any help anyone can offer.

Each month I "dump" data from an accountancy program into Excel. I need to compare this data to the previous months data and highlight the new data. The data is unsorted, and it is not possible to sort the data to do a "line by line" comparison. So I need Excel to search the data to match the data up.

Please see example attached of what I would like the solution to output. The yellow highlights the new data in April in comparison to March. I would like Excel to check all 4 pieces of data (Name, Date, Transaction Number & Value) to confirm a no match exists before highlighting the new data. Also, I don't want to have to manually sort the data as that is a slow process. Hopefully Excel can do the work for me


Thanks in advance for any help offered. Please let me know any questions if I haven't made anything clear.


Kind regards,
Ryan

[Gregb11]

While you have the data selected (G5:J12), in Conditional Formatting, you can enter this formula:

=NOT(SUMPRODUCT((G5=$A$5:$A$8)*(H5=$B$5:$B$8)*(I5=$C$5:$C$8)*(J5=$D$5:$D$8)))

[razza]

Wow, that's awesome Gregb11. Thanks very much.

Your solution worked when applied to the example spreadsheet I attached. However when I applied it to the live data it does not work. I have changed the formula accordingly to suit the live data. I assume it is because in the live data there are other columns of data present, though this data is not used for the purpose of comparing the data of one data dump to the next. How do I amend the formula to include for these "redundant" columns?

Thanks again.

Regards,
Ryan

[Gregb11]

Yes, make sure that the cells in the formula match the cells you want to match. I can't really say why it's not working if I don't see an example. If you can't figure it out, maybe you can post just a few rows of data and make some minor adjustments to the data so you can post it publicly.

[razza]

Thanks for your reply Gregb11. I have attached a sample of the live data. Columns A to N have a sample of the previous data, and columns P to AC have the new data. I have listed the formula I was unsucessfully using for the conditional formatting at the bottom of the spreadsheet.

Would really appreciate your feedback again!

Thanks & regards,
Ryan

[Gregb11]

It looks like you have it a little backwards. If the new data starts in column P, and let's say you were just looking to see if P, Q, and R were different or the same. You would put this formula in the Conditional Formatting:
=NOT(SUMPRODUCT(($P1=$A$1:$A$15)*($Q1=$B$1:$B$15)*($R1=$C$1:$C$15)))

If you want to test the whole line, then just continue the formula for each column. You'll want to have the same array (P1:R30 in this case) when you add the Conditional Format. I have it attached for columns P-R.

[razza]

Yes thats all working. Thanks again Gregb11 for your help, really appreciated!!