Creating a matrix from columns

[Ernie Sersen]

I have an Excel Sheet A with 3 columns. Col A=Site; Col B=Part; Col
C=Quantity. Rows of site and parts data are added daily and may contain the
same site or same part number along with quantities for each row in Col C. I
then create Sheet B with unique sites and unique parts so that I now have a
matrix of unique sites in Col A and unique parts (transposed)in Row 1. In
the data cells of the matrix, I need to know how to reference back to the
Sheet A to summarize the quantities (Col C) of each unique part located at
each unique site (VLOOKUP? SUMIF? Some other database function?) Any idea's?

[Peo Sjoblom]

One way

=SUMPRODUCT(--('SheetA'!$A$2:$A$200="Site1"),--('SheetA'!$B2:$B200="Part1"),
'SheetA'!$C2:$C200)

adapt to fit you sheet names and site and part numbers

of course you can replace the hard coded names with the matrix values

=SUMPRODUCT(--(SheetA!$A$2:$A$200=$A2),--(SheetA!$B2:$B200=B$1),SheetA!$C2:$
C200)

and copy across and down

--

Regards,

Peo Sjoblom


"Ernie Sersen" <[email protected]> wrote in message
news:[email protected]...
> I have an Excel Sheet A with 3 columns. Col A=Site; Col B=Part; Col
> C=Quantity. Rows of site and parts data are added daily and may contain
the
> same site or same part number along with quantities for each row in Col C.
I
> then create Sheet B with unique sites and unique parts so that I now have
a
> matrix of unique sites in Col A and unique parts (transposed)in Row 1. In
> the data cells of the matrix, I need to know how to reference back to the
> Sheet A to summarize the quantities (Col C) of each unique part located at
> each unique site (VLOOKUP? SUMIF? Some other database function?) Any
idea's?

[Duke Carey]

Am I missing something? Wouldn't this just be a simple Pivot Table?

"Ernie Sersen" wrote:

> I have an Excel Sheet A with 3 columns. Col A=Site; Col B=Part; Col
> C=Quantity. Rows of site and parts data are added daily and may contain the
> same site or same part number along with quantities for each row in Col C. I
> then create Sheet B with unique sites and unique parts so that I now have a
> matrix of unique sites in Col A and unique parts (transposed)in Row 1. In
> the data cells of the matrix, I need to know how to reference back to the
> Sheet A to summarize the quantities (Col C) of each unique part located at
> each unique site (VLOOKUP? SUMIF? Some other database function?) Any idea's?