Insert Rows and Transpose Data: Macro or Formulas?

[jyadayada]

Hi All, Happy New Year! :D

I think it's a macro i need but it might well be achievable by a set of functions?

I have been asked to provide a list looking like this:

Person Warehouse StockRole
Person 1 EBWALL Administrator
Person 1 ECOMPD Administrator
Person 1 ECOSHH Administrator
Person 1 EGENTR Administrator
Person 1 EJ4RCK Administrator
Person 1 ELOCKR Administrator
Person 1 EMRACK Administrator
Person 1 EPAINT Administrator
Person 1 EROOFS Administrator
Person 1 ESTORE Administrator
Person 2 EBWALL Stock Manager
Person 2 ECOMPD Stock Manager
Person 2 ECOSHH Stock Manager
Person 2 EGENTR Stock Manager
Person 2 EJ4RCK Stock Manager
Person 2 ELOCKR Stock Manager
Person 2 EMRACK Stock Manager
Person 2 EPAINT Stock Manager
Person 2 EROOFS Stock Manager
Person 2 ESTORE Stock Manager
Person 3 EBWALL Read Only
Person 3 ECOMPD Read Only
Person 3 ECOSHH Read Only
Person 3 EGENTR Read Only
Person 3 EJ4RCK Read Only
Person 3 ELOCKR Read Only
Person 3 EMRACK Read Only
Person 3 EPAINT Read Only
Person 3 EROOFS Read Only
Person 3 ESTORE Read Only


But because i have aprox 15000 entries in the Person column, with a single row for each, I would have to Insert the additional 9 rows against each name and add the 10 Warehouse entries and populate the Stock Role column against each. The Stock Role is the same against every warehouse against any given Person name, but differs for a different Persons.

The format I can create easily is this:

PersonnelName UserClass StockRole EBWALL ECOMPD ECOMPD ECOMPD ECOMPD ECOMPD ECOMPD ECOMPD ECOMPD ECOMPD
Person 1 Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator
Person 2 Helpdesk Stock Manager Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator
Person 3 Senior Operative Read Only Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator Administrator


Please see attachment for better laid out example.

Is there a way to create the first from the second or from the 'rules' that avoids a manual exercise repeated 15000 times?

thanks in advance!

Jason

[sandy666]

with Power Query

Please Login or Register  to view this content.

see attachment, is that what you want?

[JohnTopley]

in A3

=INDEX($I$3:$I$5,INT((ROWS($1:1)-1)/10)+1)

in B3

=INDEX($L$2:$U$2,,MOD((ROWS($1:1)-1),10)+1)

in c3

=INDEX($K$3:$K$5,INT((ROWS($1:1)-1)/10)+1)

[thomglea]

And here's a VBA solution. I made your output into an Excel table as it's easier to manage.

[Fluff13]

Another option if you have the new functions is

Please Login or Register  to view this content.