Hi everyone, I have two worksheets (A & B). I would like a formula/macros to lookup B, find the value in row n, and return it in A. I have provided examples in A (column 3) on what values should be obtained.
Worksheet A
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Worksheet B
<colgroup><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>
As shown, there are specific criteria in Worksheet B that must be fulfilled before the value can be identified:
1. Matching "Activity Number" must be in row n+1.
2. "Activity" in row n must be "Open", while "Close" in row n+1.
3. "Position" in row n must be of non-numeric value.
4. "Unit" in row n must match that in Worksheet A.
5. "In/Out" in row n+1 must be "Out"
I would welcome any help! Thanks so much in advance!
Worksheet A
Unit | Activity Number | Value |
101 | 2222 | #N/A |
101 | 4444 | 300 |
102 | 5555 | #N/A |
103 | 7777 | #N/A |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Worksheet B
Unit | Activity Number | Activity | In/Out | Position | Value |
101 | 1111 | Close | In | Tom | 100 |
101 | 1111 | Open | In | Tom | 100 |
101 | 2222 | Close | In | 10 | 200 |
101 | 2222 | Open | In | 10 | 200 |
101 | 3333 | Close | In | Jane | 300 |
101 | 3333 | Open | In | Jane | 300 |
101 | 4444 | Close | Out | 20 | 400 |
101 | 4444 | Open | Out | 20 | 400 |
102 | 5555 | Close | Out | 30 | 500 |
102 | 5555 | Open | Out | 30 | 500 |
103 | 6666 | Close | In | Harry | 600 |
103 | 6666 | Open | In | Harry | 600 |
103 | 7777 | Open | Out | 40 | 700 |
103 | 7777 | Open | Out | 40 | 700 |
<colgroup><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>
As shown, there are specific criteria in Worksheet B that must be fulfilled before the value can be identified:
1. Matching "Activity Number" must be in row n+1.
2. "Activity" in row n must be "Open", while "Close" in row n+1.
3. "Position" in row n must be of non-numeric value.
4. "Unit" in row n must match that in Worksheet A.
5. "In/Out" in row n+1 must be "Out"
I would welcome any help! Thanks so much in advance!