Convert table to columms

[yaelcohen]

Hello

Thanks for your help!

If I have a table that look like thatattached also a file)

Person Name/year 2002 2003 2004 2005 2006
John 1 2 2 1 3
Jacob 1 3 2 4 3
Ab 5 6 8 9 12

How can I convert it to:

John 2002 1
John 2003 2
/
/
Ab 2002 5

How can I use formula for it?

Thanks a lot

Yael

 

[jindon]

For the data provided

Sub test()
    Dim a, b, i As Long, ii As Long, n As Long
    a = Range("b2").CurrentRegion.Value
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
    For i = 2 To UBound(a, 1)
        For ii = 2 To UBound(a, 2)
            n = n + 1
            b(n, 1) = a(i, 1): b(n, 2) = a(1, ii): b(n, 3) = a(i, ii)
    Next ii, i
    Range("o2").Resize(n, 3).Value = b
End Sub

 

[NARAYANK991]

Hi Yael ,

See the attached file ; a few named ranges have been defined.

Narayan

 

[John Jairo V]

Hi to all!

Another solution, with Power Query (I leave the video here - Excel in Spanish, but is easy to understand). I did the video in Excel 2016, but you can download the add-in Power Query if you have Excel 2010 or newer.

The advantage... is easy for updating (right click above results, update) and easy reply.

Blessings!

 

[Marc L]

Hola John Jairo !

As you are comfortable with Power Query, could you take a glance
to the thread Getting data from web with Power Query and
post back your opinion, thanks !
​

 

[John Jairo V]

Hi Marc L!

I'm just starting with Power Query... and this is web scraping, and need knowlegdes about M code for achieve this. I think maybe this link could help... but I'm not sure:

https://social.technet.microsoft.co...ref-attributes-in-html-page-?forum=powerquery

With VBA maybe is more factible. Blessings!

 

[yaelcohen]

Thanks a lot!

 

[yaelcohen]

By the way, this is the best professional forum in the internet!

 

[Marc L]

Thanks John Jairo !

I know how to achieve web scraping via VBA
but like demand is linked to Power Query …
​