Wide to Long Data of Varying Length with No Macros/PivotTable

I'd appreciate if anyone can help with the following problem:

Original data

Manufacturer	Model 1	Model 2	Model 3	Model 4
Toyota	Corolla	Camry	4Runner
Honda	Civic	Accord	Odyssey	CRV

Reformatted data

Manufacturer	Model
Toyota	Corolla
Toyota	Camry
Toyota	4Runner
Honda	Civic
Honda	Accord
Honda	Odyssey
Honda	CRV

How can I make this conversion only by (array) formulas? Also important to note is that not all manufacturers have same number of models. Any help is appreciated!

Hi achpdsee,

This can be done without VBA with a newer version of Excel. There is a function built into Power Query (an add-in to 2010,2013 Excel) called UnPivot. I've written VBA to do this UnPivot but you don't want that. Sorry but there are now two different ways I know of, neither use Array formulas.

https://support.office.com/en-us/art...5-f588221c7098

achpdsee welcome to the forum.

At this time all I can come up with is an array formula solution. If you don't have thousands of rows/columns of data this might work for you. If are not aware of it array formulas are resource hungry. Too many of them will slow a workbook down.

In B6 array enter this formula and fill down until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Then in A6 enter this non array formula and fill down until you get blanks.

Formula:

Please Login or Register  to view this content.

FWIW the array constant {1;1;1;1} can be made more dynamic with additional functions. I left it this way for simplicity.

	A	B	C	D	E
1	Manufacturer	Model 1	Model 2	Model 3	Model 4
2	Toyota	Corolla	Camry	4Runner
3	Honda	Civic	Accord	Odyssey	CRV
4
5	Manufacturer	Model
6	Toyota	Corolla
7	Toyota	Camry
8	Toyota	4Runner
9	Honda	Civic
10	Honda	Accord
11	Honda	Odyssey
12	Honda	CRV
13
14
15
16
17