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

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
175
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>

Answer1Answer2Answer3Answer4Answer5Answer6Answer7Result
Q001R01A
Q002R03Q001R99B
Q004R12Q001R01Q003R05C
Q005R10D
Q005R10E
Q005R10F
Q006R12Q002R99A
Q006R12Q002R99G
Q001R01Q002R03Q003R05Q004R12Q005R10Q006R12A
Q001R01Q002R03Q003R05Q004R12Q005R10Q006R12Q009R11A

<tbody>
</tbody>


Result
A
C
D
E
F

<tbody>
</tbody>


Thanks for your time
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Maybe using Advanced Filter + a formula

Something like this

A
B
C
D
E
F
G
H
I
J
K
L
M
N
1
Answer given​
Result​
Answer1​
Answer2​
Answer3​
Answer4​
Answer5​
Answer6​
Answer7​
Result​
2
Q001R01​
A​
Q001R01​
A​
TRUE​
3
Q002R03​
C​
Q002R03​
Q001R99​
B​
4
Q003R05​
D​
Q004R12​
Q001R01​
Q003R05​
C​
5
Q004R12​
E​
Q005R10​
D​
6
Q005R10​
F​
Q005R10​
E​
7
Q006R12​
Q005R10​
F​
8
Q009R11​
Q006R12​
Q002R99​
A​
9
Q006R12​
Q002R99​
G​
10
Q001R01​
Q002R03​
Q003R05​
Q004R12​
Q005R10​
Q006R12​
A​
11
Q001R01​
Q002R03​
Q003R05​
Q004R12​
Q005R10​
Q006R12​
Q009R11​
A​

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

Board Regular
Joined
Feb 6, 2010
Messages
175
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.
 

Forum statistics

Threads
1,089,437
Messages
5,408,214
Members
403,190
Latest member
RBrite

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top