Help Macro Transpose Data

N

namariegaudi

New Member
Joined
Oct 14, 2016
Messages
14
Hi, Need some help to build a macro that copies and transposes data. The rows vary in "products" entered from 1 or up to 5. need to bring the data as many times as I have products per row. is there a way I can send a copy of the data source and the fields I need to help illustrate better what I need?

Here's a sample of the data if it's helpful. The relevant data needed is marked with red text.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Calibri; color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 11.0px Calibri}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Calibri}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 11.0px Calibri; min-height: 13.0px}table.t1 {border-collapse: collapse}td.td1 {background-color: #38d4d6; border-style: solid; border-width: 0.8px 1.0px 0.8px 0.8px; border-color: #a8d6ff #cbcbcb #a8d6ff #a8d6ff; padding: 0.0px 5.0px 0.0px 5.0px}td.td2 {background-color: #38d4d6; border-style: solid; border-width: 0.8px 1.0px 0.8px 1.0px; border-color: #a8d6ff #cbcbcb #a8d6ff #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td3 {background-color: #38d4d6; border-style: solid; border-width: 0.8px 0.8px 0.8px 1.0px; border-color: #a8d6ff #a8d6ff #a8d6ff #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td4 {background-color: #d4feff; border-style: solid; border-width: 0.8px 1.0px 0.8px 0.8px; border-color: #a8d6ff #cbcbcb #a8d6ff #a8d6ff; padding: 0.0px 5.0px 0.0px 5.0px}td.td5 {background-color: #d4feff; border-style: solid; border-width: 0.8px 1.0px 0.8px 1.0px; border-color: #a8d6ff #cbcbcb #a8d6ff #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td6 {background-color: #d4feff; border-style: solid; border-width: 0.8px 0.8px 0.8px 1.0px; border-color: #a8d6ff #a8d6ff #a8d6ff #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td7 {border-style: solid; border-width: 0.8px 1.0px 0.8px 0.8px; border-color: #a8d6ff #cbcbcb #a8d6ff #a8d6ff; padding: 0.0px 5.0px 0.0px 5.0px}td.td8 {border-style: solid; border-width: 0.8px 1.0px 0.8px 1.0px; border-color: #a8d6ff #cbcbcb #a8d6ff #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td9 {border-style: solid; border-width: 0.8px 0.8px 0.8px 1.0px; border-color: #a8d6ff #a8d6ff #a8d6ff #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}</style>
ID
Fecha Real
Tipo de Orden
Proveedor
Proveeduría
Condiciones
Moneda
Estatus Orden
Unidad de Medida
Cotizacion
Producto_01
Producto_02
Producto_03
Producto_04
Producto_05
Cantidad_01
Cantidad_02
Cantidad_03
Cantidad_04
Cantidad_05
Unidad_01
Unidad_02
Unidad_03
Unidad_04
Unidad_05
PU_01
PU_02
PU_03
PU_04
PU_05
ST_01
ST_02
ST_03
ST_04
ST_05
IVA
Total
Año
Mes
Contador
Contador PO
Entrega
Numero de Orden
Fecha Previa
Creado
Elaboracion
Ruta de acceso
Tipo de elemento
43
08/01/2017
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
90
Euro
Abierta
Kilos


Lubro Clean Lito 010






112








17,920








1.65








29568


29568
2017
ago.






08/01/2017
09/07/2017 17:31
2017-09-07
Lists/LBIMX_Ordenes
Item
80
04/23/2018
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
90
Euro
Abierta
Kilos


Ester Lube 50/30 ISO VG
Lubro Grease PB
Lubro Solv LP
Lubro Emul 96
Lubro Drawing 84/IM
1
2
16
64
12
180
360
2,560
11,520
2,160
2.52
3.49
1.94
2.46
2.7
453.6
1256.4
4966.4
28339.2
5832


40847.6
2018
abr.
5
Inmediata
LME-


04/23/2018 10:58
2018-04-23
Lists/LBIMX_Ordenes
Item
81
04/23/2018
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
90
Euro
Abierta
Kilos


Lubro Drawing N/100






12








2,160








2.75








5940


5940
2018
abr.
1
Inmediata
LME-


04/23/2018 10:58
2018-04-23
Lists/LBIMX_Ordenes
Item
34
07/31/2017
Consumible
Desarrollo Esp. Quimicas S.A. de C.V.
Local
MXN
Abierta
Pieza
EUT2636
Agua Bidestilada
Agua Destilada
Acido Clorhidrico 2.5 LT Concentrado
Eter Etílico
Alcohol Etilico al 96% de 20 LT
1
1
3
3
1
1
1
3
3
1
160
140
351
1010
520
160
140
1053
3030
520
784.48
5687.48
2017
jul.








07/31/2017 11:28
2017-07-31
Lists/LBIMX_Ordenes
Item

<tbody>
</tbody>

Output should be like this
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Calibri; color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 11.0px Calibri}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Calibri}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 11.0px Calibri; min-height: 13.0px}table.t1 {border-collapse: collapse}td.td1 {background-color: #38d4d6; border-style: solid; border-width: 0.8px 1.0px 0.8px 0.8px; border-color: #a8d6ff #cbcbcb #a8d6ff #a8d6ff; padding: 0.0px 5.0px 0.0px 5.0px}td.td2 {background-color: #38d4d6; border-style: solid; border-width: 0.8px 1.0px 0.8px 1.0px; border-color: #a8d6ff #cbcbcb #a8d6ff #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}td.td3 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}</style>
ID
Fecha Real
Tipo de Orden
Proveedor
Proveeduría
Moneda
Estatus Orden
Unidad de Medida
Producto_01
Cantidad_01
Unidad_01
PU_01
ST_01
IVA
Total
Año
Mes
43
08/01/2017
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
Euro
Abierta
Kilos
Lubro Clean Lito 010
112
17920
1.65
29568


29568
2017
ago.
80
04/23/2018
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
Euro
Abierta
Kilos
Ester Lube 50/30 ISO VG
1
180
2.52
453.6


40847.6
2018
abr.
80
04/23/2018
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
Euro
Abierta
Kilos
Lubro Grease PB
2
360
3.49
1256.4


40847.6
2018
abr.
80
04/23/2018
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
Euro
Abierta
Kilos
Lubro Solv LP
16
2560
1.94
4966.4


40847.6
2018
abr.
80
04/23/2018
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
Euro
Abierta
Kilos
Lubro Emul 96
64
11520
2.46
28339.2


40847.6
2018
abr.
80
04/23/2018
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
Euro
Abierta
Kilos
Lubro Drawing 84/IM
12
2160
2.7
5832


40847.6
2018
abr.
81
04/23/2018
Producto
Lubritalia S.p.A. Localita San Marco dei Lupini 74019 Palagiano (TA) Italia
Foraneo
Euro
Abierta
Kilos
Lubro Drawing N/100
12
2160
2.75
5940


5940
2018
abr.
34
07/31/2017
Consumible
Desarrollo Esp. Quimicas S.A. de C.V.
Local
MXN
Abierta
Pieza
Agua Bidestilada
1
1
160
160
784.48
5687.48
2017
jul.
34
07/31/2017
Consumible
Desarrollo Esp. Quimicas S.A. de C.V.
Local
MXN
Abierta
Pieza
Agua Destilada
1
1
140
140
784.48
5687.48
2017
jul.
34
07/31/2017
Consumible
Desarrollo Esp. Quimicas S.A. de C.V.
Local
MXN
Abierta
Pieza
Acido Clorhidrico 2.5 LT Concentrado
3
3
351
1053
784.48
5687.48
2017
jul.
34
07/31/2017
Consumible
Desarrollo Esp. Quimicas S.A. de C.V.
Local
MXN
Abierta
Pieza
Eter Etílico
3
3
1010
3030
784.48
5687.48
2017
jul.
34
07/31/2017
Consumible
Desarrollo Esp. Quimicas S.A. de C.V.
Local
MXN
Abierta
Pieza
Alcohol Etilico al 96% de 20 LT
1
1
520
520
784.48
5687.48
2017
jul.

<tbody>
</tbody>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
How about
Code: 
Sub CopyTrans()
   Dim Cl As Range
   Dim Qty As Long
   Dim i As Long, col As Long, j As Long
   Dim ary() As Variant
   Dim lr As Long
   
   Columns(6).Hidden = True
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Qty = Application.CountA(Cl.Offset(, 10).Resize(, 5))
      ReDim ary(1 To 5, 1 To Qty)
      For i = 1 To Qty
         col = 10 + i
         For j = 1 To 5
            ary(j, i) = Cells(Cl.Row, col)
            col = col + 5
         Next j
      Next i
      lr = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      Cl.Resize(, 9).SpecialCells(xlVisible).Copy Sheets("Sheet2").Range("A" & lr).Resize(Qty)
      Sheets("Sheet2").Range("I" & lr).Resize(Qty, 5).Value = Application.Transpose(ary)
      Cl.Offset(, 35).Resize(, 4).Copy Sheets("Sheet2").Range("N" & lr).Resize(Qty)
   Next Cl

End Sub
 
Upvote 0
Cross posted https://www.excelforum.com/excel-pr...spose-data-into-multiple-row.html#post4901417

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Hi, I posted a new request a few weeks back, but can't seem to find it. Should I wait for an answer or repost on a new thread?
 
Upvote 0
Did you post that request to this site?
 
Upvote 0
Have a look to see if you posted it on ExcelForum.
If you did & want to start a thread here, just make sure that you include a link to the original question & also post a link there to say youve asked it here as well.
 
Upvote 0
I posted this same request on ExcelForum "Copy & Transpose Data from 2 Different Table into 2 Different sheets" thread HERE. I really do need some help here...regards
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Help coding Excel Macro
2
Replies
12
Views
1K
Fluff
Fluff
T
  • Question
use GETPIVOTDATA to get specific grandtotals
Replies
0
Views
413
taimy20
T
S
  • Question
Transpose Columns to Rows by blank
2
Replies
10
Views
941
Mike Szczesny
M

Forum statistics

Threads
1,215,133
Messages
6,123,234
Members
449,092
Latest member
SCleaveland

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top