Mixing
Excel VBA: Compley copy & paste
1
as many of us, I'm new to VBA. Often I can research the answers to my questions, but I suppose this on is too specific.
I got the following arrangement of data:
<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Production site</td>
<td class=xl1523206 width=64 style='width:48pt'>Year</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 1</td>
<td class=xl1523206 width=64 style='width:48pt'> Part 2</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 3</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 4</td>
<td class=xl1523206 width=64 style='width:48pt'>Part n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>The number of parts, production sites and years is not limited.
Now I need to copy the data and put it into a different structure to be able to work with it.
This is how the data must look like:
<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Part</td>
<td class=xl1523206 width=64 style='width:48pt'>Production Site</td>
<td class=xl1523206 width=64 style='width:48pt'>2017</td>
<td class=xl1523206 width=64 style='width:48pt'>2018</td>
<td class=xl1523206 width=64 style='width:48pt'>2019</td>
<td class=xl1523206 width=64 style='width:48pt'></td>
<td class=xl1523206 width=64 style='width:48pt'>Year n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>I suppose I need to do all kind of indexing, looping and so on. Anybody with ideas to get my head around this task? I appreciate any input. Thanks a lot!
excel vba
edited Nov 19 '18 at 12:37
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 12:22
Rene Hanschke
83
add a comment |
1
as many of us, I'm new to VBA. Often I can research the answers to my questions, but I suppose this on is too specific.
I got the following arrangement of data:
<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Production site</td>
<td class=xl1523206 width=64 style='width:48pt'>Year</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 1</td>
<td class=xl1523206 width=64 style='width:48pt'> Part 2</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 3</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 4</td>
<td class=xl1523206 width=64 style='width:48pt'>Part n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>The number of parts, production sites and years is not limited.
Now I need to copy the data and put it into a different structure to be able to work with it.
This is how the data must look like:
<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Part</td>
<td class=xl1523206 width=64 style='width:48pt'>Production Site</td>
<td class=xl1523206 width=64 style='width:48pt'>2017</td>
<td class=xl1523206 width=64 style='width:48pt'>2018</td>
<td class=xl1523206 width=64 style='width:48pt'>2019</td>
<td class=xl1523206 width=64 style='width:48pt'></td>
<td class=xl1523206 width=64 style='width:48pt'>Year n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>I suppose I need to do all kind of indexing, looping and so on. Anybody with ideas to get my head around this task? I appreciate any input. Thanks a lot!
excel vba
edited Nov 19 '18 at 12:37
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 12:22
Rene Hanschke
83
Looks like unpivoting Part and pivoting on year. You could give it a quick go with Powerquery and see if that works using data > from table to import the source and then righ clicking on columns to pivot/unpivot (or via transform tab)
– QHarr
Nov 19 '18 at 12:25
1
That does the trick without VBA. Thanks a lot!
– Rene Hanschke
Nov 19 '18 at 15:44
I'll put in case anyone else come across
– QHarr
Nov 19 '18 at 16:07
add a comment |
1
1
1
as many of us, I'm new to VBA. Often I can research the answers to my questions, but I suppose this on is too specific.
I got the following arrangement of data:
<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Production site</td>
<td class=xl1523206 width=64 style='width:48pt'>Year</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 1</td>
<td class=xl1523206 width=64 style='width:48pt'> Part 2</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 3</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 4</td>
<td class=xl1523206 width=64 style='width:48pt'>Part n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>The number of parts, production sites and years is not limited.
Now I need to copy the data and put it into a different structure to be able to work with it.
This is how the data must look like:
<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Part</td>
<td class=xl1523206 width=64 style='width:48pt'>Production Site</td>
<td class=xl1523206 width=64 style='width:48pt'>2017</td>
<td class=xl1523206 width=64 style='width:48pt'>2018</td>
<td class=xl1523206 width=64 style='width:48pt'>2019</td>
<td class=xl1523206 width=64 style='width:48pt'></td>
<td class=xl1523206 width=64 style='width:48pt'>Year n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>I suppose I need to do all kind of indexing, looping and so on. Anybody with ideas to get my head around this task? I appreciate any input. Thanks a lot!
excel vba
edited Nov 19 '18 at 12:37
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 12:22
Rene Hanschke
83
as many of us, I'm new to VBA. Often I can research the answers to my questions, but I suppose this on is too specific.
I got the following arrangement of data:
<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Production site</td>
<td class=xl1523206 width=64 style='width:48pt'>Year</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 1</td>
<td class=xl1523206 width=64 style='width:48pt'> Part 2</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 3</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 4</td>
<td class=xl1523206 width=64 style='width:48pt'>Part n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>The number of parts, production sites and years is not limited.
Now I need to copy the data and put it into a different structure to be able to work with it.
This is how the data must look like:
<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Part</td>
<td class=xl1523206 width=64 style='width:48pt'>Production Site</td>
<td class=xl1523206 width=64 style='width:48pt'>2017</td>
<td class=xl1523206 width=64 style='width:48pt'>2018</td>
<td class=xl1523206 width=64 style='width:48pt'>2019</td>
<td class=xl1523206 width=64 style='width:48pt'></td>
<td class=xl1523206 width=64 style='width:48pt'>Year n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>I suppose I need to do all kind of indexing, looping and so on. Anybody with ideas to get my head around this task? I appreciate any input. Thanks a lot!
<table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Production site</td>
<td class=xl1523206 width=64 style='width:48pt'>Year</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 1</td>
<td class=xl1523206 width=64 style='width:48pt'> Part 2</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 3</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 4</td>
<td class=xl1523206 width=64 style='width:48pt'>Part n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table><table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Production site</td>
<td class=xl1523206 width=64 style='width:48pt'>Year</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 1</td>
<td class=xl1523206 width=64 style='width:48pt'> Part 2</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 3</td>
<td class=xl1523206 width=64 style='width:48pt'>Part 4</td>
<td class=xl1523206 width=64 style='width:48pt'>Part n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site A</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2017</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2018</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Site B</td>
<td class=xl1523206 align=right>2019</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table><table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Part</td>
<td class=xl1523206 width=64 style='width:48pt'>Production Site</td>
<td class=xl1523206 width=64 style='width:48pt'>2017</td>
<td class=xl1523206 width=64 style='width:48pt'>2018</td>
<td class=xl1523206 width=64 style='width:48pt'>2019</td>
<td class=xl1523206 width=64 style='width:48pt'></td>
<td class=xl1523206 width=64 style='width:48pt'>Year n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table><table border=0 cellpadding=0 cellspacing=0 width=491 style='border-collapse:
collapse;table-layout:fixed;width:368pt'>
<col width=107 style='mso-width-source:userset;mso-width-alt:3913;width:80pt'>
<col width=64 span=6 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 width=107 style='height:15.0pt;width:80pt'>Part</td>
<td class=xl1523206 width=64 style='width:48pt'>Production Site</td>
<td class=xl1523206 width=64 style='width:48pt'>2017</td>
<td class=xl1523206 width=64 style='width:48pt'>2018</td>
<td class=xl1523206 width=64 style='width:48pt'>2019</td>
<td class=xl1523206 width=64 style='width:48pt'></td>
<td class=xl1523206 width=64 style='width:48pt'>Year n</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 1</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>8</td>
<td class=xl1523206 align=right>6</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>5</td>
<td class=xl1523206 align=right>3</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site A</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>3</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site B</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1523206 style='height:15.0pt'>Part 2</td>
<td class=xl1523206 align=right>Site n</td>
<td class=xl1523206 align=right>7</td>
<td class=xl1523206 align=right>4</td>
<td class=xl1523206 align=right>1</td>
<td class=xl1523206 align=right>2</td>
<td class=xl1523206 align=right>0</td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=107 style='width:80pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>excel vba
excel vba
edited Nov 19 '18 at 12:37
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 12:22
Rene Hanschke
83
edited Nov 19 '18 at 12:37
Pᴇʜ
20.2k42650
asked Nov 19 '18 at 12:22
Rene Hanschke
83
edited Nov 19 '18 at 12:37
Pᴇʜ
20.2k42650
edited Nov 19 '18 at 12:37
Pᴇʜ
20.2k42650
edited Nov 19 '18 at 12:37
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 19 '18 at 12:22
Rene Hanschke
83
asked Nov 19 '18 at 12:22
Rene Hanschke
83
asked Nov 19 '18 at 12:22
Rene Hanschke
83
83
Looks like unpivoting Part and pivoting on year. You could give it a quick go with Powerquery and see if that works using data > from table to import the source and then righ clicking on columns to pivot/unpivot (or via transform tab)
– QHarr
Nov 19 '18 at 12:25
1
That does the trick without VBA. Thanks a lot!
– Rene Hanschke
Nov 19 '18 at 15:44
I'll put in case anyone else come across
– QHarr
Nov 19 '18 at 16:07
add a comment |
Looks like unpivoting Part and pivoting on year. You could give it a quick go with Powerquery and see if that works using data > from table to import the source and then righ clicking on columns to pivot/unpivot (or via transform tab)
– QHarr
Nov 19 '18 at 12:25
1
That does the trick without VBA. Thanks a lot!
– Rene Hanschke
Nov 19 '18 at 15:44
I'll put in case anyone else come across
– QHarr
Nov 19 '18 at 16:07
Looks like unpivoting Part and pivoting on year. You could give it a quick go with Powerquery and see if that works using data > from table to import the source and then righ clicking on columns to pivot/unpivot (or via transform tab)
– QHarr
Nov 19 '18 at 12:25
Looks like unpivoting Part and pivoting on year. You could give it a quick go with Powerquery and see if that works using data > from table to import the source and then righ clicking on columns to pivot/unpivot (or via transform tab)
– QHarr
Nov 19 '18 at 12:25
1
1
That does the trick without VBA. Thanks a lot!
– Rene Hanschke
Nov 19 '18 at 15:44
That does the trick without VBA. Thanks a lot!
– Rene Hanschke
Nov 19 '18 at 15:44
I'll put in case anyone else come across
– QHarr
Nov 19 '18 at 16:07
I'll put in case anyone else come across
– QHarr
Nov 19 '18 at 16:07
add a comment |
1 Answer
1
active
oldest
votes
1
This can be done using Powerquery. You are unpivoting Part and pivoting on year.
Load the table using data > from table to import the source, in Excel 2013 use the powerquery add-in tab (free download from microsoft), in the editor window tha opens you then right clicking on columns to pivot/unpivot (or via transform tab) and finally close and load to sheet.
answered Nov 19 '18 at 16:09
QHarr
30.1k81941
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53374553%2fexcel-vba-compley-copy-paste%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
This can be done using Powerquery. You are unpivoting Part and pivoting on year.
Load the table using data > from table to import the source, in Excel 2013 use the powerquery add-in tab (free download from microsoft), in the editor window tha opens you then right clicking on columns to pivot/unpivot (or via transform tab) and finally close and load to sheet.
answered Nov 19 '18 at 16:09
QHarr
30.1k81941
add a comment |
1
This can be done using Powerquery. You are unpivoting Part and pivoting on year.
Load the table using data > from table to import the source, in Excel 2013 use the powerquery add-in tab (free download from microsoft), in the editor window tha opens you then right clicking on columns to pivot/unpivot (or via transform tab) and finally close and load to sheet.
answered Nov 19 '18 at 16:09
QHarr
30.1k81941
add a comment |
1
1
1
This can be done using Powerquery. You are unpivoting Part and pivoting on year.
Load the table using data > from table to import the source, in Excel 2013 use the powerquery add-in tab (free download from microsoft), in the editor window tha opens you then right clicking on columns to pivot/unpivot (or via transform tab) and finally close and load to sheet.
answered Nov 19 '18 at 16:09
QHarr
30.1k81941
This can be done using Powerquery. You are unpivoting Part and pivoting on year.
Load the table using data > from table to import the source, in Excel 2013 use the powerquery add-in tab (free download from microsoft), in the editor window tha opens you then right clicking on columns to pivot/unpivot (or via transform tab) and finally close and load to sheet.
answered Nov 19 '18 at 16:09
QHarr
30.1k81941
answered Nov 19 '18 at 16:09
QHarr
30.1k81941
answered Nov 19 '18 at 16:09
QHarr
30.1k81941
answered Nov 19 '18 at 16:09
QHarr
30.1k81941
30.1k81941
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53374553%2fexcel-vba-compley-copy-paste%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Looks like unpivoting Part and pivoting on year. You could give it a quick go with Powerquery and see if that works using data > from table to import the source and then righ clicking on columns to pivot/unpivot (or via transform tab)
– QHarr
Nov 19 '18 at 12:25
1
That does the trick without VBA. Thanks a lot!
– Rene Hanschke
Nov 19 '18 at 15:44
I'll put in case anyone else come across
– QHarr
Nov 19 '18 at 16:07