Convert a Variable Array into a List where each Row is an Input of the Array (Tried TOCOL)

Hello Everyone,

I can do this in VBA, but this particular workbook has the requirement of being macro-free unfortunately.

My workbook will have a grid with 7 columns that users need to input countries and numbers into. The number of rows and which cells are used/left blank is totally variable as are the number of rows input.

This is an example of the input tab.

Country	Dom	For	Pass	Gen	Otr	Ptr
US	10	42	39	68
FR						200
UK		45	39	400

This is an example of what I would need to output with formulas given the above input. The formula TOCOL makes putting the numbers into the 3rd column super easy, but I'm not sure how to return the first and second columns.

Country	Type	Amount
US	Dom	10
US	For	42
US	Pass	39
US	Gen	68
FR	Ptr	200
UK	For	45
UK	Pass	39
UK	Gen	400

I considered using INDEX and some kind of COUNTA to dynamically set the reference for the number of times any given country needs to be repeated (e.g., there's four inputs for US, so US gets repeated four times).

I did think about trying to return the country and type first, then doing a lookup for the numbers, but the problem is, the countries can end up having more than one instance (which is a pain), so there maybe more than one instance of any given country AND code combo.

Any help would be greatly appreciated. Thank you!

How about

Formula:

Please Login or Register  to view this content.

One click in Power Query

Please Login or Register  to view this content.

May I ask you how you came up with using 1/0 for the FALSE components of the IF formulas?

It doesn't need to be 1/0 it can be anything that will cause an error. So you could use NA() or maybe x (not in quotes). The 2 as the 2nd argument in TOCOL tells the function to ignore errors

Originally Posted by Fluff13

It doesn't need to be 1/0 it can be anything that will cause an error. So you could use NA() or maybe x (not in quotes). The 2 as the 2nd argument in TOCOL tells the function to ignore errors

Ah, OK, that makes a lot of sense. Thank you, once again!

You're welcome & thanks for the feedback.