Sorting Rows, then use Index/Match to return data headers

[JeffGrant]

Hi All,

Thank you in advance for the support of this forum.

I have a range where each row needs to be individually sorted, then the range headers returned.

In this range:
1. The first group of columns, 1-4, is the raw data.
2. The second group of 4 columns, 5-8, is the sorted raw data from max to min. Because each row is sorted indivually, I have used the Large function in each cell to do the sort.
if anybody has a better suggestion, how to this, I would welcome some ideas.
3. The third group of 4 columns, 9-12, I used Index/Match to return the columns headers of columns 1-4.

No problem so far.

where the issue pops up is that Index/Match will only return the first occurance. It is highly likely that I will get repeated values in the raw data.

If I use a complicated IF or LET function I may be able to return the 2nd ,3rd, 4th values appropriatly, but I do not believe that is the best way to go.

Columns 13 -16, is the expected results that should appear in columns 9 - 12.

PS, I did try to use the xl2bb to make this a bit easier to read, and give the equations, b ut my PC kept hanging. Sorry.

All suggestions are very much appreciated.

Firm

Good

Soft

Heavy

Sorted Places

Sorted Conditions

Should Be

0​

6​

2​

1​

6​

2​

1​

0​

Good

Soft

Heavy

Firm

0​

2​

4​

3​

4​

3​

2​

0​

Soft

Heavy

Good

Firm

0​

1​

5​

3​

5​

3​

1​

0​

Soft

Heavy

Good

Firm

0​

8​

1​

0​

8​

1​

0​

0​

Good

Soft

Firm

Firm

0​

1​

6​

2​

6​

2​

1​

0​

Soft

Heavy

Good

Firm

0​

5​

3​

2​

5​

3​

2​

0​

Good

Soft

Heavy

Firm

0​

3​

4​

3​

4​

3​

3​

0​

Soft

Good

Good

Firm

Soft

Good

Heavy

Firm

0​

2​

5​

3​

5​

3​

2​

0​

Soft

Heavy

Good

Firm

0​

4​

4​

1​

4​

4​

1​

0​

Good

Good

Heavy

Firm

Good

Soft

Heavy

Firm

0​

0​

4​

2​

4​

2​

0​

0​

Soft

Heavy

Firm

Firm

0​

3​

4​

3​

4​

3​

3​

0​

Soft

Good

Good

Firm

Soft

Good

Heavy

Firm

0​

0​

1​

0​

1​

0​

0​

0​

Soft

Firm

Firm

Firm

Good

Firm

Good

Heavy

0​

6​

4​

0​

6​

4​

0​

0​

Good

Soft

Firm

Firm

Good

Soft

Firm

Heavy

0​

10​

8​

2​

10​

8​

2​

0​

Good

Soft

Heavy

Firm

0​

6​

2​

2​

6​

2​

2​

0​

Good

Soft

Soft

Firm

Good

Soft

Heavy

Firm

0​

2​

0​

0​

2​

0​

0​

0​

Good

Firm

Firm

Firm

Good

Firm

Soft

Heavy

0​

2​

0​

2​

2​

2​

0​

0​

Good

Good

Firm

Firm

Good

Soft

Firm

Heavy

0​

1​

2​

0​

2​

1​

0​

0​

Soft

Good

Firm

Firm

Soft

Good

Firm

Heavy

0​

0​

2​

1​

2​

1​

0​

0​

Soft

Heavy

Firm

Firm

Soft

Heavy

Firm

Good

 

[Peter_SSs]

Try this (formulas in columns E and I only)
Note that if your goal is to get the headers then you don't actually need the Column E formula.

22 05 29.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Firm	Good	Soft	Heavy	Sorted Places				Sorted Conditions
2	0	6	2	1	6	2	1	0	Good	Soft	Heavy	Firm
3	0	2	4	3	4	3	2	0	Soft	Heavy	Good	Firm
4	0	1	5	3	5	3	1	0	Soft	Heavy	Good	Firm
5	0	8	1	0	8	1	0	0	Good	Soft	Firm	Heavy
6	0	1	6	2	6	2	1	0	Soft	Heavy	Good	Firm
7	0	5	3	2	5	3	2	0	Good	Soft	Heavy	Firm
8	0	3	4	3	4	3	3	0	Soft	Good	Heavy	Firm
9	0	2	5	3	5	3	2	0	Soft	Heavy	Good	Firm
10	0	4	4	1	4	4	1	0	Good	Soft	Heavy	Firm
11	0	0	4	2	4	2	0	0	Soft	Heavy	Firm	Good
12	0	3	4	3	4	3	3	0	Soft	Good	Heavy	Firm
13	0	0	1	0	1	0	0	0	Soft	Firm	Good	Heavy
14	0	6	4	0	6	4	0	0	Good	Soft	Firm	Heavy
15	0	10	8	2	10	8	2	0	Good	Soft	Heavy	Firm
16	0	6	2	2	6	2	2	0	Good	Soft	Heavy	Firm
17	0	2	0	0	2	0	0	0	Good	Firm	Soft	Heavy
18	0	2	0	2	2	2	0	0	Good	Heavy	Firm	Soft
19	0	1	2	0	2	1	0	0	Soft	Good	Firm	Heavy
20	0	0	2	1	2	1	0	0	Soft	Heavy	Firm	Good
Sort Rows

Cell Formulas
Range	Formula
E2:H20	E2	=SORT(A2:D2,,-1,1)
I2:L20	I2	=SORTBY(A$1:D$1,A2:D2,-1)
Dynamic array formulas.

 

[JeffGrant]

Oh my Thank you Peter. I have never used the SORTBY function, but because of your valuable assistance, I have now delete several blocks of very unnecessay formulas. I just spent an hour trying to see how i could this with VBA.

And yet, the goal was only to retirve the headers

Again, my sincere thanks..

 

[Peter_SSs]

You're welcome. Glad I had a last-minute idea to put in the note about headers only!