Any Excel / Sql Gurus Here?

[evictee]

Thanks TPM - that sounds very interesting, but probably a bit too advanced for my current level of understanding

I will try the C# suggestion, and see how I get on with that. Otherwise I'll spend the weekend learning some SQL and give that a try.

If you go to: https://dotnetfiddle.net/ and paste in the following you'll get a working demo:

using System;

using System.Linq;

public class Program

{

public static void Main()

{

var data1 = @"Dave|Blue|Bananas

Dave|Blue|Oranges

Dave|Blue|Apples

Dave|Red|Bananas

Dave|Red|Melons

Dave|Green|Kiwis

Dave|Green|Oranges

Dave|Green|Berries";

var data2 = @"Dave|NotBlue|Bananas

Dave|NotBlue|Oranges

Dave|NotBlue|Apples

Dave|NotRed|Bananas

Dave|NotRed|Melons

Dave|NotGreen|Kiwis

Dave|NotGreen|Oranges

Dave|NotGreen|NotBerries";

var table1 = data1.Split('\n').Select(x => x.Trim()).Select(x => x.Split('|')).Select(x => new Row { Picker = x[0], Basket = x[1], Fruit = x[2] });

var table2 = data2.Split('\n').Select(x => x.Trim()).Select(x => x.Split('|')).Select(x => new Row { Picker = x[0], Basket = x[1], Fruit = x[2] });

var grouped1 = table1.OrderBy(x => x.Fruit).GroupBy(x => new { x.Picker, x.Basket });

var grouped2 = table2.OrderBy(x => x.Fruit).GroupBy(x => new { x.Picker, x.Basket });

var unmatched = grouped1.Where(g => grouped2.Where(x => x.Key.Picker == g.Key.Picker && x.Select(y => y.Fruit).SequenceEqual(g.Select(y => y.Fruit))).Count() != 1);

unmatched.Select(x => x.Key.Picker + ':' + x.Key.Basket).Dump(); // result is Dave:Green as does not match on NotBerries

}

class Row

{

public string Picker { get; set; }

public string Basket { get; set; }

public string Fruit { get; set; }

}

}

Better still, to work on it locally I highly recommend LINQPad for playing arround with C# and databases. If you installl and run that, change the Language dropdown from C# Expression to C# Program then paste in the bit inside Program { ... } above.

[evictee]

I've sent you a private message about this by the way FD, just in case you don't notice it. I only highlight it here because I've never used this function myself before and I've just discoved a few messages sent to me six years ago that I never knew existed.

[Fully Detached]

I've sent you a private message about this by the way FD, just in case you don't notice it. I only highlight it here because I've never used this function myself before and I've just discoved a few messages sent to me six years ago that I never knew existed.

Blimey, I'm glad you told me that - I used to get email notification of PMs, now it's just a pathetic little red icon at the top of the screen...

Thanks a million for all your help - as per my pm, I think we have a solution now, but I did try your code and it worked perfectly. So perhaps it can stay here as a little bit of help to any other wannabe mango managers ;-)

Thanks again - very much appreciated!