software

I have an array of data with column headings and changeable row quot;item namesquot;
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 quot;Item
Namesquot; 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

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

UpperLowerInnerOuter
TD 013601101
TD 013732201
TD 013610201
TD 013640201
TD 013783010

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

TD 01360Upper1
TD 01373Upper2
TD 01378Upper3
TD 01360Lower1
TD 01373Lower2
TD 01361Lower2
TD 01364Lower2
TD 01378Inner1
TD 01360Outer1
TD 01373Outer1
TD 01361Outer1
TD 01364Outer1

Thanks in advance,
RDWTry John Walkenbach's quot;reverse pivottablequot; instructions.
j-walk.com/ss/excel/usertips/tip068.htm

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

--

Dave Peterson

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

UpLoInOutPos1
TD601101
TD732201
TD610201
TD640201
TD783010
Pur1

set1Pur2Pos2Qty
1TD60Up1
2TD60Lo1
3TD60In0
4TD60Out1
5TD73Up2
6TD73Lo2
7TD73In0
8TD73Out1
9TD61Up0
10TD61Lo2
11TD61In0
12TD61Out1
13TD64Up0
14TD64Lo2
15TD64In0
16TD64Out1
17TD78Up3
18TD78Lo0
19TD78In1
20TD78Out0

Select your numerical data and name it array1
Use Insert gt; Name gt; Define or fill in name box
SelectPos1, Pur1, set1 and its data and
Insert gt; Name gt; Create gt; 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.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 quot;$A$2:$A$6quot;) to suit to your data.

=IF(ROW()gt;4*COUNTA($A$2:$A$6),quot;deletequot;,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 quot;deletequot;.

These columns are still linked to the original matrix. To make them
independent, select and copy the entire area of the new columns, quot;Editquot; --gt;
quot;Paste Specialquot; --gt;quot;Valuesquot; --gt; quot;OKquot;.

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. Ramachandranquot;RD Wirrquot; wrote:

gt; Just to illustrate what I am talking about here, data looks like this:
gt;
gt; UpperLowerInnerOuter
gt; TD 013601101
gt; TD 013732201
gt; TD 013610201
gt; TD 013640201
gt; TD 013783010
gt;
gt; and I am trying to work out a formula that will transpose it into a filtered
gt; (gt;0) column that looks like this:
gt;
gt; TD 01360Upper1
gt; TD 01373Upper2
gt; TD 01378Upper3
gt; TD 01360Lower1
gt; TD 01373Lower2
gt; TD 01361Lower2
gt; TD 01364Lower2
gt; TD 01378Inner1
gt; TD 01360Outer1
gt; TD 01373Outer1
gt; TD 01361Outer1
gt; TD 01364Outer1
gt;
gt; Thanks in advance,
gt; RDW
gt;

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:
lt; Select Pos1, Pur1, set1 and its data and
Insert gt; Name gt; Create gt; row/column gt;
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 quot;Set1 Rquot; very much. What am I missing here?

Thanks for your help
Regards,
RDW

quot;Herbert Seidenbergquot; wrote:

gt; Or if you want to use formulas...
gt; Arrange your data like this:
gt;
gt; UpLoInOutPos1
gt; TD601101
gt; TD732201
gt; TD610201
gt; TD640201
gt; TD783010
gt; Pur1
gt;
gt; set1Pur2Pos2Qty
gt; 1TD60Up1
gt; 2TD60Lo1
gt; 3TD60In0
gt; 4TD60Out1
gt; 5TD73Up2
gt; 6TD73Lo2
gt; 7TD73In0
gt; 8TD73Out1
gt; 9TD61Up0
gt; 10TD61Lo2
gt; 11TD61In0
gt; 12TD61Out1
gt; 13TD64Up0
gt; 14TD64Lo2
gt; 15TD64In0
gt; 16TD64Out1
gt; 17TD78Up3
gt; 18TD78Lo0
gt; 19TD78In1
gt; 20TD78Out0
gt;
gt; Select your numerical data and name it array1
gt; Use Insert gt; Name gt; Define or fill in name box
gt; SelectPos1, Pur1, set1 and its data and
gt; Insert gt; Name gt; Create gt; row/column
gt; Define the following names
gt; set_c Refers to
gt; =COLUMN(INDEX(R1,1):INDEX(R1,COLUMNS(array1)))
gt; set_r Refers to
gt; =ROW(INDEX(C1,1):INDEX(C1,ROWS(array1)))
gt; array_n Refers to
gt; =(set_r-1)*MAX(set_c) set_c
gt; The formula for Pur2, Pos2 and Qty are respectively
gt; =INDEX(Pur1,SUMPRODUCT((array_n=set1 R)*set_r))
gt; =INDEX(Pos1,SUMPRODUCT((array_n=set1 R)*set_c))
gt; =SUMPRODUCT((array_n=set1 R)*array1)
gt; To remove the zeros, sort descending by Qty and delete them.
gt; Start with R1C1 Reference Style, then revert to A1.
gt;
gt;

Pos1, Pur1 are labels for your labels.
In my example, select Up, Lo, In, Out and Pos1 and
Insert gt; Name gt; Create gt; Right Column and for Pur1
Insert gt; Name gt; Create gt; 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 gt; Name gt; Create gt; Top Row

All the formulas are written in R1C1 style, so first go to
Tools gt; Option gt; 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.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 quot;quot;. The COUNTA function finds all those quot;quot; 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,lt;gt;quot;quot;) or
COUNTIF($A$2:$A$6,quot;lt;gt;quot;quot;quot;) 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 quot;quot;?
Regards,
RDW

quot;B. R.Ramachandranquot; wrote:

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

Hi Herbert,

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

Thanks very much,
RDW

quot;Herbert Seidenbergquot; wrote:

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

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 quot;quot;. Do you have a workaround for this?
Thanks and Regards,
RDW

quot;Herbert Seidenbergquot; wrote:

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