[jakester]

Ok - I have a spreadsheet that is exported from an application I use at work. It formats the data in a manner that makes it really difficult to manipulate but I think I've worked out a solution that gets me what I need; only it is very tedious.

The data starts in row 16 of the sheet. There is a name in row 16 and in every 10th row after that. Then there is a correpsponding value for each name that is 6 rows beneath the name and in a different column (because the application merges cells, etc. the values aren't on the same row as the name and trying to unmerge things creates more problems).

So, name1 begins in cell A16 and its corresponding value is in cell D22. The next name value is in A26 and its corresponding value is in cell D32, etc. etc. What I would like to do is to put the corresponding value on the same row as the name in column J. Now I have 1600 rows of data so going one by one through each would be exceptionally tedious.

What I would like to do is beginning in cell J16, return the corresponding name value for name1 there and go every 10th row until the end of the data set. But I wouldn't want to do that manually using an offset formula because I'd have to do that 1600 times.

[joypulv]

You can't just make a copy, set it to no formulas, and delete the top 6 cells of column J?
Then if column A has non values in the cells you want, you can write a formula to ignore cells with values, and write a macro to send only those rows to a new worksheet?

[jakester]

You can't just make a copy, set it to no formulas, and delete the top 6 cells of column J?
Then if column A has non values in the cells you want, you can write a formula to ignore cells with values, and write a macro to send only those rows to a new worksheet?

Joy - thanks for taking a stab at it... there is no existing formula in J... I want to put a formula in J that refers to the corresponding name value which begins in D22 and repeats every 10th row after that.

[joypulv]

I meant:
Make a copy (set to no formulas if need be) and delete cells J1-6.
Now everything you want is one the corresponding row (or am I missing something more complicated).
Write a macro that creates a new sheet with only every 10th row.
Then enter ordinary formulas in col J.

[jakester]

I meant:
make a copy (set to no formulas if need be) and delete cells J1-6.
now everything you want is one the corresponding row (or am I missing something more complicated).
write a macro that creates a new sheet with only every 10th row.
then enter ordinary formulas in col J.

Yeah, it is just a tad more complicated than what you are suggesting. Because of the merged cells, the very first empty cell begins on each row in Column J, which is where I want to start the formula. If try to delete the first set of cells, the cells become unmerged and I lose the data. Truthfully, the way I am trying to work it is the only way I think... largely due to the merged cell issue.

[JBeaucaire]

You have a shorter example of the sheet, perhaps 50-100 rows showing BEFORE and AFTER examples?

That would make it trivial to devise a quick macro to display your results using either formulas or actually just inserting the values.