VBA that would spot an ID according to a group of Answers

[jbesclapez]

Dear All (and Mike )

I have a column with a list of answers (IDs)
I have a table with 7 columns for the answers (some are empty) and an additional column with Result
The answer given in the first table have to be found in the second table to give the Result.
The order of the Answer is not important (Q002R03 + Q001R99 is the same then Q001R99 + Q002R03...)
You must read the table with AND attributes. So for example in the second row it is Q002R03 AND Q001R99 that gives Result B. But the Answers given do not have Q001R99 so result B cannot be expected as a result.

Please note that in the 8 you have Q006R12 + Q002R99 but no Q002R99 in the answer given. But still the result A is expected but coming from other "formulae".

The last table brings the expected results.

Answer given

Q001R01

Q002R03

Q003R05

Q004R12

Q005R10

Q006R12

Q009R11

<tbody>
</tbody>

Answer1	Answer2	Answer3	Answer4	Answer5	Answer6	Answer7	Result
Q001R01	A
Q002R03	B
Q004R12	Q001R01	Q003R05	C
Q005R10	D
Q005R10	E
Q005R10	F
Q006R12	A
Q006R12	G
Q001R01	Q002R03	Q003R05	Q004R12	Q005R10	Q006R12	A
Q001R01	Q002R03	Q003R05	Q004R12	Q005R10	Q006R12	Q009R11	A

<tbody>
[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="bgcolor: #FCE4D6"] Q001R99 [/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: 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: 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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FCE4D6"] Q002R99 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FCE4D6"] Q002R99 [/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>

<tbody>
[TD="bgcolor: #FFFFFF"] Result [/TD]

[TD="bgcolor: #FFFFFF"] A [/TD]

[TD="bgcolor: #FFFFFF"] C [/TD]

[TD="bgcolor: #FFFFFF"] D [/TD]

[TD="bgcolor: #FFFFFF"] E [/TD]

[TD="bgcolor: #FFFFFF"] F [/TD]

</tbody>

Thanks for your time

 

[MickG]

Try this:-
https://app.box.com/s/r2c2nq6hp80pz93j56emytiut8mym5dx

 

[Marcelo Branco]

Maybe using Advanced Filter + a formula

Something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][td="bgcolor: #DCE6F1"]

M​

[/td][td="bgcolor: #DCE6F1"]

N​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Answer given​

[/td][td][/td][td]

Result​

[/td][td][/td][td]

Answer1​

[/td][td]

Answer2​

[/td][td]

Answer3​

[/td][td]

Answer4​

[/td][td]

Answer5​

[/td][td]

Answer6​

[/td][td]

Answer7​

[/td][td]

Result​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Q001R01​

[/td][td][/td][td]

A​

[/td][td][/td][td]

Q001R01​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

A​

[/td][td][/td][td]

TRUE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Q002R03​

[/td][td][/td][td]

C​

[/td][td][/td][td]

Q002R03​

[/td][td]

Q001R99​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

B​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Q003R05​

[/td][td][/td][td]

D​

[/td][td][/td][td]

Q004R12​

[/td][td]

Q001R01​

[/td][td]

Q003R05​

[/td][td][/td][td][/td][td][/td][td][/td][td]

C​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Q004R12​

[/td][td][/td][td]

E​

[/td][td][/td][td]

Q005R10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

D​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Q005R10​

[/td][td][/td][td]

F​

[/td][td][/td][td]

Q005R10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

E​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Q006R12​

[/td][td][/td][td][/td][td][/td][td]

Q005R10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

F​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Q009R11​

[/td][td][/td][td][/td][td][/td][td]

Q006R12​

[/td][td]

Q002R99​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

A​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Q006R12​

[/td][td]

Q002R99​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

G​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Q001R01​

[/td][td]

Q002R03​

[/td][td]

Q003R05​

[/td][td]

Q004R12​

[/td][td]

Q005R10​

[/td][td]

Q006R12​

[/td][td][/td][td]

A​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Q001R01​

[/td][td]

Q002R03​

[/td][td]

Q003R05​

[/td][td]

Q004R12​

[/td][td]

Q005R10​

[/td][td]

Q006R12​

[/td][td]

Q009R11​

[/td][td]

A​

[/td][td][/td][td][/td][/tr]
[/table]

Type Result in C1

Leave N1 blank and enter this formula in N2
=SUMPRODUCT(--ISNUMBER(MATCH(E2:K2,$A$2:$A$8,0)))=COUNTA(E2:K2)

Select the range (E1:L11 in the data sample above)

Data > Advanced Filter
pick Copy to another location
List range: $E$1:$L$11
Criteria Range: $N$1:$N$2
Copy to: $C$1
check Unique records only
Ok

Hope this helps

M.

 

[jbesclapez]

I had time to try both solutions.
I am going with the one of Mike as it runs faster on my massive Sheet.

However, the formula was also interesting to know. Never though about it like that.

Thanks to both of you.

 

[MickG]

You're welcome