Rearranging and Indexing a column array

[Guest]

I have an array of 100 columns X 15 rows A1:CV15

Each column contains the words of a single line from a document up to a
maximum of 15.

Some of 15 end cells are blanks since not all lines are equal in length.

It is required to index (Book Index) all these words into a single column
with the line numbers from which it came into a column alongside. That is we
need to know the line number of each word in the original document.

The tricky bit here is how to index the line numbers alongside the words
before
using VBA code to move sequentially all the columns into one one.

 

[Guest]

yoou really dont need to use VBA
insert two new columns in A and B
in A1 enter
=(INT((ROW()-1)/15)+1)
in B1 enter
=index(C1:CX15,MOD(ROW()-1,15)+1,INT((ROW()-1)/15)+3)
Copy A1:B1
to A1:B1500
Select A1:B1500

copy and paste special values

 

[Guest]

This formula did'nt work out for me.

I will explain with a smaller array.

Take a (3 columns X 4 rows) array such as A1:C4
with the following data
A E H
B F I
C G J
D K
The first column ABCD is the first line and all cells in it get the index=1
The second column EFG blank gets index=2 etc.
What is needed is column with this result A 1
B 1
C 1
D 1
E 2
F 2
G 2
H 3
I 3
J 3
K 3

 

[Guest]

MIA CULPA

in A1
=index($C$1:$C$X15,MOD(ROW()-1,15)+1,INT((ROW()-1)/15)+1)
(I forgot to make the reference be absolute)

in B1
enter
=(INT((ROW()-1)/15)+1)
copy A1:B1
Select A1:B1500
paste
copy A1:B1500
select A1 and paste special values

With A1B1500 Selected
data-filter-autofilter
in column B Select 0
Select all the rows with Zeros and edit delete rows.

caution in the first 15 rows you might want to delete the cells separately
so that you do not delete any of your original data
and if you have "0"s anywhere in your word list it needs to be watched for
so that it doesn't get deleted.

 

[Guest]

Thanks bj. It works well. I must say I have never used the filter function
before.It
is so useful.