Combine multiple columns into one Unique List - Excel
HTML-код
- Опубликовано: 4 июл 2024
- In this video, demonstrate how to combine or append multiple columns into one column using formulas and the Unique function. Unique function easily works with single rows or columns, but with multiple columns requires some creativity
Workbook can be found here:
docs.google.com/spreadsheets/...
You, sir, have just saved me a ton of work!! Thank you, this is awesome
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.
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!
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
Thanks Wayne for the comments and the UDF, You always make one's solution better. Much appreciated
@@ExcelMoments Cheers! My pleasure to share. I learn from you every time. Thumbs up!!
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))
Exactly what I needed to do! Thanks a lot!
You are welcome
Great to make it simple & adoptable. thanks a lot.
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.
@@geethasampath3283 it would look something like UNIQUE(FILTER(range,range"")), so give me a unique list of items that are not blank.
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)
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,
LOL it's not even out yet
@@zhongxina8427 oh well......it is out for some people 😁😁
Ha ha - I was so please with myself for working this one out, now VSTACK is so much simpler :)
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
wow ! this is amazing , you are incredible 👍 👍
Thanks for the feedback
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.
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.
ruclips.net/user/shortsOsXFKMDgtiE and other here ruclips.net/video/RAHvU7icwTI/видео.html
Thanks!
You are welcome!
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?
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 < arr1.length; 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 < arr2.length; i++) {
// Push the value from the second array to the result
result.push(arr2[i]);
}
// Return the concatenated array
return result;
}
what if the colums are on different sheets? can i still use this? tia
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
Have you seen my response to your initial comment, the one with the TOCOL, see if that works for you
This formula would do same using the FILTERXML() function:
=UNIQUE(FILTERXML(""&TEXTJOIN("",,C5:F14)&"","//m"))
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
@@ExcelMoments I didn't see it actually.
@@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.
Na people like una dey make teacher assume say person don sabi.
@@deleolasunkanmi Which of the people? @oken? 😁
If Range is over 20 i.e. C5:F24, Last Value is #Ref
Is your MOD using a divisor of 10 or 20 in this case?
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
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
@@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
Hi Victor
Great Video can you please attach the files so that we can follow along
regards
😃
Hello Will. The workbook is in the description of the video above.
An even better solution than VSTACK:
=UNIQUE(TOCOL(C5:F14)
😎😎😎
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.
Not in that way except you are doing say UNIQUE(VSTACk(C5:C14,D5:D14,E5:E14,F5:F14)) or UNIQUE(TOCOL(C5:F14))