• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Extract [SOLVED]

S

ssraghunathan

New Member
Hi,


I would like to extract data for me to integrate with the ERP System. Please find below the sample worksheet (link is mentioned below)


https://dl.dropboxusercontent.com/u/67680061/Data%20Sheet.xlsx


Data Sheet : Typical data we used in excell sheet.

Data Extract : The data to be extracted such fashion in order to upload into the ERP system.


Please let me know, if you need any further inputs.


Thanks


Raghunathan S
 
Hi, ssraghunathan!


If you haven't a lot of records you could try this 1st formula solution, otherwise you use the 2nd formula solution or consider VBA code (macro) for speeding up the process:


A1:C1 : Empcode/Details/Amount


1st. Solution (Indirect & Address)


A2: =INDIRECTO(DIRECCION(ENTERO((FILA()-1+23)/24)+1;1;4;1;"Data Sheet")) -----> in english: =INDIRECT(ADDRESS(INT((ROW()-1+23)/24)+1,1,4,1,"Data Sheet"))


B2: =INDIRECTO(DIRECCION(1;RESIDUO(FILA()-1;24)+(1-SIGNO(RESIDUO(FILA()-1;24)))*24+1;4;1;"Data Sheet")) -----> in english: =INDIRECT(ADDRESS(1,MOD(ROW()-1,24)+(1-SIGN(MOD(ROW()-1,24)))*24+1,4,1,"Data Sheet"))


C2 : =INDIRECTO(DIRECCION(ENTERO((FILA()-1+23)/24)+COLUMNA()-2;RESIDUO(FILA()-1;24)+(1-SIGNO(RESIDUO(FILA()-1;24)))*24+1;4;1;"Data Sheet")) -----> in english: =INDIRECT(ADDRESS(INT((ROW()-1+23)/24)+COLUMN()-2,MOD(ROW()-1,24)+(1-SIGN(MOD(ROW()-1,24)))*24+1,4,1,"Data Sheet"))


Copy down A2:C2 as required.


2nd. Solution (Index)


A2: =INDICE('Data Sheet'!$1:$3;ENTERO((FILA()-1+23)/24)+1;1) -----> in english: =INDEX('Data Sheet'!$1:$3,INT((ROW()-1+23)/24)+1,1)


B2: =INDICE('Data Sheet'!$1:$3;1;RESIDUO(FILA()-1;24)+(1-SIGNO(RESIDUO(FILA()-1;24)))*24+1) -----> in english: =INDEX('Data Sheet'!$1:$3,1,MOD(ROW()-1,24)+(1-SIGN(MOD(ROW()-1,24)))*24+1)


C2 : =INDICE('Data Sheet'!$1:$3;ENTERO((FILA()-1+23)/24)+COLUMNA()-2;RESIDUO(FILA()-1;24)+(1-SIGNO(RESIDUO(FILA()-1;24)))*24+1) -----> in english: =INDEX('Data Sheet'!$1:$3,INT((ROW()-1+23)/24)+COLUMN()-2,MOD(ROW()-1,24)+(1-SIGN(MOD(ROW()-1,24)))*24+1)


Copy down A2:C2 as required.


Just advise if any issue.


Regards!


EDITED


PS: Fixed formulas as noticed by the OP.
 
Hi,


Thanks for your response.


I tried the above formule in the sheet. It's working fine for the first row and from next row onwards the details column, it picks the data rather than nomenclature (viz. basic, hra, etc.)


i'm not able to figure it out to correct the formulae.


Pls. find the table again.


https://dl.dropboxusercontent.com/u/67680061/Data%20Sheet.xlsx


Thanks


Raghunathan S
 
Hi, ssraghunathan!

Sorry, my mistake in column B formulas. Yet fixed in my previous post.

Regards!
 
Hi, ssraghunathan!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top