Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 411K Members
- 5,507 Online
- 466K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Sorting col large to small with duplicates and putting into a vertical table

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- :
- Excel
- :
- General Discussion
- :
- Sorting col large to small with duplicates and putting into a vertical table

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 09:41 AM

I am trying to sort the horizonal values in the first row based upon the values in the second row and have the result be similar to the vertical table to the right

I keep getting #num or it only returns one value

I had so many formulas I tried I am not putting them up so this is a blank slate for the formula

Labels:

14 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 10:15 AM

You may first create helper column to find the positions with with the large to small in totals (in P2)

=IFERROR((AGGREGATE(14,6,$B$10:$N$10*1000+COLUMN($B$9:$N$9),ROW()-ROW($P$1))/1000-INT(AGGREGATE(14,6,$B$10:$N$10*1000+COLUMN($B$9:$N$9),ROW()-ROW($P$1))/1000))*1000,"")

based on that position you may select values from other rows like

=IFERROR(INDEX($B$9:$N$9,0,$P2-COLUMN($A$9)),"")

Drag the all down till first empty cells appear.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 10:40 AM

@Sergei BaklanThanks that will work and luckily I have a place to hide the helper column close by

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 11:10 AM

@Dichotomy66 , you are welcome

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 11:20 AM

This is futures but a simple solution is coming to a computer near you!

Using modern Dynamic Arrays the formula in cell Q2

will be

= SORT( TRANSPOSE(data), 2, -1 )

I will be so thankful to get rid of direct cell referencing and the practice of filling down single-cell formulas when a simple array is all that is needed!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 11:28 AM

@Peter Bartholomew , I used this to check if "traditional" formulas are correct...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 12:44 PM

I switched the values in row 12 to just numbers

RA | 0.00 | 0.00 | 0.00 | 16.67 | 50.00 | 60.00 | 0.00 | 36.36 | 66.67 | 28.57 | 0.00 | 0.00 | 0.00 |

Now when I run the helper column the zeroes give big numbers that lead to blanks in the results column

696 |

23 |

22 |

385 |

597 |

691 |

24 |

20 |

19 |

18 |

MHH |

MMH |

HHH |

LHH |

LMH |

LMM |

There may be no real way to do this but RA stands for advantage RN neutral and RD disadvantage

If I could somehow have the results column sort row9 such that first from large to small was the ones where RA was over 50 then the ones where RN was greater than RD followed by RN=RD and lastly RD sorted so the result would in this case be

MHH LHH LMH LMM HHH MMH LLH MMM LLL LLM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 01:23 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 03:03 PM

Answered from mobile previous time, open the computer and didn't catch how row 12 (RA) affects the Total (row 10) and what they shall be. Anyway, I reworked the formula to avoid helper column as

=IFERROR( INDEX($B$9:$N$9,1, AGGREGATE(14,6, 1/($B$10:$N$10=AGGREGATE(14,6,$B$10:$N$10,ROW()-ROW($P$1)))* COLUMN($B$10:$N$10), SUMPRODUCT(--($B$10:$N$10=AGGREGATE(14,6,$B$10:$N$10,ROW()-1))))-COLUMN($A$10)), "")

and now it shall not depend on which numbers are in Total row.

If again something is wrong please attach sample file there the issue is, from the text it's bit hard to understand without the knowledge what is behind your figures.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-24-2019 02:54 AM

In the attached file, these are my formulas:

1. Q1, copied across to U1:

=INDEX($A10:$A14,

COLUMN()-16)

2. P2, copied down rows:

=IF(ROW()-1>COUNT($B$10:$N$10),"",

LOOKUP(2,1/($B$10:$N$10*(COUNTIF(P$1:P1,$B$9:$N$9)=0)=MAX(INDEX(

$B$10:$N$10*(COUNTIF(P$1:P1,$B$9:$N$9)=0),0))),

$B$9:$N$9))

3. Q2, copied down rows and across to Column U:

=IF($P2="","",

HLOOKUP($P2,$B$9:$N$14,

COLUMN()-15,0))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-29-2019 06:30 PM

SO here is the solution I came up with in the end

