# Code to count matching record pairs

#### Brew

##### Well-known Member
How do I create a code that will count each time the matching value in F1022:F1035 precedes a matching value in G1021:T1021 from F1011:Y1011 and record in the G1022:T1035 on the same row as the match in F1022:F1035 and the same column as the match in G1022:T1035.

example

If F1011:Y1011=7,6,5,11,5,5,4,6,10,7,9,6,6,4,5,10,5,9,6,5
If F1022:F1035=1,2,3,4,5,6,7,8,9,10,11,12,13,14
If G1021:T1021=1,2,3,4,5,6,7,8,9,10,11,12,13,14
Partial results in G1022:T1035
L1028=1, because f1011=7,g1011=6 and f1028=7, L1021=6,
this match occurred once in the range F1011:Y1011 and
result is recorded on row 1028 and column L
K1027=2, because G1011=6,H1011=5 & X1011=6,Y1011=5 and f1027=6, K1021=5,
this match occurred twice in the range F1011:Y1011 and
result is recprded on row 1027 and column K

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Help!!!
Below is a sample of the source data range to search and match:
trending3b.xls
FGHIJKLMNOPQRSTUVWXY
101176511554610796645105963
AZ_eve73

A formula approach...

G1022, copied across and down:

=SUMPRODUCT(--(\$F\$1011:\$X\$1011=\$F1022),--(\$G\$1011:\$Y\$1011=G\$1021))

Then, custom format G1022:T1035 to hide zero values...

Format > Cells > Number > Custom > Type:

0;-0;;@

Hope this helps!

Yes, Domenic that works great!. thanks

Replies
12
Views
2K
Replies
17
Views
1K

1,219,997
Messages
6,151,365
Members
451,022
Latest member
Baijano23

### 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.

### Which adblocker are you using?

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

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