Transpose rows to columns and sort by scores (with repetitive scores)

[anishmalhotra]

Hi All,

Thank you in advance for investing your time in an effort to resolve my query.

I have scores for multiple people in multiple subjects in rows. The result I want to achieve is (without VBA and just formula driven so that I have the real-time view anytime any score gets changed)

1. Transpose the score from Rows to column for each stream
2. sort people by scores (highest score to lowest score). Sorting even when there is a repetive score in a subject

Thank you again for your help.

Raw data


V1 v2 v3 V4 V5 V6 V7 V8 V9 V10
S1 4.00 2.63 3.20 1.20 3.87 3.87 3.87 5.20 4.47 4.97
S2 4.60 3.20 3.20 1.20 1.20 3.60 5.10 4.80 4.80 5.60
S3 3.40 2.45 3.00 1.00 1.70 2.65 4.50 4.55 4.40 5.10
S4 3.40 1.92 1.77 0.25 0.97 1.66 2.20 2.20 2.20 2.20
S5 3.40 2.00 2.00 2.00 2.00 2.00 2.00 1.97 4.40 2.27
S6 3.40 1.98 1.76 0.39 1.02 1.80 2.24 1.96 4.40 2.23
S7 1.84 2.08 2.08 2.08 2.08 2.08 2.08 2.08 2.08 2.08




Result
S1
Vs Result
V8 5.20
V10 4.97
V9 4.47
V1 4.00
V5 3.87
V6 3.87
V7 3.87
v3 3.20
v2 2.63
V4 1.20

Sorted by descending order


S2
Vs Result
V10 5.6
V7 5.1
V8 4.8
V9 4.8
V1 4.6
V6 3.6
v2 3.2
v3 3.2
V4 1.2
V5 1.2


Please use the correct version of the file

[anishmalhotra]

One more thing I forgot to mention for the result was;

It will be great to have the final result with the drop down option to select from the subjects. i.e. Formula to auto adjust based on the selection from the Subject (drop down).
It is not a must but if we can achieve that, that will be AWESOME.

Thank you!!!

[alansidman]

I was able to unpivot your data. See below. I used power query. Here is the Mcode

Please Login or Register  to view this content.

v	A	B
1	Attribute	Value
2	AC	Record-1
3	AH	Record-10
4	AC	Record-11
5	AC	Record-12
6	AB	Record-13
7	AB	Record-14
8	AB	Record-15
9	AB	Record-16
10	AH	Record-2
11	AC	Record-3
12	AB	Record-4
13	AB	Record-5
14	AH	Record-6
15	AC	Record-7
16	AC	Record-8
17	AC	Record-9

[anishmalhotra]

Thanks, Alan for all your efforts. Apologies mate, by mistake I might have attached the incorrect version of the file however the decription above was word perfect.

I have replaced the file now.

Happy to answer any questions you may have.

Cheers
Anish

[FlameRetired]

By formula.

In B18:B27

Please Login or Register  to view this content.

Copy that range and paste to E18.

Array enter this in A18 and fill down to A27. 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.

Please Login or Register  to view this content.

Copy that range and paste to D18.

[alansidman]

Using Power Query once again,

Please Login or Register  to view this content.

Resulting in:

v	A	B	C	D	E	F	G	H
1	Attribute	S1	S2	S3	S4	S5	S6	S7
2	V1	4	4.6	3.4	3.4	3.4	3.4	1.84
3	V10	4.97	5.6	5.1	2.2	2.27	2.23	2.08
4	V4	1.2	1.2	1	0.25	2	0.39	2.08
5	V5	3.87	1.2	1.7	0.97	2	1.02	2.08
6	V6	3.87	3.6	2.65	1.66	2	1.8	2.08
7	V7	3.87	5.1	4.5	2.2	2	2.24	2.08
8	V8	5.2	4.8	4.55	2.2	1.97	1.96	2.08
9	V9	4.47	4.8	4.4	2.2	4.4	4.4	2.08
10	v2	2.63	3.2	2.45	1.92	2	1.98	2.08
11	v3	3.2	3.2	3	1.76	2	1.76	2.08

Columns can now be inserted as needed and sorted as needed.

[anishmalhotra]

Thanks Dave. That works like a charm!!

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FlameRetired]

@ anishmalhotra

You are welcome. Thank you for the feedback.