• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UN-pivoting a pivoted range with vba

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 FromName ToType
Alisa MoralesElwood Iglesiasvalue two
Elwood IglesiasAlisa Moralesvalue one
Elwood IglesiasGeorgette Harrisonvalue three
Elwood IglesiasFannie Fuentesvalue four
Fannie FuentesAlisa Moralesvalue three
Fannie FuentesGeorgette HarrisonValue five
Georgette HarrisonAlisa Moralesvalue four
Georgette HarrisonElwood Iglesiasvalue two
Georgette HarrisonFannie FuentesValue five
Sam SpadeFannie Fuentesvalue one
Sam SpadeElwood Iglesiasvalue two
Sam SpadeAlisa Moralesvalue two
Sam SpadeGeorgette Harrisonvalue four
Sam SpadeMaxy Maxvalue five
Maxy MaxSam Spadevalue five

I need

Participant NameParticipant relationship #1Type #1Participant relationship #2Type #2Participant relationship #3Type #3Participant relationship #4Type #4Participant relationship #5Type #5
Alisa MoralesElwood IglesiasValue Two
Elwood IglesiasAlisa MoralesValue oneGeorgette Harrisonvalue threeFannie Fuentesvalue four
Fannie FuentesAlisa MoralesValue threeGeorgette Harrisonvalue five
Georgette Harrisonalisa Moralesvalue fourElwood IglesiasValue twoFannie Fuentesvalue five
Sam SpadeFanny Fuentesvalue oneElwood Iglesiasvalue twoAlisa Moralesvalue oneGeorgette HarriosonValue fourMaxy MaxValue five
Maxy MaxSam SpadeValue 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



Excel Ninja
Tim Hanson
Please, reread