Great solution! I like that highlight of the 2 columns before pivoting. NICE.

@HowToExcelBlog

5 жыл бұрын

Just like a pivot table! Divide integer in the rows area, modulo index in the columns area, and contact info in the values area 😃

Fantastic! And because I could not figure this out without this lesson, I have to award you three okey-dokeys👌👌👌. Thanks for your help and thorough tutorial.

Liked & Subscribed! Thanks!

Many thanks

Simple and easy to understand. Thank you.

@HowToExcelBlog

4 жыл бұрын

You're welcome 👍

Hi John.. thanks for the great tutorial on unstacking data with PQ. Your explanation makes it very easy to follow and replicate. I realize that you did this to show the method in PQ. In case anyone is interested in a formula based solution, I wrote this formula to do the same: =IFERROR(INDEX(tblContacts[Contact Information],COLUMNS($D$3:D3)+(ROWS($D$3:$D3)-1)*5,0),"-"). This assumes your data is in table format and named tblContacts. In your example, from cell D3, copy the formula down 5 rows and right 5 columns. If you have more or less than 5 data points, then the +X constant must be adjusted to compensate. This could be made to be dynamic, but I find that when unstacking a data set, the number of columns needed is known and therefore it is just simpler to enter in the formula as a constant, as needed. I find the formula based solution pretty handy for small data sets where PQ would be overkill and other transformation methods require more steps. Once you get the hang of it, the formula is pretty easy to write on the fly. It can be easily expanded for new data or set up to respond to new data via IFERROR to hide the #REF! errors before data is added to a larger range waiting for new data, as I designed above. I hope you and others find it a useful alternative. Thumbs up!

@Guialdelo

4 жыл бұрын

Quite a useful alternative! I tried to shorten it a bit and got this: =IFERROR(INDEX(tblContacts,COLUMN(A$1)+(ROW(A1)-1)*5,0),""). I think this one would apply for most cases as you don't need to reference any specif cell, what do you think?

@wayneedmondson1065

4 жыл бұрын

@@Guialdelo Hi Guille.. thanks for your comment. Your formula would work, as long as the table of data starts in B2 (the column heading). Unfortunately, your formula would break if you were to insert columns to the left of column B or insert rows above row 2. My formula construct is designed to anticipate those changes and would continue to work, even with the inserts described above. So, while your formula works, it is riskier, because it will fail if you or anyone alters the structure of the worksheet. I always try to build formulas that anticipate potential changes to the worksheet structure, thereby bulletproofing the result and integrity both in the present and in a potentially changing future situation. I hope this comment helps. Good luck with your Excelling :)) PS - By the way, if you have Office 365 (with dynamic array functions), you could unstack the data with a single formula, as in: =INDEX(tblContacts[Contact Information],SEQUENCE(5,5)). That would give you 5 rows and 5 columns, unstacked, just like the formula above. Note that if you wanted more flexibility, you could design the function arguments for SEQUENCE to react to inputs related to the number of data items per record and the total number of records.. such as SEQUENCE(counta(range of data)/# of fields per record, # of fields per record). Just another way to go about it. Good luck!

@Guialdelo

4 жыл бұрын

@@wayneedmondson1065 Wow, I hadn't foreseen those other scenarios. Thank you so much for the feedback. I really appreciate you'd taken your time for such a complete response. And as for the DA alternative you mentioned, however simple it is, I never would have come up with that one. Seems you're a true Excel master. Good luck buddy!

@wayneedmondson1065

4 жыл бұрын

@@Guialdelo Thanks Guille.. glad to help.. and thanks for the kind words :)) All the best to you in your future endeavors!!

@wayneedmondson1065

Жыл бұрын

@Hassan Baloch Good question. I don't know a way to do that in EXCEL without making all the data blocks the same number of rows. It can be done in Power Query, as long as there is some way to identify either the first or last item in each block. Without that.. not sure it can be done. Good luck!

Thanks! Good video.

thank you....thanks a lot to share informative knowlege

Fantastic tutorial thanks

Integer divide (row index) + modulo (column index): great way to create indexes for pivoting!

@HowToExcelBlog

4 жыл бұрын

Yes, two handy features with a lot of potential use cases.

very clever...never thought of using modulo to create an index!

@HowToExcelBlog

5 жыл бұрын

Yep, works great when you want to create a simple repeating index!

very informative!

Very helpful video, thanks

Wonderful

Nice work

Clever!!

Great and so clever! Can you help me, how can I do it but with more than 1 column?

is it possible to unstack uneven columns of data in to a row?

Really usueful! I'm able to replicate the procedure but I do not understand why this happen. This is beaceuse I do not deeply know pivot and unpivot? Can you suggest me the exercices to understand this kind of use of pivot function? Thx

Great Video! I have a similar file, but City, State, and Zip Code are in one row. How could I break those out in Power Query before using your solution?Thanks,Ken Mc

@HowToExcelBlog

5 жыл бұрын

Are they separated by a comma, or some other delimiter?

@kenmcmillan2637

5 жыл бұрын

A space between the city and State, and 2 spaces between the State and zip

@HowToExcelBlog

5 жыл бұрын

Do everything the exact same, then at the end you'll have a column that contains the city, state and zip. Then add an extra step at the end to split that column by the space delimiter.

@kenmcmillan2637

5 жыл бұрын

Thank you, I really appreciate that. Will give it a shot

@HowToExcelBlog

5 жыл бұрын

No problem! Good luck 🤞