Create a List Output from a Horizontal Array Input

[Rob]

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

 

[T. Valko]

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$2>0,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))

 

[Rob]

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

 

[Rob]

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

Cheers,
Rob

 

[T. Valko]

You're welcome. Thanks for the feedback!