Transpose from Horizontal (with blanks) to Vertical (without blanks)

[DarkSmile]

Hi,

I need help (again ), i'm trying to transpose from horizontal to vertical (but without blanks).
Let me show you:

Test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1	Normen: Vinçotte NL
2	BRC ASDA	BRC Agents & Brokers	BRC Packaging Materials	BRC S&D	BRC Food	FSSC 22000	BRC GFCP	IFS Broker	IFS Logistics	IFS Food	IFS Wholesalte Cash & Carry	IFS Global Markets Food
3										x
4									x
5												x
6										x
7										x
8										x
9										x
10					x					x
11												x
12	x				x					x
13	x				x			x		x
14	x				x		x			x
Follow-up

Cell Formulas
Range	Formula
B3:M14	B3	=IFNA(INDEX(Certivin!$M:$M,MATCH(1,($A3=Certivin!$A:$A)*(B$2=Certivin!$C:$C),0)),"")

B14 is a great example, I have a formula

=IF(B14:M14="x",B2:M2,"")

It returns row 2, horizontally.

I'm trying now to transpose this vertically, but without the blank spaces in between them

Test.xlsx
	BD
21	BRC ASDA
22
23
24
25	BRC Food
26
27	BRC GFCP
28
29
30	IFS Food
Follow-up

Cell Formulas
Range	Formula
BD21:BD30	BD21	=TRANSPOSE(IF(BB14:BL14="","",BB14:BL14))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you again!

David

 

[DarkSmile]

Transpose won't work for what I need it to do... so never mind

 

[alansidman]

How to Unpivot Data in Excel using Power Query (aka Get & Transform)

In this tutorial, I'll show you how to unpivot data using Power Query (Get & Transform). Pivot Table needs the data in a specific format so that it can analyze it and create different views from it. In case you don't have the data in a Pivot Table ready format, you can use Power Query to unpivot...

trumpexcel.com