Forum Discussion
Help creating a matrix table
Could you please share desired output for the above sample.
Hi Sergei.
Thank you and apologies for my delayed reply. If I am understanding the correct method to format the range / table for Power BI to create a chord diagram, then I believe I need to create 'pairwise' relationships between the issues. If I am correct in this assumption then I think it needs to be a two stage process, firstly identifying all pairings (which I think would look something like this):
Table A: (Possible output from original table)
Then another step would require counting the occurrence of pairs.
I am not certain this is the correct method, but seems to be the manner suggested in other forums. However, how to get from the original table to Table A (where pairs are combined) and then onto a final table counting the occurrences alludes me!
Thanks again,
Sub matrixtable() Dim i As Long, j As Long, k As Long Range("F:H").Clear k = 2 For i = 2 To Range("B" & Rows.Count).End(xlUp).Row j = Application.WorksheetFunction.CountA(Range(Cells(i, 2), Cells(i, 5))) Select Case j Case Is = 1 Cells(k, 7).Value = Cells(i, 2).Value Cells(k, 6).Value = Cells(i, 1).Value k = k + 1 Case Is = 2 Cells(k, 7).Value = Cells(i, 2).Value Cells(k, 8).Value = Cells(i, 3).Value Cells(k, 6).Value = Cells(i, 1).Value k = k + 1 Case Is = 3 Cells(k, 7).Value = Cells(i, 2).Value Cells(k, 8).Value = Cells(i, 3).Value Cells(k + 1, 7).Value = Cells(i, 2).Value Cells(k + 1, 8).Value = Cells(i, 4).Value Cells(k + 2, 7).Value = Cells(i, 3).Value Cells(k + 2, 8).Value = Cells(i, 4).Value Cells(k, 6).Value = Cells(i, 1).Value Cells(k + 1, 6).Value = Cells(i, 1).Value Cells(k + 2, 6).Value = Cells(i, 1).Value k = k + 3 Case Is = 4 Cells(k, 7).Value = Cells(i, 2).Value Cells(k, 8).Value = Cells(i, 3).Value Cells(k + 1, 7).Value = Cells(i, 2).Value Cells(k + 1, 8).Value = Cells(i, 4).Value Cells(k + 2, 7).Value = Cells(i, 2).Value Cells(k + 2, 8).Value = Cells(i, 5).Value Cells(k + 3, 7).Value = Cells(i, 3).Value Cells(k + 3, 8).Value = Cells(i, 4).Value Cells(k + 4, 7).Value = Cells(i, 3).Value Cells(k + 4, 8).Value = Cells(i, 5).Value Cells(k + 5, 7).Value = Cells(i, 4).Value Cells(k + 5, 8).Value = Cells(i, 5).Value Range(Cells(k, 6), Cells(k + 5, 6)).Value = Cells(i, 1).Value k = k + 6 End Select Next i End SubThis code returns the intended output in column F:H in my sheet. For ~1500 rows in cols A-E the code runtime was ~3 seconds in my sheet. If necessary the code can be changed and executed in the main memory which would speed up the runtime significantly. The attached sample file doesn't contain the macro because for some reason i can't attach a macro-enabled Excel file anymore.
=SUM( (MMULT(--($B$2:$E$25=G3), TRANSPOSE(--(COLUMN($A$1:$D$1)>0)))>0 )* (MMULT(--($B$2:$E$25=H3), TRANSPOSE(--(COLUMN($A$1:$D$1)>0)))>0 ) )This formula was contributed by SergeiBaklan in an earlier discussion and i adapted it for your task.
Thanks to you and SergeiBaklan
I did post a reply earlier but it seems to have vanished, so apologies if this now appears twice! The macro you provided works perfectly to create the adjacent pairs, so thank you for that.
The formula to sum the number of occurrences seems to work fine, apart from where there is only one category listed. To provide an example from your sheet: Wednesday occurs as a lone entry in cell B3 and B19. Therefore one would assume that it should have a sum of '2' in cell I8. However, it shows a value of 7. Using your kindly provided example sheet, the same is true when expanding the formula to cell I31 (for the Friday entry). The miscalculation also shows up in my range when using the formula. It must be summing the combination of blank spaces I think?
Thanks again for your help!
