Unique function from multiple columns into one single column

[Tarek78]

Hi Everyone,

I am stuck with something. I have 4 columns giving me the list of client accounts I follow. I have used unique functions from different tables.

How can I get one single columns to give the unique list of client accounts accross the 4 columns (B to E)?

P.S: my xl2BB doesn't work at the office for some reason

Thanks,

 

[Fluff]

How about

=LET(ra,ROWS(B4#),rb,ra+ROWS(C4#),rc,rb+ROWS(D4#),s,SEQUENCE(rc+ROWS(E4#)),UNIQUE(IF(s<=ra,B4#,IF(s<=rb,INDEX(C4#,s-ra),IF(s<=rc,INDEX(D4#,s-rb),INDEX(E4#,s-rc))))))

 

[Tarek78]

Thanks Fluff. Always here to help I appreciate.

Just to confirm before I try the formula.

Sometimes, I have an account in one column that won’t be in another column and vice versa. The formula I would need, need to take the unique list from all the 4 columns. Is that what your formula does?

 

[Tarek78]

Hi Fluff, I just tried your formula.

You are a genius. It works!!!

 

[Tarek78]

Hi Fluff,

I would love to fully understand your formula in order to reproduce it when needed. I am a bit lost. Could you please explain the following parts:

- rb,ra+ROWS(C4#) : why do you always add the rows count of the current + previous column?

- s,SEQUENCE(rc+ROWS(E4#): why do you use a sequence function and not the rows function you used before?

- IF(s<=ra,B4#: I am note sure to understand what it means. Why would we take the unique list from the first column if s<= ra?

- IF(s<=rb,INDEX(C4#,s-ra) : same thing here. First, I don't understand the index part and what means s-ra

- IF(s<=rc,INDEX(D4#,s-rb),INDEX(E4#,s-rc): same remark as above


As you can see I didn't understand a thing. Too genius for me I belive


Thanks again for your help

 

[Fluff]

why do you always add the rows count of the current + previous column?

Simply because it makes the formula shorter & (IMO) simpler.

why do you use a sequence function and not the rows function you used before?

Rows just give a single value (the total number of rows in the range), whereas Sequence gives an array of values.

Why would we take the unique list from the first column if s<= ra?

Whilst the array of numbers in s is <= to the number of rows in B4# we need to return the values from B4#

First, I don't understand the index part and what means s-ra

Once the numbers in s are higher than ra & <= rb we need to get the values from C4# so we subtract the value of ra from s & use that as the row number for the index.

 

[Tarek78]

I finally managed to understand your formula. Thanks so much it's a genious one! I will use it in the future

 

[Fluff]

Glad to help & thanks for the feedback.