Forum Discussion
Excell formula help
Thank you for your comments. I have a nasty feeling that much of the material I post is unintelligible for the end user who will approach problems bottom-up in the time-honoured spreadsheet manner. Something that I find interesting is that the new dynamic array functionality requires top-down thinking. Instead of array methods being methods of last resort, they can come to the fore and offer a far more structured approach to problem solving.
You drew attention to the fact that Excel offers multiple ways to solve many problems. Since the current problem is essentially one of ETL, I have also included a Power Query solution in the attached workbook. I feel it should be possible to pivot the first table without aggregation but my limited knowledge of PQ and the M language probably resulted in more steps than were strictly needed.
And here is one more Power Query variant. Assuming source file is in C:\Test folder (perhaps it's possible to connect directly to database from which source file is generated).
Another assumption is empty rows are always empty, i.e. we don't have two or more phone numbers or email addresses, etc. for the same unit.
After that give names to fields which are not specified, add index for each group and pivot.
Peter, I published it since started doing the task before seen your variant. At the same I think more variants in discussions the better, it's always useful to check an issue from different points of view.
Your strong point is analysis. That's only bit more practice to add the word "data" in front.
Thanks for that. I got close but then pivoted on the wrong column (I think I selected the ID rather than the attribute column I wished to split into fields). The outturn was an error so I reverted to plan B and used a sequence of outer joins to build the output table. Data analysis is definitely not my strong suit; I can't remember ever having a significant data set to slice and dice so the skills come slowly.