Gegevens van een matrix naar een tabel

[Rozanne]

Hi,

Ik ben aan het puzzelen welke formule ik moet gebruiken voor het volgende.

Ik heb een matrix waarbij per ingrediënt gevaren zijn gekoppeld, voor het gemak heb ik dit in het voorbeeld cijfers gegeven. Er staat een "X" bij het gevaar die hoort bij het ingrediënt.

Nu wil ik in de tabel een overzicht krijgen van alle gevaren (cijfers) die bij een ingrediënt horen.

Ik heb een voorbeeld in de tabel gezet.

De daadwerkelijke matrix is enorm en handmatig kost dit erg veel werk. Dus wie kan mij helpen dit efficiënt op te lossen?

 

[edmoor]

Zoiets?

 

[AlexCEL]

Met Power Query een fluitje van een cent (tabel inladen > draaitabel opheffen voor kolommen).

 

[Rozanne]

Met Power Query een fluitje van een cent (tabel inladen > draaitabel opheffen voor kolommen).

Dit is inderdaad wat ik bedoel. Maar nu de vraag, zou je kunnen uitleggen wat je precies doet om dit overzicht te krijgen? Power Query zegt mij niet zoveel.

 

[Enigmasmurf]

Met een ouderwetse macro is het ook niet uitzonderlijk moeilijk

 

[AlexCEL]

Korte uitleg:
1. Selecteer je gehele matrix, en druk daarna in menu "gegevens" op "van tabel/bereik"
2. Vink "mijn tabel bevat kopteksten" aan indien dit niet zo is, en druk op ok. Je komt nu in Power Query (PQ).
3. Selecteer door shift vast te houden de kolommen met titel 1 t/m 10.
4. Druk in menu "gegevens transformeren" op "draaitabel opheffen voor kolommen".
5. Selecteer kolom "waarde" en delete deze via de rechtermuisknop.
6. Druk in menu "bestand" op "sluiten en laden".

Klaar. Je gewenste tabel verschijnt op een nieuw tabblad.

PS Welke Excel-versie gebruik je?

 

[Rozanne]

Korte uitleg:
1. Selecteer je gehele matrix, en druk daarna in menu "gegevens" op "van tabel/bereik"
2. Vink "mijn tabel bevat kopteksten" aan indien dit niet zo is, en druk op ok. Je komt nu in Power Query (PQ).
3. Selecteer door shift vast te houden de kolommen met titel 1 t/m 10.
4. Druk in menu "gegevens transformeren" op "draaitabel opheffen voor kolommen".
5. Selecteer kolom "waarde" en delete deze via de rechtermuisknop.
6. Druk in menu "bestand" op "sluiten en laden".

Klaar. Je gewenste tabel verschijnt op een nieuw tabblad.

PS Welke Excel-versie gebruik je?

Het is gelukt! Super dankjewel hiervoor.
Ik heb versie 2210 Build 16.0.15726.20188

 

[snb]

Sub M_snb()
  sn = Blad1.Cells(1).CurrentRegion
  ReDim sp(UBound(sn) * UBound(sn, 2), 1)
  
  For j = 2 To UBound(sn)
    For jj = 2 To UBound(sn, 2)
      If sn(j, jj) = "X" Then
        sp(n, 0) = sn(j, 1)
        sp(n, 1) = sn(1, jj)
        n = n + 1
      End If
    Next
 Next
  
 Blad2.Cells(2, 1).Resize(UBound(sp), 2) = sp
End Sub

 

[snb]

Powerquery maakt in de achtergrond gewoon gebruik van VBA:

Sub M_snb()
  Set O_1 = CreateObject("ADODB.connection")
  O_1.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0""")
     
  For j = 1 To 10
    With CreateObject("ADODB.recordset")
      .Open "SELECT `Ingredient:`, """ & j & """ FROM `Matrix$` where " & Format(j, "`@") & "`=""X""", O_1
      Blad2.Cells(Rows.Count, 1).End(xlUp).Offset(1).CopyFromRecordset .DataSource
    End With
  Next

  Blad2.Cells(1).CurrentRegion.Sort Blad2.Cells(1), , , , , , , 1
End Sub

 

[Enigmasmurf]

@ snb,

Buiten het feit dat jij je doelarray nogal aan de grote kant maakt (wat uiteraard verder geen probleem vormt) is er niet bijster veel verschil tussen #8 en #5

 

[snb]

@enig

Ik ben niet zo'n fan van Excelformules in VBA.
Je doel kon je ook bereiken met specialcells.

 

[JEC.]

Als ik dan toch een suggestie mag geven zou ik de laatste regel zo schrijven. Zo wordt weggeschreven tot waar de array gevuld is. Alles onder de tabel blijft tevens intact

blad2.Cells(2, 1).Resize(n, 2) = sp

 

[peter59]

@SNB

Onderstaande query welke AlexCEL, d.m.v. even iets in te klotsen, lijkt mij toch iets anders als VBA.
Persoonlijk vind ik query's makkelijker te "lezen" dan VBA.
Ik hoop dat ik geen spelfouten heb gemaakt anders deze ook graag even corrigeren.
Dank alvast.

let
    Bron = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Ingredient:", type text}, {"1", type text}, {"2", type text}, {"3", type text}, {"4", type text}, {"5", type text}, {"6", type text}, {"7", type any}, {"8", type text}, {"9", type any}, {"10", type text}}),
    #"Draaitabel voor kolommen opgeheven" = Table.UnpivotOtherColumns(#"Type gewijzigd", {"Ingredient:"}, "Kenmerk", "Waarde"),
    #"Kolommen verwijderd" = Table.RemoveColumns(#"Draaitabel voor kolommen opgeheven",{"Waarde"}),
    #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Kolommen verwijderd",{{"Kenmerk", "Hazard"}})
in
    #"Namen van kolommen gewijzigd"

 

[JEC.]

Het kan ook met 1 dynamische formule in office 365

=LET(r;Matrix!A2:A17;s;SUBSTITUTE(Matrix!B2:K17;"X";Matrix!B1:K1);z;--IF(s="";0;s);x;TOCOL(z);IFERROR(CHOOSE({1\2};TEXTSPLIT(TEXTJOIN("";;REPT(r&"|";z>0));;"|");FILTER(x;x<>0));""))

of

=LET(r;Matrix!B2:K17;IFERROR(CHOOSE({1\2};TEXTSPLIT(TEXTJOIN("";;REPT(Matrix!A2:A17&"|";MMULT(LEN(r);SEQUENCE(10;;;0))));;"|");TOCOL(--SUBSTITUTE(r;"X";Matrix!B1:K1);2));""))

 

[edmoor]

Ik denk dat Rozanne inmiddels een beetje beduust is :d

 

[snb]

Beduusd met een d vermoed ik.