Vba lookup 2 criteria scripting dictionary

[winrow]

Hello,

I kindly request some help regarding a lookup according two criteria that I would like to set up with scripting dictionary.

what I have

1 workbook, 2 worksheets
First worksheet ("datasheet") contains in column A (the different questions= 1st criteria), in column B (pool participants names= 2nd criteria), in column C (the outcomes like yes, no, didn't answer). All three columns have a header.

the second worksheet ("compilation") contains in A2 downwards the unique name of participants and in B1 to the right are the questions

what I'd like is to populate the second worksheet("compilation") with the outcomes according to the two criteria. Some questions might have been asked to specific people but not to others.

Many thanks in advance.

winrow

 

[Fluff]

Untested, but how about

Sub SplitDataName()
   Dim Cl As Range
   Dim Dic As Object
   Dim v1 As String, v2 As String, v3 As String
   Dim Co As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare
   With Sheets("datasheet")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         v1 = Cl.Value: v2 = Cl.Offset(, -1).Value: v3 = Cl.Offset(, 1).Value
         If Not Dic.exists(v1) Then
            Dic.Add v1, CreateObject("scripting.dictionary")
            Dic(v1).Add v2, v3
         ElseIf Not Dic(v1).exists(v2) Then
            Dic(v1).Add v2, v3
         End If
      Next Cl
   End With
   With Sheets("Compilation")
      For Each Cl In .Range("a2", .Range("A" & Rows.Count).End(xlUp))
         If Dic.exists(Cl.Value) Then
            For Each Co In .Range("B1", .Cells(1, Columns.Count).End(xlToLeft))
               If Dic(Cl.Value).exists(Co.Value) Then
                  Cells(Cl.Row, Co.Column).Value = Dic(Cl.Value).Item(Co.Value)
               End If
            Next Co
         End If
      Next Cl
   End With
End Sub

 

[Eric W]

You can do this with formulas:

	A	B	C
1	Question	Name	Answer
2	Gender	Alan	M
3	Gender	Betty	F
4	Excel experience?	Alan	Y
5	Excel experience?	Betty	N
6	Gender	Cyndi	F
7	Age	Cyndi	21
8	Age	Alan	22
9	Age	Betty	23
10	Likes cats	Alan	N
11	Likes cats	Betty	Y
12	Likes cats	Betty	N
13	Age	Bob	24
14	Gender	Bob	M

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

datasheet​

	A	B	C	D	E
1		Age	Excel experience?	Gender	Likes cats
2	Alan	22	Y	M	N
3	Betty	23	N	F	Y
4	Bob	24		M
5	Cyndi	21		F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

compilation​

Array Formulas Cell Formula A2 {=IFERROR(INDEX(datasheet!$B$2:$B$26,MATCH(0,COUNTIF(datasheet!$B$2:$B$26,"<"&datasheet!$B$2:$B$26)-SUM(COUNTIF(datasheet!$B$2:$B$26,A$1:A1)),0)),"")} B1 {=IFERROR(INDEX(datasheet!$A$2:$A$26,MATCH(0,COUNTIF(datasheet!$A$2:$A$26,"<"&datasheet!$A$2:$A$26)-SUM(COUNTIF(datasheet!$A$2:$A$26,$A$1:A1)),0)),"")} B2 {=IFERROR(INDEX(datasheet!$C$2:$C$26,MATCH(B$1&"|"&$A2,datasheet!$A$2:$A$26&"|"&datasheet!$B$2:$B$26,0)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you already have a list of names and/or questions, you don't need the B1 or A2 formulas. If you don't have a list, put the B1 formula in, confirm with Control+Shift+Enter, then drag to the right. Put the A2 formula in with CSE and drag down. Then put in the B2 formula, confirm with Control+Shift+Enter, then copy it to the right and down as needed.

 

[winrow]

Hi Fluff,

I just tested your code on a small subset and it works like a charm. I will test irl conditions tomorrow and push it for 11 questions and 60 participants and let you know. Thank you already very much for your prompt feedback.

Winrow

 

[winrow]

You can do this with formulas:

compilation​

Array Formulas Cell Formula B2 {=IFERROR(INDEX(datasheet!$C$2:$C$26,MATCH(B$1&"|"&$A2,datasheet!$A$2:$A$26&"|"&datasheet!$B$2:$B$26,0)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you already have a list of names and/or questions, you don't need the B1 or A2 formulas. If you don't have a list, put the B1 formula in, confirm with Control+Shift+Enter, then drag to the right. Put the A2 formula in with CSE and drag down. Then put in the B2 formula, confirm with Control+Shift+Enter, then copy it to the right and down as needed.

Hi Eric,

First of all may thanks, I will have stick with Fluff's code as I will not be the end-user for the compilation of these data . However, I am most interested in your array formula. The array formula I used was built based on https://exceljet.net/formula/index-and-match-with-multiple-criteria and it was really slow.

I will use test your array formula tomorrow in real settings and assess its speed. I would be grateful if you could provide me with some pointers on what the pipe symbol is for.

Thanks again

Winrow

 

[Eric W]

The basic idea is that we are concatenating the name with the question, and then searching a table we build with all the names and questions in columns A and B. The pipe symbol is used to separate the parts of the key. In most cases it's not necessary, but occasionally, depending on the data, it could cause a problem. Like if you're concatenating first/last names, and you have

anne liason
ann eliason

The pipe prevents that.

I looked at the other link, and I am familiar with that technique. I can't really speak to the relative efficiencies of the two formulas though. If you find something out, let us know.

 

[winrow]

Hi again Eric,

well, in my settings, your array formula works way faster for a 66*11 range. I.e. Below 1 sec in comparison with 15 sec

glad I could learn two ways to tackle down my problem and an alternative to an array formula.

best

Winrow