PDA

View Full Version : Help Organising Data



Arvon
09-08-2013, 12:07 PM
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
09-08-2013, 05:17 PM
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
09-09-2013, 02:09 AM
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