Forum Discussion
amberkm17
Jan 21, 2025Copper Contributor
Formulas Needed to Manipulate Table Data
I have a data table that contains four pieces of relevant data, the part number and three cost values:
I have a second table this data gets pulled into. Here's the end goal:
So, the part number is repeated in Col A. The cost is in Col B and an external value is brought into Col C from another table (it's always the same values). What formulas do I use to make this happen?
- m_tarlerBronze Contributor
So here is a stab at what you want. I'm sure others have other options that may be more elegant. I would also recommend power pivot as I believe that could have a nice solution too. Here is the formula in which I stack 3 different ways:
=HSTACK( TOCOL(CHOOSECOLS(t_Costs[V P/N],SEQUENCE(,7,1,0))), TOCOL(MMULT({1,0,0;0,1,0;0,1,0;0,0,1;0,0,1;0,0,1;0,0,1},TRANSPOSE(DROP(t_Costs,,1))),,1), TOCOL(CHOOSECOLS(t_pwIDs,SEQUENCE(,7,1,0)),,1) )
line 2 will take that first column of part numbers and repeat them horizontally and then make them into a single column;
line 3 uses matrix multiplication to create a grid with values from each row repeating 1x, 2x, 4x in corresponding columns and then TOCOL (by cols instead of rows)
line 4 is like line 2 but for the list of warehouse ID# and doing the TOCOL by cols instead of rows
- JKPieterseSilver Contributor
Seeing that you have not had a reply in days, I suggest that you try to add an example workbook to your question.