You, sir, have just saved me a ton of work!! Thank you, this is awesome

@ExcelMoments

Жыл бұрын

Great to hear! You are welcome

This is something I've wanted to be able to do for years so thanks so much for posting. Took me a bit of thought to realise that the divisor for each of the functions should always be the number of rows in your table - would have been worth mentioning that to make it a completely generic solution.

@ExcelMoments

Жыл бұрын

Thanks for the feedback. Sometimes the things we thunk are obvious are nit too obvious, so a mention eill help to clarify that

Amazing - learned three new functions in one short video. Cheers!

@ExcelMoments

Жыл бұрын

That's great to know. Thanks for the feedback

Thank you very much. I've been having trouble with this.

Hi Victor. An awesome way to start the new year! Thanks for sharing this technique. Thumbs up!! Note - for anyone interested in a solution with a VBA user defined function, this will work Function StackRangeByColumn(rng As Range) As Variant Dim r As Long, c As Long, n As Long r = rng.Rows.Count c = rng.Columns.Count n = rng.Cells.Count Dim arr As Variant ReDim arr(1 To n, 1 To 1) Dim i As Long, j As Long n = 1 For i = 1 To c For j = 1 To r arr(n, 1) = rng.Cells(j, i).Value n = n + 1 Next j Next i StackRangeByColumn = arr End Function

@ExcelMoments

2 жыл бұрын

Thanks Wayne for the comments and the UDF, You always make one's solution better. Much appreciated

@wayneedmondson1065

2 жыл бұрын

@@ExcelMoments Cheers! My pleasure to share. I learn from you every time. Thumbs up!!

@wayneedmondson1065

2 жыл бұрын

Also, here's another way using LET and an EXCEL table named Table1. Makes it more flexible for adding data and also for empty cells where COUNTA would stumble. =LET(r,ROWS(Table1),c,COLUMNS(Table1),n,SEQUENCE(r*c),INDEX(Table1,MOD(n-1,r)+1,INT((n-1)/r)+1))

wow ! this is amazing , you are incredible 👍 👍

@ExcelMoments

2 жыл бұрын

Thanks for the feedback

with the new VSTACK function everything is much simpler: you can achieve the same result thus: =VSTACK(C5:C14,D5:D14,E5:E14,F5:F14)

@ExcelMoments

2 жыл бұрын

Absolutely Meni. This video was uploaded prior to the release of the new functions. That said, I would think TOCOL would be a shorter construct than VSTACK,

@zhongxina8427

2 жыл бұрын

LOL it's not even out yet

@ExcelMoments

2 жыл бұрын

@@zhongxina8427 oh well......it is out for some people 😁😁

@glenmitchell9980

Жыл бұрын

Ha ha - I was so please with myself for working this one out, now VSTACK is so much simpler :)

@incekarayusuf

8 ай бұрын

this still awesome, i was solving with Pivot table but was not dynamic, every time need to refresh.. not everyone got the latest version of excel, so 2018 this is the best solution

Exactly what I needed to do! Thanks a lot!

@ExcelMoments

2 жыл бұрын

You are welcome

@geethasampath3283

2 жыл бұрын

Great to make it simple & adoptable. thanks a lot.

@geethasampath3283

2 жыл бұрын

Can you please also guide me on how to get rid of blanks. I am also getting blanks in the unique line items, as my data range is from A1 to Z 60 K line items.

@ExcelMoments

2 жыл бұрын

@@geethasampath3283 it would look something like UNIQUE(FILTER(range,range"")), so give me a unique list of items that are not blank.

Thanks!

@ExcelMoments

Жыл бұрын

You are welcome!

Hello, i am having a problem with FILTERXML formula. It works for small data, but seems to give a calc error on big data. Apparently TEXTJOIN is too long. Do you know a way this would work with bigger datasets? Maybe an alternative for TEXTJOIN?

Brother you did well, but try this with TOCOL function, believe me you'll be amazed. This is way to complex and is not a dynamic formula but TOCOL is a dynamic one. May you be blessed.

