Unstacking Records with 4 Stunning Methods: Power Query vs. Dynamic Arrays vs. Functions vs. VBA

In this Amazing tutorial I show you how to unstack records: What is Unstacking?
We commonly have data in Excel where records are piled on top of each other in one column. unstacking means organizing your data in a list where each complete record is on a different row. By unstacking data, we are able to sort, filter or create pivot tables to analyze our data
You can download the exercise file and follow along by clicking on the link:

and I’ll be using four different Methods
I will show you how to do that by using an index and Match functions in a very creative method.
If you have never created a code in VBA I will guide you step by step on creating a simple code that can unstack your data
I will then unstack my records by using the magic of Power Query.
And finally, the fastest, laziest and most robust method by using Dynamic Arrays.
You can go directly to a specific method by clicking on the Timeline as follows:
1- Using Functions 01:08 min
2- Using a VBA Code 07:42 min
3- Using Power Query 22:43 min
4- Using Dynamic Arrays 29:13 min

In the VBA worksheet you will find the code I created, you can simply copy it and paste it in the visual basic editor.
Here is the VBA code another time:

Sub FixRecords()
Dim MyRecords As Integer
Dim FixRange As Range
Dim PasteCell As Integer
Dim Loopcounter As Integer
Range(“E2“). Resize(200,10).ClearContents
MyRecords = Range("B1", Range("B1").End(xlDown)).Count
Range("B1").Select
For Loopcounter = 1 To MyRecords Step 10
Set FixRange = Range(Cells(Loopcounter, 2), Cells(Loopcounter, 2).Offset(9, 0))
FixRange.Copy
PasteCell = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row + 1
Cells(PasteCell, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True
Next Loopcounter
Range(“E2”).select
End Sub

I added an Extra worksheet “Did You Watch” with links to some of my popular video Tutorials.

Finally, it motivates me to read your comments ...and don’t forget to hit the Subscribe button to be notified when new tutorials are posted… The Best is yet to come!

[eduardomunoz]

Extraordinaria explicación maestro. Saludos desde México

[eduardomunoz]

Wonderful explanation, thank you teacher. greetings from Mexico

[esperanzapadilla]

You continue to amaze me with all your video tutorials Professor! What about unstacking uneven records and the fields are not consistent in the order of appearance. I hope you can do a tutorial on how to solve that using power query.

[guillermogachuz]

Gracias por compartir tus conocimientos. Saludos

[Luciano_mp]

Nabil ... Sensational ... All the methods you've used are incredible. Except for VBA I know absolutely nothing (ahahah), the others I like a lot, but ...
Power Query is my favorite, I have Office 365, but the Dynamic function has not arrived yet for me. Thanks for the material provided. A great one from Brazil. Thank you very much!!!
I'm using Google Translate, I do not know if the translation is giving to understand.

[hankgrimes]

Each of these unstacking methods is amazing, and I certainly will be rewatching this video again and again until I get the processes down cold. My data, however, will need a bit more work, as I have a table of records that is six columns wide. I don't have a clue as to how I might unstack individual records that are already in tabular form but repeat incrementally. Can you help?!

[zaighamuddinfarooqui]

Spectacular, amazing, incredible. Mr. Nabil have any body told you that a good "Screenwriter" is hidden in you?💖 Please try to record a tutorial on extracting/un-stacking records from a "Folder" containing files e.g., Excel workbooks or text, CSV files using the Power Query. I have found good stuff on other channels but I believe that your tutorial would be of another level.

[ellukayou]

With functions, is it not better to use a unique personal data instead of the telephone number?

[SaniGarba]

Excellent. To your question, I prefer PQ method. Could I ask, is there a method to deal with unevenly stacked records, where, for instance, the records occur as 10-string then 11 and then 10 or 9? Thank you.

[majidsiddique]

Dear Nabil, The first one, if there is not fix gap between record then how i can handle this? like ( gap between first name to second name 9 row gap ), second one is if the last record phone number will be same number then the formula is returning same record. kindly tell me because my record the last number is same record then start new one

[jbtubeular]

I also have unevenly stacked data, can you help? Thanks in advance.

[GeertDelmulle]

The Dynamic Array Functions take the cake!
Now, if only my O365 version would have that already...
For now I’ll settle for PQ.

[AnbarasuAnnamalai]

Wonderful, I would like to use Power Query, since not having Office 365.