indirect accessing to cells

[Dudi]

Hello,

I use data of few thousands records (cells) which are all arranged in
sequential order at single column. I need to extract specific cells'
content, and I have no idea what function I can use for.

For example, assuming I use a table (single column) of 100 records and I
need to extract every 5th cell content to rebuild a new column.

Any suggestions ?

Tnx
Dudi

 

[Gord Dibben]

Enter in cell 1 of a column assuming data is in column A

=OFFSET($A$1,5*ROW()-5,0)

Copy down as far as you wish.


Gord Dibben MS Excel MVP

 

[AltaEgo]

Without interfering with existing data:

Assuming you wish to see a list of every nth row in column A, enter the
following formula in C1 and the number of rows in column B2.
Enter this formula and drag down:

=INDIRECT("A" & $B$1*ROW()+0)


The '+0' looks to be redundant. Correct unless you need to start at an
alternate location. Depending where you put the formula and where you need
to start you every nth list, change this number (positive or negative) to
adjust.

Change your $B$1 figure, Copy/paste special/values to a new location .

 

[Dudi]

Hey Gordon,

I believe this way will work for me but I don't understand yet the
'5*ROW()-5' syntax.
I understand that 5*ROW() means current row number times 5. But what's the
meaning of '-5' ? I tried to build a simple table at B3:B47. I realize that
to get the right every 5th cell in this column, I have to '-40'. Can you
explain me this 'offset' ?

Tnx
Dudi

 

[Gord Dibben]

To start the list in B3 =OFFSET($A$1,5*ROW()-15,0)

will get A1, A6, A11 etc.

The *5 is current row number(3) *5 which would mean row 15 but you want to
pull from every 5th cell in column A starting at row 1 so you must go back
up 15 rows to start the list.

To start the list in B4 =OFFSET($A$1,5*ROW()-20,0)

I don't know if this explains it,


Gord

 

[Gord Dibben]

The *5 is current row number(3) *5 which would mean row 15

Actually it would mean row 16 because it is offset 15 from A1


Gord

 

[Shane Devenshire]

Hi,

If the data is numeric Choose the Tools, Data Analysis, Sampling, select
your range for the Input Range, click Periodic and enter 5 as the Period,
click Output Range, click the box next to it and indicate where you want the
results. Click OK.

The is an ATP tool so you need to attach it if it isn't by choosing Data,
Add-ins, and checking the Analysis ToolPak.

 

[Shane Devenshire]

Hi,

One formula approach which assumes that the first item you want to return is
the 5th item, and assuming you have titles one row 1 so the first entry is in
A2:

=OFFSET($A$1,5*ROW(A1),)

 

[Dudi]

Well done Gordon. Appreciate your professional response.

Dudi

 

[Gord Dibben]

Thanks

Just an idea for you.

In B3 enter =A1

In B4 enter Shane's formula of =OFFSET($A$1,5*ROW(A1),)

Copy down.

Much easier than trying to figure out the multiplication factor I posted.


Gord

 

[Dudi]

Hey Gord,

This is nice

Take care and enjoy the coming weekend
Dudi

 

[Dudi]

Hey Steve,

It did not work for me so I prefer Gord's way.

Many thanks.
Enjoy the coming weekend
Dudi

 

[Dudi]

Hey Shane,

Indeed this is great toolpak! It works very well although I have to add an
offset in 'input range field, otherwise this analysis starts from incorrect
cell.

Cheers
Dudi

 

[Dudi]

Yes you are right it works great.

Tnx. Enjoy the coming weekend
Dudi