Create a List Output from a Horizontal Array Input
 

I'm trying to create a dynamic list that changes daily and is derived from an
Array Formula. Is there an array formula that can provide me the list below
with the input data given? The Array Input list stays the Same with the Qty
of items listed, it's the output that will change when and if the second row
of numbers changes in any way.


Input Array:

Tom1 Dick1 Harry1 Tom2 Dick2 Harry2 Tom3 Dick3 Harry3
0 3 0 8 0 0 0 0 1



Output List:
Dick1 - 3
Tom2 - 8
Harry3 - 1



I hope I provided enough info.

Thanks in Advance,
Rob

Create a List Output from a Horizontal Array Input
 

Try this...

Names in the range A1:I1
Numbers in the range A2:I2

Assuming the names are unique

Enter this formula in A5. This will return the count of numbers 0.

=COUNTIF(A2:I2,"0")

Enter this array formula** in A6 and copy down to a number of cells that is
equal to the total number of names in A1:I1.

=IF(ROWS(A$6:A6)$A$5,"",INDEX(A$1:I$1,SMALL(IF(A$ 2:I$20,COLUMN(A$1:I$1)),ROWS(A$6:A6))-COLUMN($A1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Enter this formula in B6 and copy down the same number of cells as the
formula in A6.

=IF(A6="","",SUMIF(A$1:I$1,A6,A$2:I$2))

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I'm trying to create a dynamic list that changes daily and is derived from
an
Array Formula. Is there an array formula that can provide me the list
below
with the input data given? The Array Input list stays the Same with the
Qty
of items listed, it's the output that will change when and if the second
row
of numbers changes in any way.


Input Array:

Tom1 Dick1 Harry1 Tom2 Dick2 Harry2 Tom3 Dick3 Harry3
0 3 0 8 0 0 0 0 1



Output List:
Dick1 - 3
Tom2 - 8
Harry3 - 1



I hope I provided enough info.

Thanks in Advance,
Rob

Create a List Output from a Horizontal Array Input
 

Sorry for not seeing your reply yesterday.... I became too busy fast.

Thanks for the reply! I'll see if this is good and let you know.

Thanks Again!Rob



"T. Valko" wrote:

Try this...

Names in the range A1:I1
Numbers in the range A2:I2

Assuming the names are unique

Enter this formula in A5. This will return the count of numbers 0.

=COUNTIF(A2:I2,"0")

Enter this array formula** in A6 and copy down to a number of cells that is
equal to the total number of names in A1:I1.

=IF(ROWS(A$6:A6)$A$5,"",INDEX(A$1:I$1,SMALL(IF(A$ 2:I$20,COLUMN(A$1:I$1)),ROWS(A$6:A6))-COLUMN($A1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Enter this formula in B6 and copy down the same number of cells as the
formula in A6.

=IF(A6="","",SUMIF(A$1:I$1,A6,A$2:I$2))

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I'm trying to create a dynamic list that changes daily and is derived from
an
Array Formula. Is there an array formula that can provide me the list
below
with the input data given? The Array Input list stays the Same with the
Qty
of items listed, it's the output that will change when and if the second
row
of numbers changes in any way.


Input Array:

Tom1 Dick1 Harry1 Tom2 Dick2 Harry2 Tom3 Dick3 Harry3
0 3 0 8 0 0 0 0 1



Output List:
Dick1 - 3
Tom2 - 8
Harry3 - 1



I hope I provided enough info.

Thanks in Advance,
Rob

Create a List Output from a Horizontal Array Input
 

It Works Perfectly! Better than I could have ever expected! Thank You Sooo
Much!

Cheers,
Rob



"T. Valko" wrote:

Try this...

Names in the range A1:I1
Numbers in the range A2:I2

Assuming the names are unique

Enter this formula in A5. This will return the count of numbers 0.

=COUNTIF(A2:I2,"0")

Enter this array formula** in A6 and copy down to a number of cells that is
equal to the total number of names in A1:I1.

=IF(ROWS(A$6:A6)$A$5,"",INDEX(A$1:I$1,SMALL(IF(A$ 2:I$20,COLUMN(A$1:I$1)),ROWS(A$6:A6))-COLUMN($A1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Enter this formula in B6 and copy down the same number of cells as the
formula in A6.

=IF(A6="","",SUMIF(A$1:I$1,A6,A$2:I$2))

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I'm trying to create a dynamic list that changes daily and is derived from
an
Array Formula. Is there an array formula that can provide me the list
below
with the input data given? The Array Input list stays the Same with the
Qty
of items listed, it's the output that will change when and if the second
row
of numbers changes in any way.


Input Array:

Tom1 Dick1 Harry1 Tom2 Dick2 Harry2 Tom3 Dick3 Harry3
0 3 0 8 0 0 0 0 1



Output List:
Dick1 - 3
Tom2 - 8
Harry3 - 1



I hope I provided enough info.

Thanks in Advance,
Rob

Create a List Output from a Horizontal Array Input
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
It Works Perfectly! Better than I could have ever expected! Thank You
Sooo
Much!

Cheers,
Rob



"T. Valko" wrote:

Try this...

Names in the range A1:I1
Numbers in the range A2:I2

Assuming the names are unique

Enter this formula in A5. This will return the count of numbers 0.

=COUNTIF(A2:I2,"0")

Enter this array formula** in A6 and copy down to a number of cells that
is
equal to the total number of names in A1:I1.

=IF(ROWS(A$6:A6)$A$5,"",INDEX(A$1:I$1,SMALL(IF(A$ 2:I$20,COLUMN(A$1:I$1)),ROWS(A$6:A6))-COLUMN($A1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Enter this formula in B6 and copy down the same number of cells as the
formula in A6.

=IF(A6="","",SUMIF(A$1:I$1,A6,A$2:I$2))

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I'm trying to create a dynamic list that changes daily and is derived
from
an
Array Formula. Is there an array formula that can provide me the list
below
with the input data given? The Array Input list stays the Same with
the
Qty
of items listed, it's the output that will change when and if the
second
row
of numbers changes in any way.


Input Array:

Tom1 Dick1 Harry1 Tom2 Dick2 Harry2 Tom3 Dick3 Harry3
0 3 0 8 0 0 0 0 1



Output List:
Dick1 - 3
Tom2 - 8
Harry3 - 1



I hope I provided enough info.

Thanks in Advance,
Rob