Hi All, I hope someone can help me.

I have been asked to take a matrix of source data (see "Source Data"
below) With the values in Column A repeating themselves (possibly
hundreds of times), and the values in Row 1 being unique, but
belonging to specific groups (see "Source Group" below), and to
collapse this matrix into a Summary matrix with all the values in
column A only appearing once, and all the Row 1 Groups only appearing
once (see "Desired Result" below), with the rows & columns transposed.

I have solved this by a 2 step Sumif, one vertical, and one
horisontal, step one being the "Intermediate Step" below, and Step 2
being the "Desired Result".

I realise that this can be done with pivot tables, but the user is not
happy with that. They want to be able to open a spreadsheet, paste the
source data, and then print out the result, with no further
interaction..

I have the feeling that there must be another single-step, elegant
solution to this, but I cannot think of it. I would appreciate any
input.

Thanks,
Peter


Source Data
A1001 B200 AB2 ADDE FE
A 1 2 3 4 5
B 2 3 4 5 6
C 3 4 5 6 7
D 4 5 6 7 8
E 5 6 7 8 9
F 6 7 8 9 10
G 7 8 9 10 11
A 8 9 10 11 12
A 9 10 11 12 13
D 10 11 12 13 14
D 11 12 13 14 15
E 12 13 14 15 16


Source Group
A1001 A10
B200 B200
AB2 A10
ADDE AD
FE AD

Intermediate Step
(VLookup on Source - Group)
A10 B200 A10 AD AD
A 18 21 24 27 30
B 2 3 4 5 6
C 3 4 5 6 7
D 25 28 31 34 37
E 17 19 21 23 25
F 6 7 8 9 10
(Sumifs vertically on =A, etc)


Desired result
A B C D E F
A10 42 6 8 56 38 14
AD 57 11 13 71 48 19
B200 21 3 4 28 19 7
(Sumifs horisontally & transpose)