How do I generate a list of data one below the other!

[public]

Hello everyone

I have a small problem in an excel file, it's about generating a list like in the image below, see column "D". The data populated in the lists are just an example.

Can someone help me with an advice - how could I generate the list from column "D" using data from column "A"!!!

[Glenn Kennedy]

Try:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$30)/($A$2:$A$30<>""),ROWS(C$2:C2))),"")

in C2, copied down.

[TMS]

Try:

Please Login or Register  to view this content.

[public]

Thanks Glenn, it worked.

[TMS]

@public: and option 2 didn't work?

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[public]

@TMS, unfortunately your advice didn't work.

[TMS]

Ah, thought you had the FILTER function in Excel 2019.

[Glenn Kennedy]

2021, I think...

[TMS]

FILTER function

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones

https://support.microsoft.com/en-us/...c-4877ad80c759

[public]

According to the images below, it seems that I only have the function FILTERXML, not FILTER!!!

[Glenn Kennedy]

Microsofts www is unreliable, believe it or not! I use this one:

https://bettersolutions.com/excel/fu...-functions.htm

[sandy666]

Power Query

Result

apples

cherries

cherry

peaches

peanuts

pears

plums

Please Login or Register  to view this content.

[TMS]

Can’t argue with that. Microsoft lied. Who knew?

[TMS]

@public: If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.