splitting and multiplying data

[boaz133]

hey

need a little help...
i have a column of numerical data that starts with two zeroes (i.e. 00240011).
i need to copy it to a different column in a way that each cell will be multiplying 4 times in four different rows but keeps the zeroes in the beginning of the number.
how can it be done?

Thnx in advanced...

[samba_ravi]

Will you please attach a sample excel file with expected result

[Special-K]

It's not clear to me what multiplying 4 times in four different rows means.
So post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[Keebellah]

You add a custom format to those cells
Select Cell Formatting and the the Number, then click on Custom and the number of 0000000 equal to the length of the number you want.
In the formula bar you will see 4567 but in the cell it will be 0004567

[boaz133]

hey

I can't attach a sample file for some reason...
So i'll try to be more clear:
on Column A i have Numerical Data such as
0022001
0022045
0023054 etc.

on column B i have the number of repeats needed (in this case it's 4 but it can vary).
The expected outcome should appear on column C like this:
0022001
0022001
0022001
0022001
0022045
0022045
0022045
0022045
etc.

i.e. - each number that appear on column A should appear 4 times on column C on 4 consecutive rows.
i managed to get it done (Sort of) using a VLookup formula, but it's so not elegant... I'm sure there's a better way to do it, but i couldn't came up with one...
hope you could help me.

Thnx in advanced.

[Special-K]

As I stated above
When uploading files use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments
as the "paperclip" method does not work on this forum.

[boaz133]

attached.

thanx.

[Haseeb Avarakkan]

Hello,

Try this in C2, and copy down.

=INDEX(A$2:A$1000,INT((ROWS(C$2:C2)-1)/4)+1)

[boaz133]

hey

Thanks a lot. it works great...
is there a way i could manipulate this formula to take into consideration the numbers of repeats (col. B)?
meaning, to change the numbers of times the number appears on Col. C according to the number in Col. B (if B2 is 5 for example, i want it to appear 5 times on col/ C).
example attached.

Thnx.

[Special-K]

Hello,

Try this in C2, and copy down.

=INDEX(A$2:A$1000,INT((ROWS(C$2:C2)-1)/4)+1)

That may not work because of this

" i have the number of repeats needed (in this case it's 4 but it can vary)."

UPDATE: That solution definitely won't work with the new file attached as per post #9

[Haseeb Avarakkan]

Hello,

My bad. The OP was uploaded a file while I was responding to the post.

Try this.

In C2,

=A2

In C3 & copy down until see zero.

=INDEX(A:A,IFERROR(MATCH(C2,A:A,0),2)+(ROWS(C$3:C3)=SUMPRODUCT(SUMIF(A:A,A$2:INDEX(A:A,MATCH(C2,A:A,0)),B:B))))

[boaz133]

hey Haseeb

it is working great!
thanks a lot for your help!