Vba lookup 2 criteria scripting dictionary

winrow

New Member
Joined
Feb 2, 2018
Messages
23
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Untested, but how about
Code:
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
 
Upvote 0
You can do this with formulas:

ABC
QuestionNameAnswer
GenderAlanM
GenderBettyF
Excel experience?AlanY
Excel experience?BettyN
GenderCyndiF
AgeCyndi
AgeAlan
AgeBetty
Likes catsAlanN
Likes catsBettyY
Likes catsBettyN
AgeBob
GenderBobM

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]21[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]23[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]24[/TD]

[TD="align: center"]14[/TD]

</tbody>
datasheet



ABCDE
AgeExcel experience?GenderLikes cats
AlanYMN
BettyNFY
BobM
CyndiF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]23[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]24[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]21[/TD]

</tbody>
compilation

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=IFERROR(INDEX(datasheet!$C$2:$C$26,MATCH(B$1&"|"&$A2,datasheet!$A$2:$A$26&"|"&datasheet!$B$2:$B$26,0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
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
 
Upvote 0
You can do this with formulas:
compilation

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



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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top