Sort, reference and copy indeterminate cells

[MissT454]

One page of my workbook holds 20+ columns of over 6,000 customers and their specific information (first screen shot). Customers are grouped into areas and have a designation number. Each of my sales reps have a group of designation numbers for which they are responsible. I don't have any control of customer or sales rep designation sheets and I cannot format either of those pages. I need to create a page that will reference each sales rep on a new page then list their specific designation and customer information. There is no determined number of designations nor customers for each designation. I need to have a space between the designations on each page. I need to make sure the designations are not sorted by number but sorted by customer within each designation is required. When there are blank areas in the designation chart, I need to make sure there is no blank spaces. AND I need to do all this without the use of macros. Is this even possible?

 

[Kaper]

It shall be possible by using Power Query (Data->Get & Transform Data). You shall have one query to read sales rep table and unpivot it to create filtreable list of pairs sales rep-designation. The second query shall read main table, and join sales rep data by designation.
Then (as you want to have sales rep in L2 cell) you shall pass the content of this cell to query (named range could be a good idea to do so. Or you can have a list of sales reps somewhere in a file and make L2 a data part of a 2 cells table (header in L1 and data in L2). Then you can make a third query out of this table.

Another option (I think much tougher, because most of queries could be clicked-out in PQ environment) could be by using new Excel functions like Filter, Sort, and several others.

 

[MissT454]

this worked perfectly! thank you!