Customers took two separate surveys : How to extract customers that took both + survey score?

[kalamazoo]

I have an excel worksheet with 4 columns. Around 10,000ish customers.

Column A (Customers took 1st survey)
Column B (Score on 1st survey)
Column C (Customers took 2nd survey)
Column D (Score on 2nd survey)

Now some customers took the 1st survey, some took the 2nd survey, some took both.

I would like the ones that took both. What is the best way to go about extracting the customers that took both, with their scores on the 1st and 2nd survey?

Thank you for your help,

J

 

[mumps]

This macro will hide the customers that took only one survey:

Sub HideRows()
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

 

[Eric W]

If the customers are placed on the same row for both scores, and if one test wasn't taken the corresponding cell is blank, then mumps' code will work. If the customers are scattered in no particular order, then this could work:

	A	B	C	D	E	F	G	H
Customer	Score1	Customer	Score2	Customers who took both	Score1	Score2
Amy	Mac	Amy
Bob	Dave	Bob
Cal	Amy	Felice
Don	Bob	Mac
Ed	Felice
Felice	Jane
Mac	Zack
Al

<colgroup><col style="width: 25pxpx"><col><col><col><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"]1[/TD]

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

[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

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

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

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

[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

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

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

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

[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]

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

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

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

[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet 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] "]G2[/TH]
[TD="align: left"]=IF(F2<>"",SUMIF(A:A,F2,B:B),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(F2<>"",SUMIF(C:C,F2,D:D),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[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] "]F2[/TH]
[TD="align: left"]{=IF(F1="","",IFERROR(INDEX($A:$A,SMALL(IF($A$2:$A$20<>"",IF(COUNTIF($F$1:$F1,$A$2:$A$20)=0,IF(ISNUMBER(MATCH($A$2:$A$20,$C$2:$C$20,0)),ROW($A$2:$A$20)))),1)),""))}[/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]

 

[kalamazoo]

Hello Eric,

Thank you for the reply. Also thank you to mumps. The customers are scattered in no particular order. Instead of names the customers are assigned numbers (saved as text). Hopefully that does not make too much of a difference. When I entered the formula you provided I just got the first match pasted all the way down. I am sure this is user error on my part. I removed the scores from this image because this is just dealing with columns A, C and F.

 

[Eric W]

My first question is, did you enter the formula as an array formula? An array formula has special processing. When you enter the formula, copy it from my previous post, select the F2 cell, paste the formula into the formula bar, REMOVE the {} from the ends, change the ranges to match your sheet, then hold down the Control and Shift keys, and press enter. If you did it right, Excel will put the {} on it for you. Now you can copy the cell and paste it down the column (or drag it).

Another thought: can the same customer take one of the tests more than once? If so, you may want to change the SUMIF formulas to AVERAGEIF.

If you did enter the formula with Control+Shift+Enter (CSE), let me know and I'll see what else I can think of.

 

[kalamazoo]

Ah ok. I guess I missed a step somewhere along the way because that worked.

To answer your other question no the customer can only take each survey once.

 

[kalamazoo]

Not sure if you will see this but just had a quick question. How long should the calculation of this many array formula's take?

 

[Eric W]

Good question. I tried to optimize it as much as possible, but 10,000 rows still could take a while. If it takes too long, the next idea would be to write a macro that reads both columns, finds the duplicates internally, and writes out the results. It wouldn't be a live update, but you'd only run it when needed.

Let me know if that's of interest.

 

[kalamazoo]

The array's were working great but ended up being a bit too slow. I looked around and found this code which seemed to work but I am afraid this is a bit advanced for me to really know how to alter it without screwing it up.

So basically what I did was put the 2 identifiers in columns A and B. So I had the two scores in C and D.

So A, B, and C seemed to match up. However D did not end up aligning with them. Hopefully someone can point me in the right direction.

Sub test()
Dim a, i As Long, ii As Long, w, x, n As Long
With Range("a3").CurrentRegion
a = .Value
.ClearContents
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(a, 1)
If a(i, 1) <> "" Then
If Not .exists(a(i, 1)) Then
Redim w(1 To UBound(a, 2))
w(1) = a(i, 1): .Item(a(i, 1)) = w
End If
End If
Next
For i = 1 To UBound(a, 1)
If a(i, 2) <> "" Then
If Not .exists(a(i, 2)) Then
Redim w(1 To UBound(a, 2))
Else
w = .Item(a(i, 2))
End If
For ii = 2 To UBound(a, 2)
w(ii) = a(i, ii)
Next
.Item(a(i, 2)) = w
End If
Next
x = Application.Transpose(Application.Transpose(.items))
n = .Count
End With
.Resize.Value = x
End With
End Sub

 

[Eric W]

I thought that code was a bit of a mess, so I just rewrote it.

Sub test2()
Dim MyRange As Variant, MyRow As Long, MyDict As Object, MyOut As Object


    MyRange = Range("A1:D" & Range("A1").CurrentRegion.Rows.Count).Value
    Set MyDict = CreateObject("Scripting.Dictionary")
    Set MyOut = CreateObject("Scripting.Dictionary")
    
    For MyRow = 1 To UBound(MyRange)
        If MyRange(MyRow, 1) <> "" Then MyDict(MyRange(MyRow, 1)) = MyRange(MyRow, 1) & "," & MyRange(MyRow, 3)
    Next MyRow
    
    For MyRow = 1 To UBound(MyRange)
        If MyRange(MyRow, 2) <> "" Then
            If MyDict.exists(MyRange(MyRow, 2)) Then MyOut(MyRange(MyRow, 2)) = MyDict(MyRange(MyRow, 2)) & "," & MyRange(MyRow, 4)
        End If
    Next MyRow
    
    Range("F1").Resize(MyOut.Count) = WorksheetFunction.Transpose(MyOut.items)
    Range("F1").Resize(MyOut.Count).TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, Comma:=True
    
End Sub

The input columns (A:D) and the output column (F1) are marked in red. This assumes that column A has a customer number, C has the corresponding score, B has a customer number, and D has the corresponding score. Those column numbers (1-4) are also marked in red, so you should be able to change them easily if you need to.

There are a few other really sharp VBA programmers here, so someone may come up with a nicer version yet.

Let me know how this works.