Pivot, MSQuery or crafty use of MATCH and OFFSET?

[green biro]

Please can someone give me a leg-up for the following:

Prob1 - Easy (on same sheet)

Rows are names (eg Brown, Smith, Jones...)
Cols are Criteria (A, B, C, D...)
Data is 1, 2 or blank

I would like to cross tabulate the 1s against the 2s giving me
Rows of Criteria (A, B, C, D...)
Cols of Criteria (A, B, C, D...)
Data: Count of times row critera was 1 in original data and col criteria was
2 in original data
Extra info for clarification: AA, BB, CC etc would be nill as only one value
was possible in original data


Prob 2 - Hard (two sheets)

Original data as before except that criteria (cols) different for each sheet
ie
Sheet1 cols (A, B, C, D...)
Sheet2 cols (Z, Y, X...)
Rows of name same on both sheets

Now I would like to cross tabulate the 1s in sheet1 against the 1s in sheet2
thus:
Rows of Sheet1's Criteria (A, B, C, D...)
Cols of Sheet2's Criteria (Z, Y, X,...)
Data: Count of times row critera was 1 in sheet1 data and also 1 in sheet2
data

This exercise would then be repeated to analyse 1s against 2s, 2s against 1s
and 2s against 2s


Thanks in advance for any pearls of wisdom

GB

(e-mail address removed)

 

[Martin Fishlock]

Your homework is quite interesting.

I don't quite understand your requirements and may example answers would help.

 

[green biro]

Thanks for your interest.but I think that I may have worked out a way
forward.

As I have column headers in the original data, I can use MATCH and OFFSET to
create a new sheet that for each person shows which column has a '1' and
which has a '2' eg
Name Crit1.1, Crit 1.2, Crit 2.1, Crit 2.2
Brown A, B, X, W
Jones A, C, Z, X
Smith C, A, Y, Z

Then I can simply pivot any two columns I choose.