Excell formula help

PeterBartholomew1 

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.

SergeiBaklan 

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.

PeterBartholomew1 

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.

mathetes 

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.

PeterBartholomew1   That's a great solution too, Peter. I love how Excel has multiple ways to solve a problem. Your use of INDEX is a bit more elegant than mine with INDIRECT, in that you needed no "helper" columns or rows. I could have written that as a single formula (in retrospect), but was also trying to keep it understandable (as much as possible) for the original poster.....

Anyway, thanks for sharing that. I am also loving the learning that goes on in sharing solutions to problems people bring to these boards.

ttlj2 

A similar solution but I happened to choose to use INDEX to return data from column B.

= INDEX(data, 12*(SNum-1) + {1,4,5,6,8} )

The result is in the form of a single array formula.

Using Office 365 it was a single dynamic array that spilt from the formula in B2.

SNum is a serial number, I calculated using 

= SEQUENCE( ROWS(data)/12 )

but then reduced it to values.

By the way, ttlj2,  I do still think that you should be able to get that other program your company uses to export the data in a true Excel format, rows with a header just as we're trying to create here. Most programs DO have that capability.

I know the formulas I created will do the job, but even though I had fun creating those formulas, that IS doing it the hard way. 🙂

OK,ttlj2  I've done the first ten records and copied the formula that can (or should be able to) handle up to nearly 2,000.

What I suggest you do is this. First, make a copy of your original file.

Second, in one of your copies, rename the tab that contains the records in your original workbook...rename it to "RawData"

Third, copy my "Converter" tab to that workbook.

To do this last,

1. have both workbooks open
2. left click on "Converter"
3. select "Move or Copy"
4. pick the name of the OTHER workbook (your original)
5. down at the bottom of the dialog box click on "Create a copy"  [this saves the original, in case something goes wrong]

If all goes as I think it will, that will be the solution. If it doesn't work, let me know.