Forum Discussion

amberkm17's avatar
amberkm17
Copper Contributor
Jan 21, 2025

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_tarler's avatar
    m_tarler
    Bronze 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

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor

    Seeing that you have not had a reply in days, I suggest that you try to add an example workbook to your question.

Resources