How to convert or transpose list into table?

The resulting table is a single string for each unit? Or is that separate cells in a row? Your sheet2 didn't have a mockup of what you really want to see.

Ideally the resulting table would have separate cells in a row something like this:

Column A                       Column B      Column C
ABBMcHSW01 (10.1.1.253):       Unit 1        Product serial number: YEFF6UH1F0840
ABBMcHSW01 (10.1.1.253):       Unit 2        Product serial number: YEFF75HD13AC0
ABIPBMTSW01 (10.1.1.121):      Unit 1        Product serial number: 9KXF9XMBF6140
ABIPCORESW01 (10.1.1.122):     Unit 1        Product serial number: 9KXF9XMBF5F00

where the information in Column A would be repeated if there was more than one switch in the stack

Struggling a bit with the smartest way to represent the data in the report. Once it is like this folks where I work with average Excel skills should be able to manipulate easier...