Extract from 3 columns [SOLVED]

[luis_marques]

Hi,


My data set looks like this:


A B C

1 Joe New York

34 Mike Rio

453 James London


I need to mount a column like this in D:


D

1

JOE

NEW YORK

""

34

Mike

Rio

""

453

James

London


ps: "" is blank


Thanks in advance.

 

[jeffreyweir]

Okay, in D1 put an apostrophe i.e. ' and copy that apostrophe down all the way to the bottom of the data. (e.g. if your data goes to $C$10000 then copy that apostrophe down to $D$10000)


In E1 put this:

=OFFSET($A$1,MOD(ROW(),-4)+3,INT((ROW()-1)/4),1,1)&""

Copy that formula down, then copy and paste values to turn it from a formula into values.

 

[NARAYANK991]

Hi Luis ,


Can you try this ?


=IF(MOD(ROW(),4)=0,"",OFFSET($A$1,((ROW()-1)/4),MOD(ROW()-1,4)))


Enter this in D1 , and copy down.


Narayan

 

[jeffreyweir]

Yep, that IF(MOD(ROW(),4)=0,"" bit makes your formula an easier approach, Narayan. Why didn't I think of that?

 

[luis_marques]

Thanks Jeffrey and Narayan. Thats solved my problem. Very clever.