Need a formula to fill in a template

[Michael Makotore]

Hi Guys

I have more than 1000 rows of the source data sample I need to format to come up with data in the final template. Is there a formula i can use to speed up the process?

See attached file.

Michael

 

[AliGW]

In I2 copied down:

=IF(MOD(ROW()-1,3)=0,2,CHOOSE(MOD(ROW()-1,3),OFFSET(A$2,(ROWS(A$2:A2)-1)/3,0),1))

In J2 copied down:

=IF(I2=1,INDEX($B$2:$B$9,MATCH(I1,$A$2:$A$9,0)),IF(I2=2,"","USD"))

 

[bosco_yip]

In I2, copy down :

=IF(MOD(ROWS($1:1),3)=1,INDEX(A$2:A$9,INT((ROWS($1:1)-1)/3)+1),N(I1)+1)

In J2, copy down :

=IF(ISTEXT(I2),"USD",IF(I2=1,INDEX(B$2:B$9,SUMIF(I$2:I3,1)),""))

Regards
Bosco

 

[Michael Makotore]

In I2 copied down:

=IF(MOD(ROW()-1,3)=0,2,CHOOSE(MOD(ROW()-1,3),OFFSET(A$2,(ROWS(A$2:A2)-1)/3,0),1))

In J2 copied down:

=IF(I2=1,INDEX($B$2:$B$9,MATCH(I1,$A$2:$A$9,0)),IF(I2=2,"","USD"))

This is working perfectly Ali. fantastic staff.
Thank you

 

[AliGW]

You're welcome!

 

[Michael Makotore]

In I2, copy down :

=IF(MOD(ROWS($1:1),3)=1,INDEX(A$2:A$9,INT((ROWS($1:1)-1)/3)+1),N(I1)+1)

In J2, copy down :

=IF(ISTEXT(I2),"USD",IF(I2=1,INDEX(B$2:B$9,SUMIF(I$2:I3,1)),""))

Regards
Bosco

Waaaal Fantastic.
Its working Bosco.
A big thanks to you guys.

Michael