Excel - Separate values from column into two columns

Question

I have two columns that look like so:

Data A  | Data B
-----------------
Type1   |  23
Type2   |  19
Type1   |  3
Type2   |  9
Type1   |  9

I would like to separate it into:

Type1 | Type2
-------------
23    |  19
3     |  9
9     |

How can this be generally done in Excel?

player0 · Accepted Answer · 2019-03-24 19:22:29Z

1

...meanwhile in Google Sheets...

={"Type1","Type2";{FILTER(B1:B,A1:A="Type1"),FILTER(B1:B,A1:A="Type2")}}

={"Type1","Type2"; 
  ARRAYFORMULA(TRANSPOSE(SPLIT(TRANSPOSE(SPLIT(
  JOIN("♦",FILTER(B1:B,A1:A="Type1"))&"♥"&
  JOIN("♦",FILTER(B1:B,A1:A="Type2")),"♥")),"♦")))}

edited Mar 24, 2019 at 19:22

answered Mar 24, 2019 at 18:36

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JenniferNightly Over a year ago

This works great if the two types have equal amount of values, which mine unfortunately doesn't... I glanced over this and apologize for not having mentioned it. :(

JenniferNightly Over a year ago

You're brilliant. Thank you so much for the help!

Collectives™ on Stack Overflow

Excel - Separate values from column into two columns

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related