Can this be done

  • Thread starter Thread starter quizzer
  • Start date Start date
Q

quizzer

:confused: I have an array that I wish to interogate to populate some
cells, I was just wondering if this is possible. In the examples below,
the first one shows that there are 2 in the A row, 1 in the -5 row, 0 in
the -20 row, 1 in the -24 row and 2 in the -30 row.

Basically what I am trying to do is search the array and return the
results in the following order, A -5 -20 -24 -30

I hope this makes sense to someone as I am struggling to explain.

Array
*1 2 3 4 5*
A [/B] 1 1 0 0 0
*-5 * 1 0 0 0 0
*-20 * 0 0 0 0 0
*-24 * 1 0 0 0 0
*-30 * 1 1 0 0 0

RESULT
A A -5 -24 -30 -30

*1 2 3 4 5
A * 1 0 0 0 0
*-5 * 1 0 0 0 0
*-20 * 1 1 0 0 0
*-24 * 0 0 0 0 0
*-30 * 1 0 0 0 0

Result
A -5 -20 -20 -30 X
 
If you data is no more than your examples and if I understand your
requirements correctly try:

=REPT(A1&" ",SUM(B1:F1))&REPT(A2&" ",SUM(B2:F2))&REPT(A3&"
",SUM(B3:F3))&REPT(A4&" ",SUM(B4:F4))&REPT(A5&" ",SUM(B5:F5))


--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
Back
Top