Nested IF with Vlookup Help Required?

[NKRA]

I have a Row with System Name and I have Values for each System, I want to get a result in one Col where ever the System Col have value greater than zero then bring the System name in Result Col. If it is less than Zero do nothing. I have some 34 System Columns like this.


Attach is Spreadsheet example

[VBA Noob]

Hi,

This is close

=IF(E2>0,E1&",","")&IF(F2>0,F1&",","")&IF(G2>0,G1&",","")&IF(H2>0,H1&",","")&IF(I2>0,I1&",","")&IF(J2>0,J1&",","")&IF(K2>0,K1&",","")&IF(L2>0,L1&" ","")

VBA Noob

[TomMoston]

=IF(E2>0,E1&", ","")&IF(F2>0,F1&", ","")&IF(G2>0,G1&", ","")&IF(H2>0,H1&", ","")&IF(I2>0,I1&", ","")&IF(J2>0,J1&", ","")&IF(K2>0,K1&", ","")&IF(L2>0,L1&",","")

This works but is very longwinded

Cheers

Tom

[TomMoston]

Sorry VBANoob, you beat me to it. you can delete my post now.....

Cheers

Tom

[daddylonglegs]

To eliminate the additional comma....

=SUBSTITUTE(IF(E2>0,","&E1,"")&IF(F2>0,","&F1,"")&IF(G2>0,","&G1,"")&IF(H2>0,","&H1,"")&IF(I2>0,","&I1,"")&IF(J2>0,","&J1,"")&IF(K2>0,","&K1,"")&IF(L2>0,","&L1,""),",","",1)

although for 34 values you'll have a rather long formula.

There is no native formula that concatenates arrays but if you use the Morefunc addin you can use the MCONCAT function and this formula for 34 columns

=SUBSTITUTE(MCONCAT(IF(E2:AL2>0,","&E1:AL1,"")),",","",1)

confirmed with CTRL+SHIFT+ENTER

get morefunc here

http://xcell05.free.fr/