Excell formula help

Copper Contributor

Hi, I would like help to merge multiple rows and columns into one continuous row. Eg - all data to be spread across A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1 etc FROM: B4,C4,A6,B6,A7,B7,B8,A9,B9,A11,B11 - Is there an easy formula to do this? Thank you

24 Replies

@Peter Bartholomew   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.

@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.

@Peter Bartholomew 

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.

@Sergei Baklan 

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.

@Peter Bartholomew 

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.