IN the Data set I added an infintessimal

ex. =(COUNTIF(TYPERN,BG$10)/BG$11)+(1/(COLUMN()*10^5))

Then I created 4 conditional helper columns to sort and return column numbers

Helper 1 The Best

=IFERROR(MATCH(LARGE(IF(($BC$13:$BL$13>=0.5),$BC$13:$BL$13,"glo"),ROWS($T$2:$T2)),$BC$13:$BL$13,0),"")

Helper 2 The good

=IFERROR(MATCH(LARGE(IFS(($BC$13:$BL$13<0.5)*($BC$13:$BL$13>$BC$14:$BL$14)*($BC$13:$BL$13>$BC$15:$BL$15),$BC$14:$BL$14,($BC$14:$BL$14>=$BC$13:$BL$13)*($BC$14:$BL$14>$BC$15:$BL$15),$BC$14:$BL$14,($BC$14:$BL$14=$BC$13:$BL$13=$BC$15:$BL$15),$BC$14:$BL$14,TRUE,""),ROWS($U$2:$U2)),$BC$14:$BL$14,0),"")

Helper 3 the not so good

=IFERROR(MATCH(LARGE(IF(($BC$14:$BL$14=$BC$15:$BL$15)*($BC$14:$BL$14>$BC$13:$BL$13),$BC$14:$BL$14),ROWS($V$2:$V2)),$BC$14:$BL$14,0),"")

Helper 4 I ranked them with small so the worst appear at the bottom of the list

=MATCH(SMALL($BC$15:$BL$15,ROWS($W$2:$W2)),$BC$15:$BL$15,0)

SO the first 3 columns only have a few to zero values

Then I used this formula in my results column

=IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS(ROW(),20)))),(IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS((ROW()-(COUNT($T$2:$T$11))),21)))),(IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS(((ROW())-(COUNT($T$2:$T$11))-(COUNT($U$2:$U$11))),22)))),(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS(ROW(),23))))))))))

This returned the values based on the column number in the helpers and ranked the results from best to worst

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2019 04:08 AM

I guess you have not yet looked into the formulas I suggested in the file I attached earlier. Please do and inform me of your thoughts thereon.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2019 05:03 AM

I had a go at using my normal methods (pre-dynamic arrays) but with limited success.

Because I used COUNTIFS to rank the terms, I had to introduce a helper row as the criterion range. The formula I used for the 'criteria' range was

= 100*(RA>50) + 10*(RN>RD) + (RN=RD)

[using a decimal place for each discrete sub-criterion]

The formula for ranking the terms by the criteria was

= 1 +

COUNTIFS(Criteria, ">"&Criteria) +

COUNTIFS(RD,">"&RD, Criteria, Criteria) +

COUNTIFS( k, "<"&k, Criteria, Criteria, RD, RD )

To place the results in rank order and transpose, I defined 'pointer' to be

= MATCH( TRANSPOSE(k), rank, 0)

That just leaves an array formula to output the result

= INDEX( Code, pointer )

I guess this doesn't look much like other people's solutions but it is still valid Excel and is about to get so much easier with modern dynamic arrays.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2019 06:19 AM

@TwifooI looked but your results didn't sort the subset results. And I couldnt see a simple way to modify this... The primary end goal is to have the LLL-HHH Values sorted from best to worst based on the criteria. I don't need or want any of the other data from the originating table transposed also. IN your results the one that was ^*% RA ended up down a ways instead of at the top for ex

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2019 06:37 AM - edited 04-30-2019 06:38 AM

@Dichotomy66This picture shows my final results with the helper columns to the left. The numbers to the right of the colored columns are generated from a totally different table than the one is this problem.After getting the sorts done I then used conditional formatting to get the color indicators. I have tested it now with 3 different data sets of varying size and works great

Related Conversations

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
23.2K
Views

10 Likes

13 Replies

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
24.7K
Views

1 Likes

35 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
11.1K
Views

0 Likes

9 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
137K
Views

6 Likes

28 Replies

IIS extension is not working - WAC 1909

HotCakeX
in
Windows Admin Center
on
09-25-2019
2,278
Views

0 Likes

11 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft