Sort data in a range/table and copy entire values from a column on a row (Transpose) in another sheet

evilcreature

New Member
Joined
May 16, 2017
Messages
2
I have 2 sheets in an excel.

1. Performance Sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Region
[/TD]
[TD]Chain
[/TD]
[TD]Code
[/TD]
[TD]Q1
[/TD]
[TD]Q2
[/TD]
[TD]Q3
[/TD]
[TD]Q4
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]1
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]2
[/TD]
[TD]a
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[TD]a
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]3
[/TD]
[TD]b
[/TD]
[TD]b
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]4
[/TD]
[TD]d
[/TD]
[TD]c
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]5
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]b
[/TD]
[/TR]
</tbody>[/TABLE]

The other Sheet named "Selector"
Where I need to have 3 Drop Downs
In Cell B2, Dropdown Containing Unique Values from Region Column
In Cell B3, Dropdown Containing Unique Value from Chain Column (after applying filter based on B2)
In Cell B4, Dropdown which contains 4 values (Q1, Q2, Q2, Q4)
When the user selects an Item in B2, 1. Filter is applied in PEFORMANCE SHEET and Dropdown List Values in B3 get changed.
When Use Selects an item in B3, filter is applied in PEFORMANCE Sheet.
Finally when user selects an item from B4, the following actions should take place.

1. Sort the Performance Data as per value in CELL B4 on Selector Sheet. If B2=Q1 then sort on column heading Q1, and so on.
2. Copy the entire rows in Code Column (excluding the header)
3. Paste in "Selector Sheet" from B5 onwards

Example. if user selected R1, CC and Q4 on "Selector Sheet". Then the following would be the result on Selector Sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Choose A Region ->
[/TD]
[TD]R1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Choose A Chain ->
[/TD]
[TD]CC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Choose A Value ->
[/TD]
[TD]Q4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Resulting Codes ->
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The data here is for sample only. I have 350+ rows in actual sheet.

How can this be achieved in Excel 2016?

Regards
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top