Driedubbel zoeken met vertikaal als horizontaal

[Rizo83]

Ik heb een probleem waar ik niet uit kwam, vandaar deze vraag:

Ik zou graag in tabblad 1 meerdere variabelen willen weergeven van een persoon (in een kolom) die voldoet aan twee voorwaarden (dag van de week & Variabele 1,2,3,4,5 of 6) in blad 2.

Ik kom niet aan de formule die het combineren voor mekaar krijgt. Het bestand ter verduidelijking.


Bekijk bijlage Dubbel vertikaalzoeken en enkel horizontaal.xlsx

 

[Rizo83]

Misschien ter verduidelijking:

In Blad1 E6 moet komen te staan 1. (Variabele afhankelijk van C6, E4, E5 in Blad1 afkomstig uit D3 blad2)

Thnx!

 

[robdgr]

Met de volgende formule lukt je dat:

=SOMPRODUCT((TEKST(WEEKDAG(Blad2!D1:AA1);"dddd")=Blad1!E4)*(Blad2!D2:AA2=Blad1!E5)*(Blad2!C3:C6=Blad1!C6))

Je moet wel van de getallen op blad2 getallen maken, nu zijn het teksten.

 

[oeldere]

Met een macro om de data in een verticale vorm te krijgen.

Daarna de weeknummer via een formule toegevoegd.

Daarna een draaitabel met de analyze van de data.

De draaitabel heb ik verwijderd anders werd het bestand te groot.

 

[Rizo83]

Hmmm... Dank je wel voor je bericht. Het enige is dat ik de originele data zoveel mogelijk in tact wil houden en geen gebruik wil/kan maken van macro's....

 

[robdgr]

Waar zie je macro's? Ik geef je slechts een formule.

 

[oeldere]

Voor het te kunnen plaatsen heb ik een binaire file gemaakt.

Hieronder de macro.

Sub CONVERTROWSTOCOL_Oeldere_revisted_new()

Dim rsht1 As Long, rsht2 As Long, I As Long, col As Long, wsTest As Worksheet, mr As Worksheet, ms As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

'set the data
                 

Set mr = Sheets("blad2")                                  'this is the name of the source sheet
 
Set ms = Sheets("Output")                                       'this is the name of the destiny sheet

col = 3
'End set the data

    With ms
     .UsedRange.ClearContents
     .Range("A1:D1").Value = Array("Naam", "Datum", "Variable", "value")
    End With
    
    rsht2 = ms.Range("A" & Rows.Count).End(xlUp).Row
    
    
    With mr
          rsht1 = .Range("C" & .Rows.Count).End(xlUp).Row
          For I = 3 To rsht1
                Do While .Cells(1, col).Value <> "" 'And .Cells(I, col).Value <> ""
                rsht2 = rsht2 + 1
               
                ms.Range("A" & rsht2).Value = .Range("C" & I).Value
                
                ms.Range("B" & rsht2).Value = .Cells(1, col).Value
                
                ms.Range("C" & rsht2).Value = .Cells(2, col).Value
                
                ms.Range("D" & rsht2).Value = .Cells(I, col).Value
         
                col = col + 1
            Loop
            col = 4
        Next
    End With
    
  With ms
  
  
  
  
    .Columns("A:Z").EntireColumn.AutoFit
    
    End With
    
End Sub

 

[Rizo83]

Waar zie je macro's? Ik geef je slechts een formule.

Klopt! Dank! Ik begin te begrijpen hoe die zou moeten werken. Alleen geeft die nu de melding dat de formule naar lege cellen verwijst als ik de formule in E6 plak.

 

[oeldere]

Het is altijd goed om te vermelden op welke reactie je reageert.

Alleen geeft die nu de melding dat de formule naar lege cellen verwijst als ik de formule in E6 plak.

welke cel en welke formule?

 

[Rizo83]

Met de volgende formule lukt je dat:

=SOMPRODUCT((TEKST(WEEKDAG(Blad2!D1:AA1);"dddd")=Blad1!E4)*(Blad2!D2:AA2=Blad1!E5)*(Blad2!C3:C6=Blad1!C6))

Je moet wel van de getallen op blad2 getallen maken, nu zijn het teksten.

Sorry!

Alleen geeft de formule nu de melding dat de formule naar lege cellen verwijst als ik de formule in E6 plak. Waar kan dit aan liggen? De tekst is omgezet naar getallen in blad2...

 

[robdgr]

Bij mij werkte de formule probleemloos, maar ik heb ook nog iets met de bereiken op blad2 gedaan.

 

[Rizo83]

Bij mij werkte de formule probleemloos, maar ik heb ook nog iets met de bereiken op blad2 gedaan.

Iets bijzonders?


Ik merk ook dat de dagen van de week in het Engels uit de formule komen. Wellicht heeft dat invloed?

 

[snb]

Waarom geen autofilter in blad 2 ?

 

[Rizo83]

Waarom geen autofilter in blad 2 ?

???

 

[Rizo83]

Niemand die een oplossing weet....? Want werken doet ie nog niet ...

 

[Rizo83]

Ik probeer zo veel mogelijk duidelijkheid te geven waarom de formule van robdgr bij niet werkt:

- De cellen met getallen heb ik veranderd naar getal ipv tekst.

- De cellen C3:C6 geeft Excel als 'leeg' aan. Ik krijg dus met het invullen van de formule in de juiste cel 0.00 met een melding dat de cel leeg is.

- Met het wijzigen van de cellen in Algemeen ipv Tekst en de formule laten verwijzen naar C3:C5, blijft Excel 0.00 aangeven.

- Ook heb ik de verwijzing naar de cellen in Blad2! veranderd naar AS ipv AA, maar dit verandert verder niets aan de zaak.

- Ik wil zo weinig mogelijk wijzigingen aanbrengen aan Blad2! omdat daar ruwe data in staat waaraan ik niets wil/kan wijzigen

- Ik wil geen gebruik maken van VBA's als het ook zonder kan ..

Kortom: ik ben nog niet veel verder, ...

Nogmaals bedankt voor het meedenken!

 

[Rizo83]

Daar nog aan toegevoegd snap ik waarom de formule het niet doet:

- Deel 1 van de formule:
TEKST(WEEKDAG(D1),"dddd")=Blad1!E4
Geeft onwaar, omdat er een Engelse "Monday" uit komt. Wanneer ik de cel verander in Blad1!, geeft excel de waarde "Waar"

- Deel 2 van de formule:
Blad2!D2:AA2=Blad1!E5
Geeft "Onwaar", terwijl dit wel "Waar" had moeten zijn...

- Deel 3 van de formule:
Blad2!C3:C5=Blad1!C6
Geeft #Waarde, wat ik niet snap waarom dat zo is.

Kortom: ik snap waarom de formule Somproduct niet werkt, ... maar waarom de formules daarin niet?

 

[robdgr]

In de bijlage staat een bij mij werkend exemplaar. Ik heb er een andere formule los bij gezet met vert.zoeken naar de Engelstalige dag van de week, die ik hier niet kan testen. Ik heb de samengestelde cellen uit elkaar gehaald en iedere dag zes variabelen gegeven. Dat varieerde ook nogal.

 

[Rizo83]

Dank!

Blijft het bijzonder dat ik een NL'se versie van Excel heb, maar alsnog er 'Monday' uit komt, maar dit is snel op te lossen.