VBA code - IF - COUNT IF - MATRIX

JorgenKjer

Board Regular
Joined
Aug 1, 2016
Messages
65
Office Version
  1. 2013
Platform
  1. 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

ABCDEFGH
1DateDepartmentLocation100200300400Match
2120011
312-01-202132002
4330012
5330012
6310012
712-01-202134002
8410011
915-01-2021113003
101130013
111110013
121120013
131540011
1412-01-2021154001
151740011
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Why you have different match number for Same Department?
Is it Correct?
Why H9 & H10 is 3?
Why H13 & H15 is 1

This is solution for Column E:H
NewRun.xlsm
CDEFGHIJKLMNOPQR
1DepartmentLocation100200300400Match100200300400MatchCountif
21200 1  1 1  11
33200    2    31
43300  1 2  1 32
53300  1 2  1 32
631001   21   31
73400    2    31
841001   11   11
911300    3    32
1011300  1 3  1 32
11111001   31   31
1211200 1  3 1  31
1315400   11   112
1415400    1    12
1517400   11   111
16
17
Sheet1
Cell Formulas
RangeFormula
E2:H15E2=IF(AND($D2=E$1,$B2=""),1,"")
I2,O2:O15I2=SUMPRODUCT(($C$2:$C$15=$C2)*($E$2:$H$15=1))
K2:N15K2=IF(AND($D2=E$1,$B2=""),1,"")
Q2:Q15Q2=COUNTIFS($C$2:$C$15,C2,$D$2:$D$15,D2)
 
Last edited:
Upvote 0
Solution
Thank you maabadi for showing interest in my problem



Department 1 has 1 match – Row 1

Department 3 has 2 matches – Row 4, 5, 6

Department 4 has 1 match – Row 8

Department 11 has 3 matches – Row 10, 11, 12

Department 15 has 1 match – Row 13

Department 17 has 1 match – Row 15

Cells H9, H10, H11, H12 are all Department 11, but only 3 meet the criteria as there is a date in cell A9.

H13 and H14 are Department 15 but there is only 1 match as there is a date in cell A14.

H15 is 1, there is only one row with Department 17 and it is a match



I hope it answers your questions

I know it's a somewhat complicated code, but I'll be very grateful his you can help

Yours sincerely

Jorgen
 
Upvote 0
Hey maabadi

Thanks for your quick answer.
Your solution works perfectly.
I am very grateful that you would spend time solving this problem for me.

Yours sincerely
Jorgen
 
Upvote 0
You 're Welcome.
For Match column, I cann't do anything.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top