# Count Matched Results ( Frequency)

1. ## Count Matched Results ( Frequency)

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

2. ## Re: Count Matched Results ( Frequency)

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.

3. ## Re: Count Matched Results ( Frequency)

Thanks Olly!

There are currently 1 users browsing this thread. (0 members and 1 guests)