Split data into adjacent columns

**ElmerS** · 11-21-2009, 08:13 AM

Hi,
I am looking for two formulas (col. B+C) in order to split every second value from Col. A into two adjacent columns.
Empty cells, in col. A, should be ignored.
The requested result is shown in green and red.
Please - no VBA
Thanks, Elm

**DonkeyOte** · 11-21-2009, 08:29 AM

The no VBA is back...

B2:
=IF($A2="";"";IF(MOD(COUNT($A$2:$A2);2)=(2-COLUMNS($B2:B2));$A2;""))

which can be applied across entire matrix (ie Column C included)

**ElmerS** · 11-21-2009, 08:36 AM

Thanks, DonkeyOte.

You may consider the return of the "no VBA" man also in the future...

By using VBA and/or helper columns I am capable to force Excel to resolve almost everything except preparing a cup of coffee.

Elm

**ElmerS** · 11-21-2009, 11:33 AM

DonkeyOte, & "to whom it may concern",
Even if it sounds a little freaky I'll appreciate an answer if it can be accomplished the way described here:

I want to build a chart relying on columns B+C.
I know I can declare tow name ranges - one for each column - and that I can declare those name-ranges as dynamic - However , I wonder if there is a way to eliminate even the use of the two "helper-Columns" B+C by defining the two names on two Array formulas which will take place of columns B+C by simulations them without the need to present them inn the sheet.
Thanks, Elm