@ExcelMoments

Жыл бұрын

Thank you very much for your feedback. TOCOL was not available as at the time of recording this video. In any event, i did later put up a solution using it. kzread.infoOsXFKMDgtiE and other here kzread.info/dash/bejne/hHV82LdwmcbdhKw.html

This is exactly what I’ve been looking for, however I need to do it on Google sheets and i can only get this to work on Excel. Do you know how I can do this in Google sheets?

@marius1029

3 ай бұрын

You can use Google Apps Script to create the following custom formula. "arr1" is the first column range and "arr2" is the second column range. This will return them one under the other. To use the formula just type =sequentialize(A1:A, B1:B) for example function sequentialize(arr1, arr2) { let result = []; // Iterate over the first array for (let i = 0; i // Push the value from the first array to the result result.push(arr1[i]); } // Iterate over the second array for (let i = 0; i // Push the value from the second array to the result result.push(arr2[i]); } // Return the concatenated array return result; }

Hi Victor Great Video can you please attach the files so that we can follow along regards 😃

@ExcelMoments

2 жыл бұрын

Hello Will. The workbook is in the description of the video above.

Thank you very much, it works perfect! but, in my range, I have 7 columns which represent the seven days of one week, and 30 rows, what if there are empty cells in the columns, and one column (Sunday) is usually entirely empty, I selected my range but the results are incomplete and with empty rows, I know it may be too much to ask but it would be grat to find a way to solve it. Thank you, again

@ExcelMoments

Жыл бұрын

Hello Bruno, in that case, it may be better to use a TOCOL(if you use M365) function since it contains an argument to help ignore blanks. it would look like =TOCOL(A2:G31,1,1) - where i assume A2:G31 is your 7 columns and 30 rows. Do try it and provide feedback

@farb2009

Жыл бұрын

@@ExcelMoments I'll give it a try, thank you! All my questions are payroll related we do the same procedure every week I just want to make it easy for us

This formula would do same using the FILTERXML() function: =UNIQUE(FILTERXML(""&TEXTJOIN("",,C5:F14)&"","//m"))

@ExcelMoments

2 жыл бұрын

Did you watch the part where I said there would be a 2nd part to this video? 😃😄 and maybe if you looked at the workbook, you would see a Tab called FILTERXML 😃 we would upload that video shortly

@OmisileKehindeOlugbenga

2 жыл бұрын

@@ExcelMoments I didn't see it actually.

@ExcelMoments

2 жыл бұрын

@@OmisileKehindeOlugbenga Not a problem at all. But yes, that's a creative solution. Both would have come in 1 video, but i felt the video was getting lengthy, so thought to split.

@deleolasunkanmi

2 жыл бұрын

Na people like una dey make teacher assume say person don sabi.

@ExcelMoments

2 жыл бұрын

@@deleolasunkanmi Which of the people? @oken? 😁

what if the colums are on different sheets? can i still use this? tia

@ExcelMoments

Жыл бұрын

In such a case where the columns are on multiple sheets, you may have to stack them up first(using a VSTACK) and then TOCOL can help get it into 1 column

forgot to mention, JOBS represents the range in my case, Omisile I just saw you got the same one

@ExcelMoments

Жыл бұрын

Have you seen my response to your initial comment, the one with the TOCOL, see if that works for you

If Range is over 20 i.e. C5:F24, Last Value is #Ref

@ExcelMoments

Жыл бұрын

Is your MOD using a divisor of 10 or 20 in this case?

An even better solution than VSTACK: =UNIQUE(TOCOL(C5:F14) 😎😎😎

@ExcelMoments

Жыл бұрын

Absolutely Meni. I hinted at that in my response to your initial comment on this video

I found this on google sheet: =unique({C5:C14,D5:D14,E5:E14,F5:F14}) I suppose it works on Excel too.

@ExcelMoments

Жыл бұрын

Not in that way except you are doing say UNIQUE(VSTACk(C5:C14,D5:D14,E5:E14,F5:F14)) or UNIQUE(TOCOL(C5:F14))