Create a new table starting from another one

anpak

New Member
Joined
Oct 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm new on this forum. Below my first question :)

I would like to create a macro in order to obtain the table named "Output" starting from the table named "Input". Please see the image below.

Is it possible?

1634652188479.png



Thank you in advance :)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,841
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Any reason you want a macro, as this can be done with a formula?
 

anpak

New Member
Joined
Oct 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hello fluff,

how can I do that with formulas?

I asked for a macro because the table "input" could have different size (it becomes from an external report).

Thank you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,841
Office Version
  1. 365
Platform
  1. Windows
Ok, will it always be 5 columns wide with a varying number of rows?
 

anpak

New Member
Joined
Oct 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The table "Input" can have maximum 50 colums and variable rows
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,841
Office Version
  1. 365
Platform
  1. Windows
Is it an actual table, or just a range of cells?
Also does it have a header row?
 

anpak

New Member
Joined
Oct 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No it is not formatted as table, it is just a range of cells
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,841
Office Version
  1. 365
Platform
  1. Windows
And a header row?
 

anpak

New Member
Joined
Oct 19, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
ops sorry, the header row is present, but we don't have to consider it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,841
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub anpak()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   With Sheets("Sheet1")
      c = .Cells(1, Columns.Count).End(xlToLeft).Column
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, c).Value2
   End With
   ReDim Nary(1 To UBound(Ary) * c, 1 To 2)
   
   For r = 1 To UBound(Ary)
      For c = 2 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            Nary(nr, 2) = Ary(r, c)
         End If
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 2).Value = Nary
End Sub
 

Forum statistics

Threads
1,147,924
Messages
5,743,894
Members
423,824
Latest member
leahpatterson

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
Top