Macro to create a file from base file if data is availabe in defined columns

[shansakhi]

Hi All,

Good Evening.

I require your help for creating Macro.

Below is the base file and the highlighted row 1 are the headers.

I want to prepare a another file based on the base file if data is available under headers S A/C, A A/C, G A/C and W A/C.

CODE1

PRO 1

CODE Details

Completed Date

SSD

SED

TSD

TED

FB

Row No

S A/C

A A/C

G A/C

W A/C

KW1

KWI1

ABCD

6-Aug-14

6-Aug-14

10-Aug-14

7-Aug-14

30-Sep-14

M1

KRPW

-

-

-

-

KW1

KWI1

ABCD

6-Aug-14

6-Aug-14

10-Aug-14

7-Aug-14

30-Sep-14

E1

KRPW

-

-

-

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR3

GMJP

GMJ01

34661

CHA

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR4

GMJP

GMJ01

34661

CHA

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR5

GMJP

GMJ01

34661

CHA

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR6

GMJP

GMJ01

34661

CHA

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR7

GMJP

GMJ01

34661

CHA

-

<TBODY>
</TBODY>

Output File
In Output file the headers will be fixed... we need to populate the data from Base file under the correct heading if data is available in defined columns of Base file (mentioned above)

Sr no

Code Details

code 1

G A/C

A A/C

W A/C

S A/C

FBR1

FBR2

FBR3

FBR4

FBR5

FBR6

FBR7

M1

E1

SSD

SED

TSD

TED

PRO1

Completed date

1

Tejas Corp

KW1

CHA

34661

-

GMJ01

-

-

GMJP1

GMJP2

GMJP3

GMJP4

GMJP5

-

-

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

HDS11

24-Aug-14

<TBODY>
</TBODY>

Hope I am able to explain you the required.
Please feel free to ask me if you need any clarification....

Regards
Shan

 

[skorpionkz]

So all columns for particular code are the same except FB and you want transpose it with added row number?

Could you review your example as it seems that require output is incorrect. If it is correct then I don't understand the logic behind it

 

[shansakhi]

Thank you for replying.

I made some changes in the file. Please let me know if I am clear.

If you see there are 5 rows for Tejas Corp we would like to have only one row for Tejas Corp ... Data is available in S A/C, A A/C, G A/C hence we would like to exctract this.
For code details ABCD there is no data in S A/C, A A/C, G A/C or W A/C hence we are not considering the same.

Base File:

CODE1

PRO 1

CODE Details

Completed Date

SSD

SED

TSD

TED

FB

Row No

S A/C

A A/C

G A/C

W A/C

KW1

KWI1

ABCD

6-Aug-14

6-Aug-14

10-Aug-14

7-Aug-14

30-Sep-14

M1

KRPW

-

-

-

-

KW1

KWI1

ABCD

6-Aug-14

6-Aug-14

10-Aug-14

7-Aug-14

30-Sep-14

E1

KRPW

-

-

-

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR3

GMJP1

GMJ01

34661

CHA

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR4

GMJP2

GMJ01

34661

CHA

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR5

GMJP3

GMJ01

34661

CHA

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR6

GMJP4

GMJ01

34661

CHA

-

HD1

HDS11

Tejas Corp

24-Aug-14

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

FBR7

GMJP5

GMJ01

34661

CHA

-

<TBODY>
</TBODY>

Output File

Sr no

Code Details

code 1

G A/C

A A/C

W A/C

S A/C

FBR1

FBR2

FBR3

FBR4

FBR5

FBR6

FBR7

M1

E1

SSD

SED

TSD

TED

PRO1

Completed date

1

Tejas Corp

HD1

CHA

34661

-

GMJ01

-

-

GMJP1

GMJP2

GMJP3

GMJP4

GMJP5

-

-

25-Aug-14

30-Jun-14

25-Aug-14

30-Jun-14

HDS11

24-Aug-14

<TBODY>
</TBODY>

 

[skorpionkz]

Could you advice that there won't be any other columns name for FB but FBR1-FBR7, E1 and M1. So that if you will have another company it would have only one of those.

 

[shansakhi]

Hi,
As of now we have fbr1-fbr7, M1,E1... In future we may hv some more. That we will add in to output file.

 

[skorpionkz]

It would be quite hard to create the code which will do this kind of analyses as well and add new columns. I can help with creating macro, but your request is complex and require lots of time.

 

[shansakhi]

hi,
Thank you. The columns will be added by us. We will add all possible FB in the output file. The micro should only populate the data from base file to the fb columns ...