Help Organising Data

[Arvon]

Hello all,

Not sure if this is in the correct part of the forum but here goes.

I am in dire need of some expert assistance as my limited knowledge of Excel has me completely stumped.

I have attached a small spreadsheet (just a snippet of the amount of data i actually have to process) that demonstrates the current format and required format.

there are a set number of column headings and they are
Companies
Ind / Edu Qualifications
Industry Technology
Positions
Programming Langs
International Regions
UK Regions
Sector / Vertical Mkt
Product / Search
Languages

If anyone can help me in trying to organize my data I would be hugely grateful.

many thanks

[James Niven]

Hi Arvon,

Welcome to this forum!

Please review the attached, it's my attempt at solving your issue, I am learning VBA as well so please excuse the simple code, but it gives you what you want.
I am sure someone else will offer a more elegant solution in the form of nicer code.

[snb]

Remove the first row in sheet2 before running this macro:

Sub M_snb()
    sn = Sheets("sheet2").Cells(1).CurrentRegion
    ReDim sp(UBound(sn), 8)
    
    For j = 1 To UBound(sn)
      sp((j - 1) \ 8, 0) = sn(j, 1)
      sp((j - 1) \ 8, (j - 1) Mod 8 + 1) = Replace(sn(j, 3), ";", vbLf)
    Next
    
    Sheets("sheet3").Cells(1, 2).Resize(, 8) = Array(sn(1, 2), sn(2, 2), sn(3, 2), sn(4, 2), sn(5, 2), sn(6, 2), sn(7, 2), sn(8, 2))
    Sheets("sheet3").Cells(2, 1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub