ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
L1XDTP1q1CorrectAh yesexample correctNice!AG02
2
L1TTTP1q2IncorrectMy Myexample incorrectnot quite.q1q7
3
L1ssTP1q3Option1AAAAG02
4
L1LLTP2q4Option2BBB
5
L1TTTP2q5Option3CCC
6
L1DDTP2q6Correct AnswerABC
7
L1XDTP3q7
8
L1TTTP3q8
9
L1ssTP3q9Current Output:q1AG02q7AG02
10
Expected Output:q1AG02Shifts to:q1AG03then to K and so on
11
q1AG02q7AG03
12
13
14
15
isnt TP3 value q7?:
The first half of the formula returns as many blanks in between the first and second display as there are unique items in column C that don't meet the display condition. For instance, changing one of the TP2 into TPx would result in an extra space.
16
query section of the combined formula should automatically shift to the next column for after each non-blank display.
Therefore, if H,I compared for q1, then if H,J compared for q7. In H10, "q1" is displayed because A= "L1" and B = "XD" and "AG02" is displayed because "Option1" = "Correct Answer" for column I. The formula needs to work exactly as is, except move query one column to the right when a value would be displayed. So in this example, I -> J -> K -> L , and so on.
17
18
19
20
21
1. can you enter the expected output manually; right now it shows error
22
2. can you also enter the expected output (manually) for a scenario where TP2 also has the matching condition A= "L1" and B = "XD" along with TP1 and TP3
23
3. any chance TP(column C) has more than 1 match of A= "L1" and B = "XD"? Is it always going to be either 0 (like TP2) or 1 (TP1, TP3)?
24
25
26
1. Expected Output:q1AG02Shifts to:q1AG03
27
2. Expected Output:q1AG02(if "LL"="XD") -> q4AG03q7AG04
28
3. There will always only be one match of column C where A = "L1" and B ="XD"
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100