vlookup with random gaps in rows (hard to explain see attached sheet!)

Hi

I have a sheet (attached) where data is put into columns J, K, L and M. This is in sets which can vary from one to six rows and it is random as to how many rows each set will have. I need to transfer the values over to columns A, B, C and D but the problem is that A, B, C and D are fixed at six rows each in order to accommodate the maximum number of rows each set can have (I cannot change this). How do I get the data from J, K, L & M into A, B, C & D? I have tried various vlookups but it cannot account for the random placing of the gaps in the sets.

The sheet attached shows how it would look with the data transferred. Can anyone help with a suitable formula to do this please.

=IF(MAX($E$1:$E2)-LARGE($E$1:$E2,2)-CHOOSE(1+MOD(ROW()-1,6),6,1,2,3,4,5)>0,INDEX(J:J,LARGE($E$1:$E2,2)+MOD(ROW()-1,6)),"")
with a helper column

It certainly seems to work but if I add any new data to columns J,K,L,M it resets all cells in A, B, C, D,E to #NAME?

What do you mean: new?
Those tables should be in different sheets.

For example if I add another set of data in your sheet, say cell J26 - M26 it resets A, B, C, D, E to #NAME? I need to be able to add data. In the attached I have added data in cells J26:M26 and it has reset.

How about using VBA.
See attached sample.
Click on the button and it will move data as you want.
When you add data it will work as well.

it works as intended

Here's the macro.

HTML Code: 

The VBA routine is just the job so I will go with that. Thanks to all for your input it is much appreciated.

If you are satisfied with the solution, please mark this thread "Solved".
Appreciate your feedback and reputation if you so choose.

Originally Posted by tim201110

it works as intended

I was surprised this did not work for me but I have found out what the problem is! I am using Excel 2007 and the formula you used unfortunately does not work with my version. When I open your sheet it displays the values correctly but as soon as I do anything to make the formula recalculate it reports the error code and puts a prefix in front of the formula: =_xlfn.AGGREGATE(15,6,ROW($J$2:$J$10000)/ISBLANK($J$2:$J$10000),COUNTA($E$1:E1)).