Count Matched Results ( Frequency)

[bob33]

Hi All,
This is likely more of a database need but since I don't have one easily accessible, I'm doing by Excel.
In the attached workbook are test results for L,M,D,B tests. A before treatment and an after treatment value for each subject (serial) in the workbook.
What is needed:
For each value in L, B, M, D rows, count for each Before Treated value, the number of After Treatment values that occur by each After Treatment value (a count of matched occurrences).
Example:
For each Before Value 1, how many times is the After Treatment value 1., and 2, and 3 etc, up through 15.
So, the value in cell d1154 (3) is matched to value After Treatment to al1154 (2).
So, the matrix at left in spreadsheet would have a 1 in d1164.
The next time a 2 is matched to a Before Treatment value of 3, the value in d1164 would increase by 1 (a counter of frequency).
Note: There may not be a Before or After 1 or other number; 1 through 15 were chosen as they are the possible range values.
I thought match or look-up but since the results can't be ordered (it would ruin the relationship of Before and After results; become unmatched) I didn't know which way to go.
Thanks for any suggestions/help!
Bob

[Olly]

You can do this using Power Query, to extract the data from your results workbook, normalise the data (one row per item, with a Pre & Post value, then summarise using a pivot table.

Query:

Please Login or Register  to view this content.

Close and Load To Data Model, then create a Pivot Table from the Data Model, with [Pre] in Rows, [Post] in Columns and [Count of Items] in Values.

[bob33]

Thanks Olly!