Formula Question (referencing data with OFFSET)

[gss01]

Hi,
Please refer to attached pic. I have data in Col C which contains a name that I want to copy and paste alongside the date which is associated with this name. I have manually copied and pasted ‘Name 1’ in col B where it needs to go as an example.

Is there any formula which can do this quickly? My file contains 47k lines of data and has also gaps as per the red cells highlighted and therefore isn’t very clean.

Many Thanks

(edited by ajlittoz to activate the picture)

[keme]

• Do you want to paste a formula all the way down column B, fetching the preceding name from column C when “this row” has a date in col C?
  No preexisting data that needs to be preserved in column B
• Are all info fields strictly text? No number, date or formula result?
• Is the “3 x info , 5 x date” pattern consistent within each block of data?

[gss01]

hi,
-yes a formula all the way down col b which copies the name of the block next to it’s related dates.
-col b is a new column, there is no prexisting data it in.
-yes the info fields are text only
-yes each block contains 1x name, 3x info, 5x date

[anon73440385]

I want to copy and paste

No formula can perform a copy. Formulas calculate values based on references to other cells, but don’t change the content of the cell containing the formula.

[keme]

Try this formula in B2:
=IF(ISNUMBER(C2);IF(ISNUMBER(C1);B1;OFFSET(B2;-5;1));"")

Copy down.

[gss01]

Worked perfectly, many thanks keme