JorgenKjer
Board Regular
- Joined
- Aug 1, 2016
- Messages
- 65
- Office Version
- 2013
- Platform
- Windows
Hi
Can anyone help me with a VBA code, which, I think, is very complicated?
The criteria is, if there is a match between header (D1: G1) and column C and at the same time has a blank cell in column A, e.g. E1 and C2 and A2 are blank, E2 must be 1 (one). If no match, the cells in the matrix should be blank.
As a result, in column H, a count of how many times each department (column B) has a match in the matrix. However, to make it even more complicated. If a department has more than one match in the same location, it should count as 1 match only. E.g., Cell F4 and F5 should count as 1 and cell D6 as 1, therefore the total is 2 for department 3.
Column A:C is dynamic, however, the values in column C will always be a match with one of the headers. Header (D1: G1) are constant.
I hope someone has time to help me with the code.
Yours sincerely
Jorgen
Can anyone help me with a VBA code, which, I think, is very complicated?
The criteria is, if there is a match between header (D1: G1) and column C and at the same time has a blank cell in column A, e.g. E1 and C2 and A2 are blank, E2 must be 1 (one). If no match, the cells in the matrix should be blank.
As a result, in column H, a count of how many times each department (column B) has a match in the matrix. However, to make it even more complicated. If a department has more than one match in the same location, it should count as 1 match only. E.g., Cell F4 and F5 should count as 1 and cell D6 as 1, therefore the total is 2 for department 3.
Column A:C is dynamic, however, the values in column C will always be a match with one of the headers. Header (D1: G1) are constant.
I hope someone has time to help me with the code.
Yours sincerely
Jorgen
A | B | C | D | E | F | G | H | |
1 | Date | Department | Location | 100 | 200 | 300 | 400 | Match |
2 | 1 | 200 | 1 | 1 | ||||
3 | 12-01-2021 | 3 | 200 | 2 | ||||
4 | 3 | 300 | 1 | 2 | ||||
5 | 3 | 300 | 1 | 2 | ||||
6 | 3 | 100 | 1 | 2 | ||||
7 | 12-01-2021 | 3 | 400 | 2 | ||||
8 | 4 | 100 | 1 | 1 | ||||
9 | 15-01-2021 | 11 | 300 | 3 | ||||
10 | 11 | 300 | 1 | 3 | ||||
11 | 11 | 100 | 1 | 3 | ||||
12 | 11 | 200 | 1 | 3 | ||||
13 | 15 | 400 | 1 | 1 | ||||
14 | 12-01-2021 | 15 | 400 | 1 | ||||
15 | 17 | 400 | 1 | 1 |