+ Reply to Thread
Results 1 to 14 of 14

Matrix to single column

  1. #1
    RD Wirr
    Guest

    Matrix to single column

    I have an array of data with column headings and changeable row "item names"
    in the left most column. The intersecting matrix of data contains quantity
    values at the intersection cells of column and row headings. I need to
    automatically reorganize the data as a list in a column with the row "Item
    Names" at the left but and repeated as necessary but instead of the
    additional columns of quantity values, I need to have this data listed in a
    single vertical column along with an additional column with the original
    column header that corresponds to the quantity value. I think there must be
    some kind of lookup function to do this but I can't find the right way to do
    it. Anyone have any ideas about this?
    Thanks,
    RDW

  2. #2
    RD Wirr
    Guest

    RE: Matrix to single column

    Just to illustrate what I am talking about here, data looks like this:

    Upper Lower Inner Outer
    TD 01360 1 1 0 1
    TD 01373 2 2 0 1
    TD 01361 0 2 0 1
    TD 01364 0 2 0 1
    TD 01378 3 0 1 0

    and I am trying to work out a formula that will transpose it into a filtered
    (>0) column that looks like this:

    TD 01360 Upper 1
    TD 01373 Upper 2
    TD 01378 Upper 3
    TD 01360 Lower 1
    TD 01373 Lower 2
    TD 01361 Lower 2
    TD 01364 Lower 2
    TD 01378 Inner 1
    TD 01360 Outer 1
    TD 01373 Outer 1
    TD 01361 Outer 1
    TD 01364 Outer 1

    Thanks in advance,
    RDW


  3. #3
    Dave Peterson
    Guest

    Re: Matrix to single column

    Try John Walkenbach's "reverse pivottable" instructions.
    http://j-walk.com/ss/excel/usertips/tip068.htm

    RD Wirr wrote:
    >
    > Just to illustrate what I am talking about here, data looks like this:
    >
    > Upper Lower Inner Outer
    > TD 01360 1 1 0 1
    > TD 01373 2 2 0 1
    > TD 01361 0 2 0 1
    > TD 01364 0 2 0 1
    > TD 01378 3 0 1 0
    >
    > and I am trying to work out a formula that will transpose it into a filtered
    > (>0) column that looks like this:
    >
    > TD 01360 Upper 1
    > TD 01373 Upper 2
    > TD 01378 Upper 3
    > TD 01360 Lower 1
    > TD 01373 Lower 2
    > TD 01361 Lower 2
    > TD 01364 Lower 2
    > TD 01378 Inner 1
    > TD 01360 Outer 1
    > TD 01373 Outer 1
    > TD 01361 Outer 1
    > TD 01364 Outer 1
    >
    > Thanks in advance,
    > RDW


    --

    Dave Peterson

  4. #4
    Herbert Seidenberg
    Guest

    Re: Matrix to single column

    Or if you want to use formulas...
    Arrange your data like this:

    Up Lo In Out Pos1
    TD60 1 1 0 1
    TD73 2 2 0 1
    TD61 0 2 0 1
    TD64 0 2 0 1
    TD78 3 0 1 0
    Pur1

    set1 Pur2 Pos2 Qty
    1 TD60 Up 1
    2 TD60 Lo 1
    3 TD60 In 0
    4 TD60 Out 1
    5 TD73 Up 2
    6 TD73 Lo 2
    7 TD73 In 0
    8 TD73 Out 1
    9 TD61 Up 0
    10 TD61 Lo 2
    11 TD61 In 0
    12 TD61 Out 1
    13 TD64 Up 0
    14 TD64 Lo 2
    15 TD64 In 0
    16 TD64 Out 1
    17 TD78 Up 3
    18 TD78 Lo 0
    19 TD78 In 1
    20 TD78 Out 0

    Select your numerical data and name it array1
    Use Insert > Name > Define or fill in name box
    Select Pos1, Pur1, set1 and its data and
    Insert > Name > Create > row/column
    Define the following names
    set_c Refers to
    =COLUMN(INDEX(R1,1):INDEX(R1,COLUMNS(array1)))
    set_r Refers to
    =ROW(INDEX(C1,1):INDEX(C1,ROWS(array1)))
    array_n Refers to
    =(set_r-1)*MAX(set_c)+set_c
    The formula for Pur2, Pos2 and Qty are respectively
    =INDEX(Pur1,SUMPRODUCT((array_n=set1 R)*set_r))
    =INDEX(Pos1,SUMPRODUCT((array_n=set1 R)*set_c))
    =SUMPRODUCT((array_n=set1 R)*array1)
    To remove the zeros, sort descending by Qty and delete them.
    Start with R1C1 Reference Style, then revert to A1.


  5. #5
    B. R.Ramachandran
    Guest

    RE: Matrix to single column

    Hi,

    Another approach using formulas:

    Let's us suppose that your matrix is in columns A thru E (for your sample
    data, it is A1:E6). Create 4 new columns (say, G, H, I, and J) with the
    following formulas in Row 1 (i.e., G1, ...J1); of course, modify the column A
    range in the formulas (here showing as "$A$2:$A$6") to suit to your data.

    =IF(ROW()>4*COUNTA($A$2:$A$6),"delete",OFFSET($A$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),0))

    =OFFSET($B$1,0,QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))

    =OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))

    =ROW()+IF(OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))=0,100000,0)

    Autofill the formulas down the rows generously. Delete the rows at the
    bottom where column G shows "delete".

    These columns are still linked to the original matrix. To make them
    independent, select and copy the entire area of the new columns, "Edit" -->
    "Paste Special" -->"Values" --> "OK".

    Sort the new columns by Column J ascending, and delete the rows at the
    bottom where Column J shows numbers greater than 100000).

    Delete the last column (Column J)

    Regards,
    B. R. Ramachandran


    "RD Wirr" wrote:

    > Just to illustrate what I am talking about here, data looks like this:
    >
    > Upper Lower Inner Outer
    > TD 01360 1 1 0 1
    > TD 01373 2 2 0 1
    > TD 01361 0 2 0 1
    > TD 01364 0 2 0 1
    > TD 01378 3 0 1 0
    >
    > and I am trying to work out a formula that will transpose it into a filtered
    > (>0) column that looks like this:
    >
    > TD 01360 Upper 1
    > TD 01373 Upper 2
    > TD 01378 Upper 3
    > TD 01360 Lower 1
    > TD 01373 Lower 2
    > TD 01361 Lower 2
    > TD 01364 Lower 2
    > TD 01378 Inner 1
    > TD 01360 Outer 1
    > TD 01373 Outer 1
    > TD 01361 Outer 1
    > TD 01364 Outer 1
    >
    > Thanks in advance,
    > RDW
    >


  6. #6
    RD Wirr
    Guest

    Re: Matrix to single column

    Hello Herbert,

    I can't pretend to know all that is going on with your formulas but I am
    trying to get them to work. If you can bear with me a bit longer... I
    appreciate your help.
    1. I am am stuck with your instruction:
    < Select Pos1, Pur1, set1 and its data and
    Insert > Name > Create > row/column >
    Are each of these separate labels? These are new fields of data added to my
    original data set so am I supposed to populate them with something? or just
    Create the row or column as Excel prompts me to do? I am unclear about this.
    2. The formulas for Pur2 and Pos2 contain (array_n=set1 R) and Excel doesn't
    seem to like the part "Set1 R" very much. What am I missing here?

    Thanks for your help
    Regards,
    RDW

    "Herbert Seidenberg" wrote:

    > Or if you want to use formulas...
    > Arrange your data like this:
    >
    > Up Lo In Out Pos1
    > TD60 1 1 0 1
    > TD73 2 2 0 1
    > TD61 0 2 0 1
    > TD64 0 2 0 1
    > TD78 3 0 1 0
    > Pur1
    >
    > set1 Pur2 Pos2 Qty
    > 1 TD60 Up 1
    > 2 TD60 Lo 1
    > 3 TD60 In 0
    > 4 TD60 Out 1
    > 5 TD73 Up 2
    > 6 TD73 Lo 2
    > 7 TD73 In 0
    > 8 TD73 Out 1
    > 9 TD61 Up 0
    > 10 TD61 Lo 2
    > 11 TD61 In 0
    > 12 TD61 Out 1
    > 13 TD64 Up 0
    > 14 TD64 Lo 2
    > 15 TD64 In 0
    > 16 TD64 Out 1
    > 17 TD78 Up 3
    > 18 TD78 Lo 0
    > 19 TD78 In 1
    > 20 TD78 Out 0
    >
    > Select your numerical data and name it array1
    > Use Insert > Name > Define or fill in name box
    > Select Pos1, Pur1, set1 and its data and
    > Insert > Name > Create > row/column
    > Define the following names
    > set_c Refers to
    > =COLUMN(INDEX(R1,1):INDEX(R1,COLUMNS(array1)))
    > set_r Refers to
    > =ROW(INDEX(C1,1):INDEX(C1,ROWS(array1)))
    > array_n Refers to
    > =(set_r-1)*MAX(set_c)+set_c
    > The formula for Pur2, Pos2 and Qty are respectively
    > =INDEX(Pur1,SUMPRODUCT((array_n=set1 R)*set_r))
    > =INDEX(Pos1,SUMPRODUCT((array_n=set1 R)*set_c))
    > =SUMPRODUCT((array_n=set1 R)*array1)
    > To remove the zeros, sort descending by Qty and delete them.
    > Start with R1C1 Reference Style, then revert to A1.
    >
    >


  7. #7
    Herbert Seidenberg
    Guest

    Re: Matrix to single column

    Pos1, Pur1 are labels for your labels.
    In my example, select Up, Lo, In, Out and Pos1 and
    Insert > Name > Create > Right Column and for Pur1
    Insert > Name > Create > Bottom Row
    Set1 is a new series of numbers from 1 to the number
    of cells in array1 ( 4*5=20). Select the label and the series and
    Insert > Name > Create > Top Row

    All the formulas are written in R1C1 style, so first go to
    Tools > Option > General and check R1C1 Reference style.
    After everything is working, you can uncheck R1C1 and
    everything will be translated to the A1 style you are used to.


  8. #8
    RD Wirr
    Guest

    RE: Matrix to single column

    Hello B.R.
    Thanks for the suggestion. This works well but for one problem. My Matrix is
    a big table of lookups and references to other cells. Most of them are
    calculating to a value of "". The COUNTA function finds all those "" but
    still non-blank cells and I get the full listing of cells with empty data
    sets. I have been searching for a way to COUNTIF($A$2:$A$6,<>"") or
    COUNTIF($A$2:$A$6,"<>""") but I have had no luck with this. Do you know how
    to count cells that are and actaul text value (i.e. TD 01360) but not cells
    that have a formula equaling ""?
    Regards,
    RDW

    "B. R.Ramachandran" wrote:

    > Hi,
    >
    > Another approach using formulas:
    >
    > Let's us suppose that your matrix is in columns A thru E (for your sample
    > data, it is A1:E6). Create 4 new columns (say, G, H, I, and J) with the
    > following formulas in Row 1 (i.e., G1, ...J1); of course, modify the column A
    > range in the formulas (here showing as "$A$2:$A$6") to suit to your data.
    >
    > =IF(ROW()>4*COUNTA($A$2:$A$6),"delete",OFFSET($A$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),0))
    >
    > =OFFSET($B$1,0,QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))
    >
    > =OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))
    >
    > =ROW()+IF(OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))=0,100000,0)
    >
    > Autofill the formulas down the rows generously. Delete the rows at the
    > bottom where column G shows "delete".
    >
    > These columns are still linked to the original matrix. To make them
    > independent, select and copy the entire area of the new columns, "Edit" -->
    > "Paste Special" -->"Values" --> "OK".
    >
    > Sort the new columns by Column J ascending, and delete the rows at the
    > bottom where Column J shows numbers greater than 100000).
    >
    > Delete the last column (Column J)
    >
    > Regards,
    > B. R. Ramachandran
    >
    >
    > "RD Wirr" wrote:
    >
    > > Just to illustrate what I am talking about here, data looks like this:
    > >
    > > Upper Lower Inner Outer
    > > TD 01360 1 1 0 1
    > > TD 01373 2 2 0 1
    > > TD 01361 0 2 0 1
    > > TD 01364 0 2 0 1
    > > TD 01378 3 0 1 0
    > >
    > > and I am trying to work out a formula that will transpose it into a filtered
    > > (>0) column that looks like this:
    > >
    > > TD 01360 Upper 1
    > > TD 01373 Upper 2
    > > TD 01378 Upper 3
    > > TD 01360 Lower 1
    > > TD 01373 Lower 2
    > > TD 01361 Lower 2
    > > TD 01364 Lower 2
    > > TD 01378 Inner 1
    > > TD 01360 Outer 1
    > > TD 01373 Outer 1
    > > TD 01361 Outer 1
    > > TD 01364 Outer 1
    > >
    > > Thanks in advance,
    > > RDW
    > >


  9. #9
    RD Wirr
    Guest

    Re: Matrix to single column

    Hi Herbert,

    That worked. That took some very creative logic. I am really impressed.

    Thanks very much,
    RDW

    "Herbert Seidenberg" wrote:

    > Pos1, Pur1 are labels for your labels.
    > In my example, select Up, Lo, In, Out and Pos1 and
    > Insert > Name > Create > Right Column and for Pur1
    > Insert > Name > Create > Bottom Row
    > Set1 is a new series of numbers from 1 to the number
    > of cells in array1 ( 4*5=20). Select the label and the series and
    > Insert > Name > Create > Top Row
    >
    > All the formulas are written in R1C1 style, so first go to
    > Tools > Option > General and check R1C1 Reference style.
    > After everything is working, you can uncheck R1C1 and
    > everything will be translated to the A1 style you are used to.
    >
    >


  10. #10
    RD Wirr
    Guest

    Re: Matrix to single column

    Hello Herbert,

    I just noticed something about this formula that gives me some problems. I
    see that when I expand Array1 down beyond where there is valid data the qty
    formulas give a #VALUE error. My actual dataset is coming from several
    lookups and is encompassed in a range that includes a changeable number of
    rows of empty data (but there are formulas in those rows to link to possible
    data). I have tried using dynamic ranges but this also finds all the cells
    with formulas even if the value is only "". Do you have a workaround for this?
    Thanks and Regards,
    RDW

    "Herbert Seidenberg" wrote:

    > Pos1, Pur1 are labels for your labels.
    > In my example, select Up, Lo, In, Out and Pos1 and
    > Insert > Name > Create > Right Column and for Pur1
    > Insert > Name > Create > Bottom Row
    > Set1 is a new series of numbers from 1 to the number
    > of cells in array1 ( 4*5=20). Select the label and the series and
    > Insert > Name > Create > Top Row
    >
    > All the formulas are written in R1C1 style, so first go to
    > Tools > Option > General and check R1C1 Reference style.
    > After everything is working, you can uncheck R1C1 and
    > everything will be translated to the A1 style you are used to.
    >
    >


  11. #11
    B. R.Ramachandran
    Guest

    RE: Matrix to single column

    Hi RD,

    Thanks for your feedback. I believe that the following modification could
    work.
    In the cell corresponding to Row 1 of the 4th column to be created (i.e.,
    J1, in my previous reply), use the following formula

    =ROW()+IF(OR(G1="",G1="delete",I1=0),100000,0)

    Note that the formula references columns G and I (1st and the 3rd of the new
    columns). If one or more cells in Column A (and, hence, in Column G) contain
    "", the formula will create numbers greater than 100000 in the corresponding
    rows. When sorted, those rows will clump up at the bottom which are
    eventually deleted (thus, even though the COUNTA function does not ignore
    ""s, it doesn't hurt).

    Regards,
    B. R. Ramachandran





    "RD Wirr" wrote:

    > Hello B.R.
    > Thanks for the suggestion. This works well but for one problem. My Matrix is
    > a big table of lookups and references to other cells. Most of them are
    > calculating to a value of "". The COUNTA function finds all those "" but
    > still non-blank cells and I get the full listing of cells with empty data
    > sets. I have been searching for a way to COUNTIF($A$2:$A$6,<>"") or
    > COUNTIF($A$2:$A$6,"<>""") but I have had no luck with this. Do you know how
    > to count cells that are and actaul text value (i.e. TD 01360) but not cells
    > that have a formula equaling ""?
    > Regards,
    > RDW
    >
    > "B. R.Ramachandran" wrote:
    >
    > > Hi,
    > >
    > > Another approach using formulas:
    > >
    > > Let's us suppose that your matrix is in columns A thru E (for your sample
    > > data, it is A1:E6). Create 4 new columns (say, G, H, I, and J) with the
    > > following formulas in Row 1 (i.e., G1, ...J1); of course, modify the column A
    > > range in the formulas (here showing as "$A$2:$A$6") to suit to your data.
    > >
    > > =IF(ROW()>4*COUNTA($A$2:$A$6),"delete",OFFSET($A$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),0))
    > >
    > > =OFFSET($B$1,0,QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))
    > >
    > > =OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))
    > >
    > > =ROW()+IF(OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))=0,100000,0)
    > >
    > > Autofill the formulas down the rows generously. Delete the rows at the
    > > bottom where column G shows "delete".
    > >
    > > These columns are still linked to the original matrix. To make them
    > > independent, select and copy the entire area of the new columns, "Edit" -->
    > > "Paste Special" -->"Values" --> "OK".
    > >
    > > Sort the new columns by Column J ascending, and delete the rows at the
    > > bottom where Column J shows numbers greater than 100000).
    > >
    > > Delete the last column (Column J)
    > >
    > > Regards,
    > > B. R. Ramachandran
    > >
    > >
    > > "RD Wirr" wrote:
    > >
    > > > Just to illustrate what I am talking about here, data looks like this:
    > > >
    > > > Upper Lower Inner Outer
    > > > TD 01360 1 1 0 1
    > > > TD 01373 2 2 0 1
    > > > TD 01361 0 2 0 1
    > > > TD 01364 0 2 0 1
    > > > TD 01378 3 0 1 0
    > > >
    > > > and I am trying to work out a formula that will transpose it into a filtered
    > > > (>0) column that looks like this:
    > > >
    > > > TD 01360 Upper 1
    > > > TD 01373 Upper 2
    > > > TD 01378 Upper 3
    > > > TD 01360 Lower 1
    > > > TD 01373 Lower 2
    > > > TD 01361 Lower 2
    > > > TD 01364 Lower 2
    > > > TD 01378 Inner 1
    > > > TD 01360 Outer 1
    > > > TD 01373 Outer 1
    > > > TD 01361 Outer 1
    > > > TD 01364 Outer 1
    > > >
    > > > Thanks in advance,
    > > > RDW
    > > >


  12. #12
    RD Wirr
    Guest

    RE: Matrix to single column

    Thanks B.R.
    That works. It makes a pretty big data set (I have around 1000 rows x 7
    columns) but it is managable. Actually I don't copy/paste/sort and delete to
    clean the data. I use a VLookup to extract the useful data on the fly. The
    original data changes constantly and some other people have to use this sheet
    so I don't want them to have to do the manual procedure.
    Thanks again and Regards,
    RDW


    "B. R.Ramachandran" wrote:

    > Hi RD,
    >
    > Thanks for your feedback. I believe that the following modification could
    > work.
    > In the cell corresponding to Row 1 of the 4th column to be created (i.e.,
    > J1, in my previous reply), use the following formula
    >
    > =ROW()+IF(OR(G1="",G1="delete",I1=0),100000,0)
    >
    > Note that the formula references columns G and I (1st and the 3rd of the new
    > columns). If one or more cells in Column A (and, hence, in Column G) contain
    > "", the formula will create numbers greater than 100000 in the corresponding
    > rows. When sorted, those rows will clump up at the bottom which are
    > eventually deleted (thus, even though the COUNTA function does not ignore
    > ""s, it doesn't hurt).
    >
    > Regards,
    > B. R. Ramachandran
    >
    >
    >
    >
    >
    > "RD Wirr" wrote:
    >
    > > Hello B.R.
    > > Thanks for the suggestion. This works well but for one problem. My Matrix is
    > > a big table of lookups and references to other cells. Most of them are
    > > calculating to a value of "". The COUNTA function finds all those "" but
    > > still non-blank cells and I get the full listing of cells with empty data
    > > sets. I have been searching for a way to COUNTIF($A$2:$A$6,<>"") or
    > > COUNTIF($A$2:$A$6,"<>""") but I have had no luck with this. Do you know how
    > > to count cells that are and actaul text value (i.e. TD 01360) but not cells
    > > that have a formula equaling ""?
    > > Regards,
    > > RDW
    > >
    > > "B. R.Ramachandran" wrote:
    > >
    > > > Hi,
    > > >
    > > > Another approach using formulas:
    > > >
    > > > Let's us suppose that your matrix is in columns A thru E (for your sample
    > > > data, it is A1:E6). Create 4 new columns (say, G, H, I, and J) with the
    > > > following formulas in Row 1 (i.e., G1, ...J1); of course, modify the column A
    > > > range in the formulas (here showing as "$A$2:$A$6") to suit to your data.
    > > >
    > > > =IF(ROW()>4*COUNTA($A$2:$A$6),"delete",OFFSET($A$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),0))
    > > >
    > > > =OFFSET($B$1,0,QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))
    > > >
    > > > =OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))
    > > >
    > > > =ROW()+IF(OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))=0,100000,0)
    > > >
    > > > Autofill the formulas down the rows generously. Delete the rows at the
    > > > bottom where column G shows "delete".
    > > >
    > > > These columns are still linked to the original matrix. To make them
    > > > independent, select and copy the entire area of the new columns, "Edit" -->
    > > > "Paste Special" -->"Values" --> "OK".
    > > >
    > > > Sort the new columns by Column J ascending, and delete the rows at the
    > > > bottom where Column J shows numbers greater than 100000).
    > > >
    > > > Delete the last column (Column J)
    > > >
    > > > Regards,
    > > > B. R. Ramachandran
    > > >
    > > >
    > > > "RD Wirr" wrote:
    > > >
    > > > > Just to illustrate what I am talking about here, data looks like this:
    > > > >
    > > > > Upper Lower Inner Outer
    > > > > TD 01360 1 1 0 1
    > > > > TD 01373 2 2 0 1
    > > > > TD 01361 0 2 0 1
    > > > > TD 01364 0 2 0 1
    > > > > TD 01378 3 0 1 0
    > > > >
    > > > > and I am trying to work out a formula that will transpose it into a filtered
    > > > > (>0) column that looks like this:
    > > > >
    > > > > TD 01360 Upper 1
    > > > > TD 01373 Upper 2
    > > > > TD 01378 Upper 3
    > > > > TD 01360 Lower 1
    > > > > TD 01373 Lower 2
    > > > > TD 01361 Lower 2
    > > > > TD 01364 Lower 2
    > > > > TD 01378 Inner 1
    > > > > TD 01360 Outer 1
    > > > > TD 01373 Outer 1
    > > > > TD 01361 Outer 1
    > > > > TD 01364 Outer 1
    > > > >
    > > > > Thanks in advance,
    > > > > RDW
    > > > >


  13. #13
    Herbert Seidenberg
    Guest

    Re: Matrix to single column

    For Qty use this formula instead
    =INDEX(array1,SUMPRODUCT((array_n=set1 R)*set_r),
    SUMPRODUCT((array_n=set1 R)*set_c))


  14. #14
    RD Wirr
    Guest

    Re: Matrix to single column

    You are a wizard Herbert. Thanks very much that does it. I'd like to find out
    where a person learns about these things. Especially "array_n=set1 R".
    Thanks again,
    RDW

    "Herbert Seidenberg" wrote:

    > For Qty use this formula instead
    > =INDEX(array1,SUMPRODUCT((array_n=set1 R)*set_r),
    > SUMPRODUCT((array_n=set1 R)*set_c))
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1