Generate unique list and convert data layout

[FT00]

Hi anyone know if there is a way to get the unique value in a range? I tried using unique for range, however, the result take the "unique of combination column" rather than individual cell.

Also, i would like to know if its possible to Unpivot table using formula.

Please see my file for clearer picture.

[TMS]

This, for the first part:

=UNIQUE(TOCOL(A2:C5))

[FT00]

This, for the first part:

=UNIQUE(TOCOL(A2:C5))

Thanks! I was trying all sort of things like Vstack etc. hahaha. So funny.

[TMS]

For the second part:

=SORT(HSTACK(TOCOL(A2:C5),TRANSPOSE(TEXTSPLIT(REPT(TEXTJOIN(",",,A1:C1)&",",ROWS(A2:C5)),",",,TRUE))),{1,2},{1,1})

[FT00]

For the second part:

=SORT(HSTACK(TOCOL(A2:C5),TRANSPOSE(TEXTSPLIT(REPT(TEXTJOIN(",",,A1:C1)&",",ROWS(A2:C5)),",",,TRUE))),{1,2},{1,1})

Hi, many many thanks. Impressive.... Don't know when I can reach your 10% power.......

[FT00]

For the second part:

=SORT(HSTACK(TOCOL(A2:C5),TRANSPOSE(TEXTSPLIT(REPT(TEXTJOIN(",",,A1:C1)&",",ROWS(A2:C5)),",",,TRUE))),{1,2},{1,1})

errr... sorry, can briefy explain the logic at why do you need to textjoin then textsplit again? I have tried to understand, but.... sigh..

[TMS]

You're welcome.

Give it time



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.

[FT00]

Hopefully i can reach 50% of your level..... it will be enough for 90% of my work.

I will mark as solve once i do not have anymore question.

[HansDouwe]

Another solution for the second part:

=LET(n,A2:C5,c,TOCOL(n),SORT(HSTACK(c,INDEX(A1:C1,1+MOD(SEQUENCE(ROWS(c),,0),COLUMNS(n)))),{1,2}))

[FT00]

Another solution for the second part:

=LET(n,A2:C5,c,TOCOL(n),SORT(HSTACK(c,INDEX(A1:C1,1+MOD(SEQUENCE(ROWS(c),,0),COLUMNS(n)))),{1,2}))

The fun part in excel is there is always more than 1 way to do. Its all up to individual's logical idea. Thanks for your alternative suggestion. I will try to digest and see the logic.

[alansidman]

Alternatively with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[FT00]

Alternatively with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Hey. Thanks. But if i use power query, and if i send it to other people, it will always have the "enable content" message right?