Extract using formula in different rows by a constant [SOLVED]

[luis_marques]

Hi, my data set is like this:

A1

1

A

B

C

2

D

E

F

Repeatedly until 18 (the number obeys a constant difference of letters between the lines). Which formula can i use to extract just all the letters right below the number in that column A1?

 

[Hui]

Luis


A few questions

Is there always 3 rows of Text after each number or will it vary?

Do you want the Letters A, B, C etc in one cell or in 3 cells next to each other?


As a guess in B1 try:

=IF($A1<19,OFFSET($A1,COLUMNS($A:A),),"")

Copy across and down

 

[Debraj]

Hi luis..


Can you please try this..


* Select A:A.

* Apply Filter. (Ctrl + Shift + L)

* Text Filter > Equal > "*" (without quote)

for all TEXT (A,b,C..)


or


* Text Filter > Does Not Equal > "*" (without quote)

for all Numeric (1,2,5..)


Copy and paste in separate location..


Regards,

Deb

 

[NARAYANK991]

Hi ,


Yet another guess !


Suppose your data is in a column , starting from row 2 , as follows :

[pre]

1
A
B
C
2
D
E
F
3
G
H
I
4
J
K
L
5
M
N
O
6
P
Q
R
7
S
T
U
8
V
W
X
9
Y
Z
AA
10
AB
AC
AD
11
AE
AF
AG
12
AH
AI
AJ
13
AK
AL
AM
14
AN
AO
AP
15
AQ
AR
AS
16
AT
AU
AV
17
AW
AX
AY
18
AZ
BA
BB
Let us call this column of data as a named range [b]List[/b]


The following formula , entered as an array formula , using CTRL SHIFT ENTER , and copied down :


=INDEX(List,SMALL(IF(ISNUMBER(List),ROW(List)-MIN(ROW(List))+2),ROW(A1)))


will generate the following output :

A
D
G
J
M
P
S
V
Y
AB
AE
AH
AK
AN
AQ
AT
AW
AZ

[/pre]
Narayan

 

[Debraj]

with respect of Narayan's Guess..


try this VBA..

[pre]

Sub luis_marques()
Range("A:A").SpecialCells(2, 1).Offset(1).Copy Range("C2")
End Sub

[/pre]

Regards,

Deb

 

[Sajan]

Hello,

Since everyone is playing guessing games, here is one more...


Keeping to Narayan's theme...


The following formula would list all of the strings in the List. (This is based on the OP's statement to "extract just all the letters right below the number".)


=INDEX(List, SMALL(IFERROR(List+0>0,ROW(List)-MIN(ROW(List))+1), ROW(A1)))


enter with Ctrl + Shift + Enter, instead of Enter.


Copy down to additional rows.


A partial output is as follows:

[pre]

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O

[/pre]
Cheers,

Sajan.

 

[luis_marques]

Thanks very much to Hui, Deb, Sajan, but Narajan guess was what i needed. I edited this comment because i made a huge mistake in typing the formula. The formula is completely correct. Thanks Narajan.