Complex Sheets Comparison

So, I've been working on this brain tickler for over nine hours, and I guess I just lack the experience to plug all the pieces together myself.

If anyone feels like lending a helping hand, it would be infinitely appreciated.

I'm trying to accomplish several different things, across multiple sheets - all at the same time and in a single, repeatable formula.

Each sheet has several thousand entries of personnel data (Name, Surname, Purchase ID#s, mod dates, etc), but I'm choosing to sort by email address for ease, and then just importing data from across the rest of the row correspondingly.

These are the goals:

1) Import all non-repeat entries from Sheet2 to Sheet3. I would just copy/paste and go by hand, but I need this particular article to be dynamic and there's already a LOT of data to sift.
2) If ((the email in Sheet1!E1 is NOT present in Sheet2!C1:C-Any) && (Email is not already present in Sheet 3)), place the item in Sheet3:C1.
3) If #2 is FALSE, proceed to checking Sheet1!E2 WITHOUT advancing from Sheet3!C1.
4) Repeat until #2 is TRUE or no valid values remain in Sheet1.

Preferably, I would also like to make the data sortable, but the sorting functions gets hinky really fast and I'm not 100% sure it's entirely feasible.

What I've got right now looks like:

=IF( AND( MATCH(Sheet1!E1, Sheet2!C1:C2001), MATCH(Sheet2!E1, Sheet2!E1:E2001)), Sheet1:A1)

As you can see, this fails on just about every front:

a) It completely ignores #1
b) It fails to cover a theoretically infinitely deep column Sheet1!E / Sheet2!C.
c) It simply produces a "FALSE" value in the event of #3/4, rather than advancing its search.

Like I said, I'm a bit out of my depth on this one.

I'll keep plugging away, but if anyone could help me solve any of the puzzle I would be so very grateful.

Thanks.

Hi,

Goal 1:
On Sheet3, try this array formula in cell A2 (I assume you have headings in row 1), then drag it across and down as far as is necessary (also adjust the ranges in the formula to match the ranges in your data):

Formula:

Please Login or Register  to view this content.

To enter an array formula, you need to use Ctrl + Shift + Enter, not just Enter. This will give you a replica of Sheet2 but with all duplicate rows removed (I think this is what you are asking for??)

As for the remainder of your goals, I believe they can all be tied into 1 single formula, however I'm not sure it would be necessary if I correctly understand what it is that you are asking for.

My understanding is that you want to filter out all unique rows on Sheet2 onto Sheet3? Is this correct? Please help me understand by uploading a sample workbook, including a snapshot of what you want the final output of the formula to look like.

Thanks

ajryan? You are awesome, and you should feel awesome.

You're close on your understanding of the goal, except I'm ALSO trying to move all rows from (Sheet1 NOT already represented on Sheet2) onto Sheet3.

I hope this makes sense. . . The whole thing has ME confused, and I'm the one who's been wrestling with it.

Here's those uploads you asked for. In the real deal, I'm handling 2000+ entries on both Sheets 1 & 2.


Sheet1:
Sheet1.png

Sheet2:
Sheet2.png

Sheet3 (Result):
Sheet3.png

Hahaha thanks!

OK no worries,

Try this modified formula in cell A2 on Sheet3 instead:

Formula:

Please Login or Register  to view this content.

This will extract all values that are unique in column A across BOTH worksheets "Sheet1" AND "Sheet2". Again, just change the A1:A20 ranges to match their respective ranges on their respective worksheets, and remember to enter this as an array formula.

Hope this is what you are looking for this time

You are awesome.

It turns out that what I was trying to do was not even close to what the client actually WANTED - but I was able to get the job done thanks to this.

In short: Imma go eat some chocolate cake, and you should too. ^-^

Hahaha I LOVE chocolate cake! Well done, that's great news.

Please don't forget to mark this thread as solved and please click on the * next to my answer(s) to say thanks