Identify which members coexist within groups

[mr-tom]

Hi,

I've got an interesting problem, which has stumped me for a week now.

I have a dataset that lists customer IDs and the product IDs for the products they currently own.

Simple example:
Tom, iphone
Tom, ipad
Tom, Minecraft Lego
Emma, iphone
Emma, ipad
Emma, guillotine
Emma, Ham sandwich

So the returned results would look like:

Iphone. Ipad. Etc
iphone
ipad
Minecraft lego
Guillotine
Ham sandwich

Where products coexist within a customer, just return a 1, but where they don't, return a zero.

I.e:
Ham sandwich and guillotine coexist within a customer, so their intersection would contain a 1
Iphone and ipad coexist within at least one customer, so their intersection would contain a 1
Ham sandwich and minecraft lego never coexist within the same customer, so their intersection would contain a zero.

I appreciate this is one of those things that may work better within SQL, but my client doesn't allow query analyser for their "normal" staff, (or even Access), so to be able to re-run this in the future, it's going to need to happen in excel.

Any ideas appreciated.

Tom.

(Edit) The actual data set is around 250k records once deduplicated across the combination of group and product

 

[AliGW]

How and where exactly do you want the results to appear? What is the current layout of the data? Try using the ForumTools add-on (link in my sig line) to provide some data in the right format.

 

[mr-tom]

Apologies - I'm on the iPad this weekend, so can't use the add in, but it's a fair comment - I'd not realised the forum had trimmed out the excess spaces I'd put in. Below is a link to a spreadsheet that illustrates what I'm trying to do.

Forgive me if the link doesn't work - I'm not 100% sure that the forum will like me posting a link to an XLS so sorry if that doesn't go well.

https://1drv.ms/x/s!AuOYpBUi2pMwkUHF9LR8380v7r_q

 

[Peter_SSs]

The actual data set is around 250k records ...

In that case I think that you are going to need a macro so I hope your client allows that.

Assuming your data is in columns B:C, starting at row 3 and your possible products are already listed in column E and row 1 as shown below**, then you could try this macro on a copy of your data.

** If your unique products are not already listed as described, the macro could be altered to do that too.

Sub Coexist()
  Dim d As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long, j As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("B3", Range("C" & Rows.Count).End(xlUp)).Value
  b = Range("E1").CurrentRegion.Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) & "|" & a(i, 2) & "|"
  Next i
  For i = 2 To UBound(b, 1)
    For j = 2 To UBound(b, 2)
      If i = j Then
        b(i, j) = "x"
      Else
        b(i, j) = 0
        For Each itm In d.Items
          If InStr(1, itm, "|" & b(i, 1) & "|", 1) > 0 Then
            If InStr(1, itm, "|" & b(1, j) & "|", 1) > 0 Then
              b(i, j) = 1
              Exit For
            End If
          End If
        Next itm
      End If
    Next j
  Next i
  Range("E1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

The above code has produced the results shown below in F2:J6

Excel 2010 32 bit
	B	C	D	E	F	G	H	I	J
1				Results	PRODA	PRODB	PRODC	PRODD	PRODE
2				PRODA	x	1	1	1	0
3	123	PRODA		PRODB	1	x	1	1	1
4	123	PRODA		PRODC	1	1	x	1	1
5	123	PRODB		PRODD	1	1	1	x	1
6	234	PRODC		PRODE	0	1	1	1	x
7	234	PRODB
8	234	PRODD
9	234	PRODE
10	345	PRODA
11	456	PRODA
12	456	PRODB
13	456	PRODC
14	456	PRODB
15	456	PRODD
Coexist

 

[mr-tom]

Thanks Peter,

I'll try this shortly. It looks perfect and yep, I do have the unique product lists exactly in the way you described! :D

I'll let you know how it goes.

 

[Peter_SSs]

OK. Look forward to hearing how it goes.

 

[mr-tom]

Works a treat on a small set of data. I'll leave it runnning on the 250k later. May be one for overnight!

Thanks again. :D