Copying info from regularly spaced cells

Taff

Board Regular
Joined
Mar 31, 2004
Messages
140
I have info in cells a3, a19, a35, a51 etc, ie every 16th cell.

I want to copy that info into b3, b4, b5, b6 etc, therefore info running consecutively with no lines in between

Is there a quick way of doing this, eg b3=a3, b4=a3+16

there is other info in the cells/rows inbetween therefore cannot filter/delete

still a novice at this type of formula.....

Cheers
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,475
Office Version
  1. 365
Platform
  1. Windows
If it is text values in those cells then try this in B3, copied down

Excel Formula:
=INDEX(A$3:A$1000,ROWS(B$3:B3)*16-15)&""

If it is numerical values to be extracted & they need to remain numerical then either we need some way to determine where the last value is or how many values to be extracted.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
as I understand
Try
VBA Code:
Sub test()
    Dim i
    For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row Step 16
        Cells(i, 2).Resize(16) = Cells(i, 1)
    Next
End Sub
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Another method: (This assumes all 15 cells between are blank)

Copy column A to B,
Select entire B column,
Press F5,
Select "Special',
Select 'Blanks',
Press 'Ctrl' + '-'
Select 'Shift cells up'
select 'ok'
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
I think you may have missed the following. ;)
Thanks for pointing that out. I missed it completely 🤣

never know, it may help someone else who stumbles by this post in the future.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,543
Members
416,923
Latest member
jarri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top