jimbo, I have to make several assumptions to provide this solution.
First assumption: that in the source table, the columns for observations are
the same for all columns for each person. That is, you could have just had
observation 1 observation 2
in the first row of the table and left those titles out of the entry rows
for the others like bill jones and mary Dee.
Second assumption, when you do comparisons, you're wanting to compare one
category for 2 or more people. That is you always want to compare weights to
weights, and not weight for one person to belt size for someone else.
I've also assumed, for the purpose of the formula I'll give you, that your
table goes from A1 over and down to C14.
For the 'solution' comparison table, we need to set up a similar table, but
where you have a person's name in the first row of a group, we put the
category, and instead of categories below it, we put the names of the people
to compare values from that category to. My first group starts at A17 and
looks like this:
A B C
17 belt size observation 1 observation 2
18 bill jones 29 28
19 john smith 40 38
Here is the basic formula that I placed into cell B18 of this comparison
group. Remember that this is a formula and it is typed in as a continuous
entry, it doesn't have the line breaks that this board may force into it.
=INDEX($A$1:$C$14,MATCH($A18,$A$1:$A$14,0)+MATCH($A$17,OFFSET($A$1:$A$14,MATCH($A18,$A$1:$A$14,0),0),0),MATCH(B$17,$A$1:$C$1,0))
This formula can then be filled to the right and down and will adjust
automatically for the columns/rows it is filled into and give you the values
for the category listed in the upper left cell of it (A17) for all names
listed in the rows below that category (A18 and A19). Here are some more
explanations of what the various address ranges in the formula refer to:
$A$1:$C$14 is the entire source table range from the upper left corner cell
to the lower right corner cell
$A$1:$A$14 is the entire list of the source table in column a, with the
names and categories
$A$1:$C$1 is the first row of the source table (includes the name plus all
"observation #" entries
$A$17 is the cell in this comparison group holding the name of the category
values to be compared
$A$18 refers to the cell on the row for the formula with the person's name
in it
Remember, there is almost always HelpFrom (at) jlathamsite.com if you need
further explanation.
