Ask Your Question
0

Calc - Sorting text from vertical view to horizontal and grouping

asked 2018-09-22 15:20:52 +0200

nobeja gravatar image

updated 2018-09-22 15:36:01 +0200

  • For this post I attached image, because I couldn't format properly text.

image description

Is it possible with pivot table or some other way, without macros and scripts, to sort and group text from this

|Bank 1 50| |Bank 2 100| |Bank 3 150| |Bank 4 23| |Bank 3 36| |Bank 1 95| |Bank 3 56| |Bank 1 24| |Bank 2 12| |Bank 2 58|

to this

|Bank 1 Bank 2 Bank 3 Bank 4| |50 100 150 23 | |95 12 36 | |24 58 56 |

I tried some combination with pivot table, but without success.

edit retag flag offensive close merge delete

Comments

Please check if this answer helps you.

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-22 15:38:48 +0200 )edit

Thank you for suggestion but I couldn't switch from vertical to horizontal view with decroise extension, it doesn't sort data the way like it's on the image. I tried few combination in decroise extension, but without result.

nobeja gravatar imagenobeja ( 2018-09-22 16:37:05 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-09-22 22:02:30 +0200

Yes, it is possible. Assume we have such table:

Bank    Amount
Bank1   50
Bank2   100
Bank3   150
Bank4   23
Bank3   36
Bank1   95
Bank3   56
Bank1   24
Bank2   12
Bank2   58

1.Firstly, we need to determine unique "Bank" entries. Select all cells with bank entries, go to Data > More Filters > Standart Filter..., select "Not Empty" value and under Options select No duplications and Copy results to:, then choose where to copy filter result. You will get column with Bank1, Bank2, Bank3 and Bank4 entries:

Unique entries
Bank1
Bank2
Bank3
Bank4

2.Then we need to to match all amounts connected with this bank. In the cell next to Bank enter an array formula (address references from my demo spreadsheet, may differ from yours):

=TEXTJOIN(";";1;IF($A$3:$A$12=D3;$B$3:$B$12;""))

NB! To enter an array formula you need to accept it with CTRL + SHIFT + ENTER As a result you will get:

Unique entries  Amounts
Bank1   50;95;24
Bank2   100;12;58
Bank3   150;36;56
Bank4   23

3.Lets split amounts to one per cell. Select all four cells with amounts, go to Data > Text to Columns... and under Separator Options select semicolon as separator:

image description

As a result you will get such table:

Unique entries  Amounts Amounts Amounts
Bank1   50  95  24
Bank2   100 12  58
Bank3   150 36  56
Bank4   23

4.The last step is to transpose this table. Select any free cell and enter an array formula (accept it with CTRL + SHIFT + ENTER), where D11:G14 is a range from previous step:

=TRANSPOSE(D11:G14)

5.And voila:

Bank1   Bank2   Bank3   Bank4
50  100 150 23
95  12  36  
24  58  56

I have also attached demo spreadsheet - Data_sort.ods

P.S> I am not strong with Pivot Table functionality, I believe there should be even easier solution. I just could not get Data Fields without applying any functions to them.

edit flag offensive delete link more

Comments

Thank you for answer.

nobeja gravatar imagenobeja ( 2018-09-23 13:04:55 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-09-22 15:20:52 +0200

Seen: 75 times

Last updated: Sep 22 '18