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? |
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? |
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? |
All times are GMT +1. The time now is 12:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com