UN-pivoting a pivoted range with vba

[Tim Hanson]

I posted this question on MrExcel and a kind gentleman posted an answer in Power Query that works but I need an answer in VBA if possible and was hoping someone here could help

Hello, I have data in the shape

Name From	Name To	Type
Alisa Morales	Elwood Iglesias	value two
Elwood Iglesias	Alisa Morales	value one
Elwood Iglesias	Georgette Harrison	value three
Elwood Iglesias	Fannie Fuentes	value four
Fannie Fuentes	Alisa Morales	value three
Fannie Fuentes	Georgette Harrison	Value five
Georgette Harrison	Alisa Morales	value four
Georgette Harrison	Elwood Iglesias	value two
Georgette Harrison	Fannie Fuentes	Value five
Sam Spade	Fannie Fuentes	value one
Sam Spade	Elwood Iglesias	value two
Sam Spade	Alisa Morales	value two
Sam Spade	Georgette Harrison	value four
Sam Spade	Maxy Max	value five
Maxy Max	Sam Spade	value five

I need

Participant Name	Participant relationship #1	Type #1	Participant relationship #2	Type #2	Participant relationship #3	Type #3	Participant relationship #4	Type #4	Participant relationship #5	Type #5
Alisa Morales	Elwood Iglesias	Value Two
Elwood Iglesias	Alisa Morales	Value one	Georgette Harrison	value three	Fannie Fuentes	value four
Fannie Fuentes	Alisa Morales	Value three	Georgette Harrison	value five
Georgette Harrison	alisa Morales	value four	Elwood Iglesias	Value two	Fannie Fuentes	value five
Sam Spade	Fanny Fuentes	value one	Elwood Iglesias	value two	Alisa Morales	value one	Georgette Harrioson	Value four	Maxy Max	Value five
Maxy Max			Sam Spade	Value Five

The difference between my example above and my real data is that my dataset has ~365 unique 'from' names and each 'from' name has anywhere from 1-170 'to' names (each 'from-to' pair is 'a connection') - for a total of ~8,500 rows or ~8,500 'connections'.

I need to repeat the 'name.#' & 'type.#' as many times as the most-connected person has connections - so ~170 times.

Thank you for any help on this

 

[AlanSidman]

crossposted: https://www.mrexcel.com/forum/excel-questions/1101448-un-pivoting-pivoted-range.html

 

[vletm]

Tim Hanson
Please, reread

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org