Summarising table entries

[Guest]

Hi, I wonder if anyone can help.

I have a spreadsheet that looks like this:

A B C D E F
1 One Two Three Four Intended result
2 Fred 1 2 3 1 One, Four
3 Ben 2 1 1 1 Two, Three, Four

I am trying to work out a way to summarise the above table in the the form
of column 'F' (intended result). I.e. the resulting cell will gather and
join the headings of the columns where a '1' appears below it. I've toyed
with sumproduct but it seems to only work with numerical values in the result.

Is there a way to do this in Excel 97?

Thanks.

Guy

 

[Biff]

Hi!

Not EXACTLY what you want, but close.

In F2 enter this formula and copy across to I2:

Entered as an array with the key combo of CTRL,SHIFT,ENTER.

=IF(ISERROR(INDEX($B$1:$E$1,SMALL(IF($B2:$E2=1,COLUMN
($A:$D)),COLUMN(A:A)))),"",INDEX($B$1:$E$1,SMALL(IF
($B2:$E2=1,COLUMN($A:$D)),COLUMN(A:A))))

This will extract the headings to separate cells.

There is an add-in with a specialized function that does
exactly what you want but I'm not familiar with it.

Another way to get close to what you want is to enter the
formula in say G2 and copy across to J2. Then, in F2 enter
this formula:

=TRIM(G2&" "&H2&" "&I2&" "&J2)

This will extract the headings to separate cells G2:J2
then the formula in F2 will concatenate those cells into a
single cell less the comma.

Biff

 

[Guest]

Thanks Biff

My actual sheets have a lot more columns (and may be added to or reduced
later) and ideally I want to run the summary for each of the values in the
cells (i.e. 1, 2, 3) in a separate end column.

I think having to create a new column for each of the headed columns, and do
it 3 times, will mean I will run past the IV column limit.

Do you know what that add-in function is called? I've had a look at
'Conditional sum' and 'Lookup' but can't see what I'm after.

Thanks for your help.

Guy