2 SPREADSHEETS, Different rows, duplicate data - lookup help needed

Hi all,

I have 2 spreadsheets.

'File attachment 1' and 'File attachment 2'. Can someone guide me on how to do lookups beteween the two files because I have duplicate data in columns such as prod (column B), description (column C), BC (column D) and BC description (column E)

I am trying to ascertain which lines have different values in column I on file attachment 1 file.

To begin I inserted columns on file attachment 1, to ascertain whether the same Prod exists in File attachment 2 (simple case of '=' symbol and starting at cell B15 in file attachment 2, and doing an IF formula on cell G2 to say whether the values matched between file attachment 1 and file attachment 2.

However when I find they don't match, I don't think I know what to do next.

Can anyone suggest a better way of doing this? my data set is 16,000 rows so i've trimmed this massivvely

the duplicate data is necessary because there could be multiple prods with different data (so they're still technically unique)

Thank you!

No attachments.

Attachments - didn't notice these didn't go through the first time!

Sorry Ali - no idea how that didn't go through, I didn't notice! Uploaded now. thank you!

Can both files be OPEN when the comparisons are being done, or must this work with their being CLOSED?

However when I find they don't match, I don't think I know what to do next.

I don't know what you want to happen next, so unfortunately I can't help until you say what that is.

Hi Ali,

Yes I guess so - I just want a definitive answer ...

which leads me to the next point I`m ultimately trying to have 1 file where I know all the lines and crucially trying to ensure that 'prod' (column B) if they have duplicates in column B i.e 123, 123, the values in the column for BCatgry should have THE SAME VALUES i.e. blue and blue it can't blue and green

My issue is my starting point of the files not being exactly the same hence why I can't start/unsure how to start

It can be done either way, but whichever way you prefer will dictate the solutions offered.

Why not just combine the two and then filter to unique values?

You could do the combining with PowerQuery if the workbooks are closed, or copy data from one workbook into the other and then use the UNIQUE function in 365 to eliminate duplicate rows.

Given the two workbooks attached, can you please mock up manually what you would want the final result to be? Show us the data you'd want left after comparing and filtering. If I can see what you want, I can provide a solution, but I am not 100% sure what that is yet.

So for example, in the attached I have combined the two sets of data and then pulled unique rows - is this anything close to what you want?

Hi Ali,

That could be one of way of starting. Putting it all in one sheet but clearly outlining whether it's from attachment 1 or attachment 2. Let me do this and revert back with a new case if need be for the 2nd part of my investigation.

Thanks for this!

OK. That's easily done, especially with PowerQuery.

You have marked this as solved - but it isn't yet. Pleae don't open a new thread on the same issue!

All good now Ali. No further actions needed thanks again