CrossTab list or Arrays???

Good day to all,

I have this list (small sample presented) in Excel 2013

The list needs to be transformed/transposed in order to build named ranges for a Cascading Lookup Function (up to 64 ranges - "Locations") - i.e. the cells filled with colour

Here's a small sample: Workbook1.xlsx.

Wondering what the best method is here.

Thanks!

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

Yeah, retyping data makes Hulk angry.

I did this with a few array formulas. Assuming your dataset was in A1:B8,



I plopped this into E1:

=IFERROR(INDEX($A$1:$A$8,MATCH(0,COUNTIF($D1:D1,$A$1:$A$8),0)),"") and copied over (to create headers)

E2:

=IFERROR(INDEX($A$1:$A$8,MATCH(0,COUNTIF($E$1:E1,$A$1:$A$8),0)),"") and copied down (to create location list)

F2:

=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=F$1,ROW($B$2:$B$8)-ROW(A$2)+1),ROW(A1))),"") copied down and over for the rest


Array formulas are confirmed with Ctrl+Shift+Enter to exit the cell, instead of using Enter. This will put { } around the formula, which has no effect when done manually.

Hulk Smash!

Glad I was able to help out.