Hey All,
Here is a sample observation, the data is basically thousands of this kind of data listed in 2 columns.
<tbody>
</tbody>
And I want to put them in to format like this, with each observation occupying one row:
<tbody>
</tbody>
Basically the original format is a long list of data, and I need to put each observation into a row, while putting the values into the correct columns. The tricky point is that for the "AU","AI","DE","KW","AF", the numbers vary for different observations. Some observations have just one each, but some have 2,or 3 or more, so in the list, each observation takes up a different length of range. For those that have multiple "AU"'s, the table version would need to have "AU_1", "AU_2"....
I have an idea about how to achieve this, but I have little experience with VBA, so I don't even know how to get started. Can I say something like: (not in VBA codes)
i=1, j=0
For i<=100000:
Look at Ai,
if Ai="$$":
j=j+1
i=i+1
if Ai="AN", then let Aj(in sheet 2)= Bi(in sheet 1)
i=i+1
if Ai="ST", then let Bj (in sheet 2)=Bi(in sheet 1)
i=i+1
...
....
if Ai="AU", if Gj = "", Gj=Bi
if Gj !="", if Ij="", Ij=Bi
if Ij != "", if Kj="", Kj=Bi.....
i=i+1
if Ai="AI", .....
and so on,,
you get the idea, I know this is probably the most inefficient way to do it, but even so I don't know how to write out the code.. Any help would be appreciated!!!
Thanks!!
Here is a sample observation, the data is basically thousands of this kind of data listed in 2 columns.
| $$ | |
| AN | 0588470 |
| ST | Academia Economic Papers |
| IS | 1018-161X |
| CI | 29 3 |
| PD | September 2001 |
| DJ | 2001 |
| AU | Rau, Hsiu-Hua |
| AI | R |
| AU | Lin, Hsiou-Wei William |
| AI | L |
| AU | Li, Ming-Yuan Leon |
| AI | L |
| TI | Examining Taiwan's Business Cycle via Two-Period MS Models. (In Chinese. With English summary.) |
| DE | Business Fluctuations; Cycles_(E320) |
| DE | General Aggregative Models: General_(E100) |
| DE | Macroeconomic Analyses of Economic Development_(O110) |
| KW | Business Cycles; Cycle |
| AF | National Cheng Chi U |
| AF | National Taiwan U |
| AF | National Chi Nan U |
<tbody>
</tbody>
And I want to put them in to format like this, with each observation occupying one row:
| AN | ST | IS | CI | PD | DJ | AU_1 | AI_1 | AU_2 | AI_2 | ... | TI | DE_1 | DE_2 | DE_3 | .. | KW_1 | KW_2 | .. | AF_1 | AF_2 | AF_3 | ..AF_n |
<tbody>
</tbody>
Basically the original format is a long list of data, and I need to put each observation into a row, while putting the values into the correct columns. The tricky point is that for the "AU","AI","DE","KW","AF", the numbers vary for different observations. Some observations have just one each, but some have 2,or 3 or more, so in the list, each observation takes up a different length of range. For those that have multiple "AU"'s, the table version would need to have "AU_1", "AU_2"....
I have an idea about how to achieve this, but I have little experience with VBA, so I don't even know how to get started. Can I say something like: (not in VBA codes)
i=1, j=0
For i<=100000:
Look at Ai,
if Ai="$$":
j=j+1
i=i+1
if Ai="AN", then let Aj(in sheet 2)= Bi(in sheet 1)
i=i+1
if Ai="ST", then let Bj (in sheet 2)=Bi(in sheet 1)
i=i+1
...
....
if Ai="AU", if Gj = "", Gj=Bi
if Gj !="", if Ij="", Ij=Bi
if Ij != "", if Kj="", Kj=Bi.....
i=i+1
if Ai="AI", .....
and so on,,
you get the idea, I know this is probably the most inefficient way to do it, but even so I don't know how to write out the code.. Any help would be appreciated!!!
Thanks!!
