Avoid nested IF statement and bring columns together

**deanstein** · 08-22-2012, 08:39 AM

Hi everyone,

I'm having a bit of difficulty trying to summarise a table of data.

I've attached the workbook to make the problem easier to understand.

Basically I want to be able to select an option from the drop down box which populates the table in the red box. My formula's are such that where there are zeroes in the main data, the cell in the red box must be left blank and there must be no column heading. That is all working correctly. The problem is that I would then like to bring the columns altogether so that there are no blanks or zero values (like the green box in the example). I also cannot use nested IF statement because there are more than 7 columns. Please help!!

Thank you in advance.

excelexample1.xlsx

**taps** · 08-22-2012, 08:43 AM

Hi,

You are using 2010. it will support more than 7 IF. So please try again with multiple IF.

**deanstein** · 08-22-2012, 08:47 AM

Thank you for the quick reply taps. I will give it a try and then report back.

**NBVC** · 08-22-2012, 09:48 AM

A couple of other ways...

1.

Use this formula in C13:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied across 11 columns.

Then in C14:

Please Login or Register  to view this content.

confirmed with just ENTER and copied across 11 columns.

or

2.

Add helper row at C8:

Please Login or Register  to view this content.

copied across.

Then at C13:

Please Login or Register  to view this content.

copied across

and at C14, same as above:

Please Login or Register  to view this content.

copied across.

Note: the second option is more efficient, especially if your lists are long....

**deanstein** · 08-23-2012, 01:57 AM

taps: The nested IFs worked, just very inefficiently.

NBVC: Thank you for those 2 solutions. I'm just having some problems with them. The formula works for the first selection in the drop down box (John) but then brings in zeroes for other selections.