Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating a matrix from columns
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? |
#2
|
|||
|
|||
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" wrote in message ... 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? |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
print excel columns on less pages | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) | |||
Creating a chart with values from two columns | Charts and Charting in Excel |