Tranferring rows according to the lowest score into a second table

[Danny_G]

Hi!

I would like to fill a second table with rows from a first table, according to the lowest score value.

Can you help me with a formula to do this?

I've pasted a screenshot about what I would like.

I have two rows of "different values for 1 parameter(Sp.1). These values give me a final score. I need these parameters, with it's values for the lowest score in the second table. If the two scores are equal (it will happen rarely) then one of the two rows.

Is this possible only with formula?

THANK YOU VERY MUCH!!!

 

[alansidman]

Cannot manipulate data in a picture. Please reload sample data using XL2BB

 

[Danny_G]

Cannot manipulate data in a picture. Please reload sample data using XL2BB

OK.
It looks interesting but nice in preview mode...

Thank You!!!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	No.	LW	RS	CU	PA	JU	PB	SW	PL	SM	LI	Score:	Lowest Score		No.	LW	RS	CU	PA	JU	PB	SW	PL	SM	LI	Score:
2	Sp.1	0,89	0,87	0,98	0,96	1,00	0,71	0,95	1,00	1,00	0,96	47,44	35,68		Sp.1	0,93	1,00	0,98	0,91	0,89	0,63	0,89	1,00	0,90	0,96	35,68
3		0,93	1,00	0,98	0,91	0,89	0,63	0,89	1,00	0,90	0,96	35,68			Sp.2	0,89	1,00	0,70	0,91	1,00	0,69	0,89	1,00	0,90	0,96	38,50
4	Sp.2	0,89	1,00	0,70	0,91	1,00	0,69	0,89	1,00	0,90	0,96	38,50	38,50		Sp.3	0,89	1,00	0,70	0,91	1,00	0,89	0,89	1,00	0,90	0,96	39,76
5		0,93	1,00	0,89	0,91	1,00	0,69	0,89	1,00	0,90	0,96	39,90
6	Sp.3	0,89	1,00	0,70	0,91	1,00	0,89	0,89	1,00	0,90	0,96	39,76	39,76
7		0,89	1,00	0,70	0,91	1,00	0,89	0,89	1,00	0,90	0,96	39,76
Sheet2

 

[alansidman]

Load your source table to Power Query and Group to find the Max Score

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"No."}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"No."}, {{"Max", each List.Max([#"Score:"]), type number}})
in
    #"Grouped Rows"

Duplicate the original table and then merge itself.

let
    Source = Table.NestedJoin(Table1, {"No.", "Max"}, #"Table1 (2)", {"No.", "Score:"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"LW", "RS", "CU", "PA", "JU", "PB", "SW", "PL", "SM", "LI"}, {"Table1 (2).LW", "Table1 (2).RS", "Table1 (2).CU", "Table1 (2).PA", "Table1 (2).JU", "Table1 (2).PB", "Table1 (2).SW", "Table1 (2).PL", "Table1 (2).SM", "Table1 (2).LI"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Table1 (2)")
in
    #"Removed Duplicates"

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	No.	LW	RS	CU	PA	JU	PB	SW	PL	SM	LI	Score:		No.	Max	Table1 (2).LW	Table1 (2).RS	Table1 (2).CU	Table1 (2).PA	Table1 (2).JU	Table1 (2).PB	Table1 (2).SW	Table1 (2).PL	Table1 (2).SM	Table1 (2).LI
2	Sp.1	0.892857	0.87494	0.981051	0.956938	1	0.71231	0.94518	1	1	0.960799	47.44077		Sp.1	47.44076984	0.892857143	0.874939971	0.981050786	0.956937799	1	0.712310334	0.945179584	1	1	0.960799385
3		0.925926	1	0.981051	0.914913	0.886476	0.626674	0.893655	1	0.9	0.960799	35.67806		Sp.2	39.9	0.93	1	0.89	0.914913083	1	0.685896851	0.893655049	1	0.9	0.960799385
4	Sp.2	0.892857	1	0.70481	0.914913	1	0.685897	0.893655	1	0.9	0.960799	38.5		Sp.3	39.75788465	0.892857143	1	0.704809771	0.914913083	1	0.893602548	0.893655049	1	0.9	0.960799385
5		0.93	1	0.89	0.914913	1	0.685897	0.893655	1	0.9	0.960799	39.9
6	Sp.3	0.892857	1	0.70481	0.914913	1	0.893603	0.893655	1	0.9	0.960799	39.75788
7		0.892857	1	0.70481	0.914913	1	0.893603	0.893655	1	0.9	0.960799	39.75788
Sheet1