retrieve value from column

[ashish mehra]

Hi,

I would like to fetch the values as sample is shown in the attached file.

Regards,
AM

 

[Luke M]

Put this formula in a cell, copy down as needed.
=OFFSET($A$3,INT((ROWS($A$1:$A1)-1)/16),MOD(INT((ROWS($A$1:$A1)-1)/4),4)+MOD(ROWS($A$1:$A1)-1,4)*5)

 

[ashish mehra]

Hi Luke,

One small updation is require.

After every block of four numbers a separator is needed.

After 432, 127 & so on.

Regards,
AM

 

[Luke M]

Ah, sorry about that. Try this one.
=IF(MOD(ROWS($A$1:$A1),5)=0,"",OFFSET($A$3,INT((ROWS($A$1:$A1)-1)/20),MOD(INT((ROWS($A$1:$A1)-1)/5),5)+MOD(ROWS($A$1:$A1)-1,5)*5))

 

[ashish mehra]

No need to say sorry sir!!!!

After 441 the result is 0,0,0,0, . Instead the output should continue giving 410, 139, 0, 421 blank cell 290, 588, 919, 623 & continue like this till there are number of data rows.

I think we should get the output in another sheet that will avoid any chance of error.

I hope I am not asking too much.

Regards,
AM

 

[Luke M]

Ah, tricky little 4! It got away from me. New formula on same sheet:
=IF(MOD(ROWS($A$1:$A1),5)=0,"",OFFSET($A$3,INT((ROWS($A$1:$A1)-1)/20),MOD(INT((ROWS($A$1:$A1)-1)/5),4)+MOD(ROWS($A$1:$A1)-1,5)*5))

Or, if you want on a separate sheet:
=IF(MOD(ROWS($A$1:$A1),5)=0,"",OFFSET(Sheet1!$A$3,INT((ROWS($A$1:$A1)-1)/20),MOD(INT((ROWS($A$1:$A1)-1)/5),4)+MOD(ROWS($A$1:$A1)-1,5)*5))

 

[Somendra Misra]

@ashish mehra

My official meeting beat me onto this

But here is an alternate solution, normal formula : Enter in A12 and copy down:

=IFERROR(INDEX($A$3:$S$6,INT((ROWS(B$12:B12)-1)/19)+1,(MOD((INT((ROWS(B$12:B12)-1)/5)),4)+1)+(MOD((ROWS(B$12:B12)-1),5)*5)),"")

I think part of the formula matches with @Luke M Formula, but I thought since I worked on it and it's non-volatile solution, I must share here .

Regards,

 

[Luke M]

Nice job SM. I thought about trying to go non-volatile, but I was feeling lazy today.

 

[Somendra Misra]

@Luke M

And I was caught up in a meeting, I hate going to meeting when I start working on a formula, but can't say NO to BOSS

Regards,

 

[shrivallabha]

BTW, just for the kick of it you could also try the normally unused feature (area) of INDEX formula as below:
=IFERROR(INDEX(($A$3:$D$6,$F$3:$I$6,$K$3:$N$6,$P$3:$S$6),CEILING(ROWS($B$12:$B12)/20,1),CEILING(MOD(ROWS($B$12:$B12),20)/5,1),MOD(ROWS($B$12:$B12),5)),"")

 

[ashish mehra]

@ashish mehra

My official meeting beat me onto this

But here is an alternate solution, normal formula : Enter in A12 and copy down:

=IFERROR(INDEX($A$3:$S$6,INT((ROWS(B$12:B12)-1)/19)+1,(MOD((INT((ROWS(B$12:B12)-1)/5)),4)+1)+(MOD((ROWS(B$12:B12)-1),5)*5)),"")

I think part of the formula matches with @Luke M Formula, but I thought since I worked on it and it's non-volatile solution, I must share here .

Regards,

Hi SM,

I have test your formula & find most of the results are good but somehow few output are not giving the require result. It's strange for me when I match with the result showing with Luke M formulas.

I am working on the file & will post the sample file soon.

Regards,
AM

 

[ashish mehra]

Ah, tricky little 4! It got away from me. New formula on same sheet:
=IF(MOD(ROWS($A$1:$A1),5)=0,"",OFFSET($A$3,INT((ROWS($A$1:$A1)-1)/20),MOD(INT((ROWS($A$1:$A1)-1)/5),4)+MOD(ROWS($A$1:$A1)-1,5)*5))

Or, if you want on a separate sheet:
=IF(MOD(ROWS($A$1:$A1),5)=0,"",OFFSET(Sheet1!$A$3,INT((ROWS($A$1:$A1)-1)/20),MOD(INT((ROWS($A$1:$A1)-1)/5),4)+MOD(ROWS($A$1:$A1)-1,5)*5))

Hi Luke,

I test your formulas & works like a charm.

I am not good in big formulas so initially I thought it could not be possible with formulas, it would only be possible using vba code as any for any change from end-user can be easily done. I tried using loop but could not able to succeed.

Many Thanks Sir

Regards,
AM

 

[ashish mehra]

BTW, just for the kick of it you could also try the normally unused feature (area) of INDEX formula as below:
=IFERROR(INDEX(($A$3:$D$6,$F$3:$I$6,$K$3:$N$6,$P$3:$S$6),CEILING(ROWS($B$12:$B12)/20,1),CEILING(MOD(ROWS($B$12:$B12),20)/5,1),MOD(ROWS($B$12:$B12),5)),"")

Hey Srivallabha,

Many thanks. Yours formula also giving the require output.

Regards,
AM

 

[ashish mehra]

Hi SM,

Here is the file. Output is in sheet2.

This is a collected file of all three answers from three experts. Happy learning guys.

Regards,
AM

 

[echo99]

sory newbii ....

i hope this can help, just sharing

hehehe ...

 

[ashish mehra]

sory newbii ....

i hope this can help, just sharing

hehehe ...

Many Thanks echo99,

I like your logic of using INDIRECT function.

Regards,
AM

 

[echo99]

hehehe your welcome, happy to be sharing and learn together .....

 

[Somendra Misra]

@ashish mehra

See the file, A small fault at my side changed the 19 in earlier formula to 20.

Regards,

 

[ashish mehra]

Thanks SM,

Its working fine. In the mean time there is twist in question regarding the separator.

What to do with end-user. doh!

Regards,
AM

 

[ashish mehra]

Here is the sample file.

Regards,
AM