3

Given a list of triples I want to get a relationship matrix as follows.

1 A X
1 A Y
1 B X             A   B   C
1 B Z         1  X,Y X,Z
2 A Z   ==>   2   Z   X   Y
2 B X         3   Y   Z
3 A Y
3 A Z
2 C Y

(How) can this be done in Excel/VB/PowerBI or similar ?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Bastl
  • 2,926
  • 5
  • 27
  • 48
  • Very similar: https://stackoverflow.com/questions/32767117/pivot-in-excel-without-aggregation-to-show-text-not-numbers – Bastl Jan 17 '19 at 09:37

2 Answers2

1

Using an array formula, you can generate an array which will hold either the value of the third column or a blank depending on whether the value should be included. You can then join them using TEXTJOIN. TEXTJOIN being new in Office 365; if you don't have it you'll first need to define it as a function this way (taken from MrExcel):

Function TEXTJOIN(Delimiter As String, IgnoreBlanks As Boolean, ParamArray Text() As Variant) As String
  Dim Item As Variant, V As Variant, Arr As Variant
  For Each Item In Text
    If VarType(Item) > 8191 Then
      For Each V In Item
        If Len(V) > 0 Or (Len(V) = 0 And Not IgnoreBlanks) Then TEXTJOIN = TEXTJOIN & Delimiter & V
      Next
    Else
      TEXTJOIN = TEXTJOIN & Delimiter & Item
    End If
  Next
  TEXTJOIN = Mid(TEXTJOIN, Len(Delimiter) + 1)
End Function

Now back to your problem, assuming your data is in A1:C9 and the table you want is at F2:I5 (with line 2 and column F containing the indexes) you'll need to use this formula in G3: {=TEXTJOIN(",";TRUE;IF($A$1:$A$9=$F3;IF($B$1:$B$9=G$2;$C$1:$C$9;"");""))}:

enter image description here

If you're unfamiliar with array formulas, please note that you'll need to use CTRL+SHIFT+ENTER to enter it. You'll then need to copy G3 and paste it to the other cells of your table (excel will be finicky about that if you try to paste to a range including the cell you copied an array formula from, so you might have to do it in several pastes); and the formula will use the proper indexes as $F3 and G$2 are relative.

Joubarc
  • 1,206
  • 10
  • 17
  • looks very good, but I struggle. I enter the formula without curly braces, right? When CTRL-SHIFT-ENTER, it's not recognized as a formula, cursor shows error at the comma-delimiter... – Bastl Jan 17 '19 at 16:37
  • 1
    Sorry, I only tested this in excel 2010 with texjoin as UDF as above. However, I forgot to take into account my locale might be different and uses `;` as parameter separator while your probably requires a comma. Can you test it that way? If that works better I'll edit the answer accordingly. – Joubarc Jan 17 '19 at 17:26
  • Great! that was (my) problem. Always found it strange that the excel formulas and vb-code is localized :-) – Bastl Jan 18 '19 at 07:38
  • Just applied it to my actual problem: super useful and very easy! Thanks! – Bastl Jan 18 '19 at 07:55
0

Maybe you would consider using an add-in

Follow this guide to get this tool

And then you have it out of the box :)

This guide is quite helpful

Daut
  • 2,537
  • 1
  • 19
  • 32
  • but this is only for numerical data isnt it? I need qualitative data (strings, or rather list of strings) in the cells. – Bastl Jan 17 '19 at 09:26
  • @Bastl tbh I haven't tested it, so I would not know. Did you test it? I want to keep this answer, but I will add that it is for numbers only if you have – Daut Jan 17 '19 at 09:30