Excel 2007 : Sort 2 Columns using Large Formula

[MarvinP]

I have a small 2 column range that I need to sort without using Data -> Sort.

I've watched DonkeyOte and a few others do it with the Large function but there was a second step involved.

Some of the values may be repeated but the string next to them needs to follow the numbers.

See Attached.

I'm still learning.

[teylyn]

Hi MarvinP,

the first bit is easy: In D5 and copied down

=LARGE($A$5:$A$12,ROW(A1))

The tricky bit is to get the duplicate 0 assigned to the correct value in column B.

One way is with a helper column. For example in C5 and copied down

=COUNTIF($A$5:A5,A5)

Then in E5 and copied down

=INDEX($B$5:$B$12,MATCH(G5&COUNTIF($G$5:G5,G5),INDEX($A$5:$A$12&$C$5:$C$12,0),0))

There are probably other ways, involving array formulas, but the helper column keeps things easy and array-formula free.

[MarvinP]

Hi teylyn,

This seems to work (you knew it would). Now I have to figure out why.
I get the Large part.
The countif works from the top down giving how many of that number are it and above. OK
How about using words for the last formula.

[DonkeyOte]

In terms of returning strings where duplicate criteria matches without use of helpers....

If strings being returned (B5:B12) are themselves unique:

Please Login or Register  to view this content.

If strings can themselves be duplicates then:

Please Login or Register  to view this content.

On a final note:

Please Login or Register  to view this content.

IMO you should avoid the above practice (use of ROW(A1)) and refer only to appropriate precedents, eg:

Please Login or Register  to view this content.

Reason being should you for ex. decide to latterly delete Row 1 then even though this row is technically not relevant to the calculations (precedent range of interest being A5:B12) your formulae will cease to work correctly
(using ROWS(A$5:A5) the formula will be unaffected - given the reference will simply alter to A$4:A4 resulting still being 1)

If you want to use ROW then you should use ROW(A5)-ROW(A$5)+1 but you may as well use ROWS for obvious reasons.

How about using words for the last formula

Can you clarify ? Do you mean - what happens if you have strings in A5:A12 rather than numbers ?

[teylyn]

Hi MarvinP,

How about using words for the last formula.

I'm a fan of plain language, so here is my explanation for the formula I posted above.

=INDEX($B$5:$B$12,MATCH(G5&COUNTIF($G$5:G5,G5),INDEX($A$5:$A$12&$C$5:$C$12,0),0))

The red bit: How how often does the value in G5 occur in the column up to the current row. If only once, it will be a 1. If we have duplicates, it will be a higher number. In row 10 it will be 2, in row 11 it will be 3, etc., because the value 0 has been counted three times from G5 up to the current row 11.

OK. Let's say the result of that Countif is "some number"

=INDEX($B$5:$B$12,MATCH(G5&<some number>,INDEX($A$5:$A$12&$C$5:$C$12,0),0))

Now the formula is performing a MATCH. The term that is searched for is a concatenation of the value in G5 and the <some number> the countif has produced. In row 5 this will be 910&1, resulting in the text string "9101". In row 10, the result of the concatenation will be 0 & 2 = "02" as the search string.

=INDEX($B$5:$B$12,MATCH(G5&COUNTIF($G$5:G5,G5),INDEX($A$5:$A$12&$C$5:$C$12,0),0))

Match is looking for that string in the INDEX the formula constructs by combining two columns. Column A is concatenated with column C, row by row, to arrive at an array of text strings.

A5&C5
A6&C6
A7&C7
etc.

This is what the Index array looks like after the concatenation:

"6701"
"5001"
"01"
"02"
"9101"
"03"
"6101"
"04"

So, now we have a Match formula, looking for a text string like "9101" (in the formula in E5) or "02" (E10) in the array above. In row 5 the Match formula will return a 5 and in row 10 the Match will return a 6. Let's call the result of the match formula "match number"

=INDEX($B$5:$B$12,<match number>)

That looks much more digestible. In row 5, match number is 5, so Index will return the content of the fifth cell in the array B5:B12 = ILAIA 06
In row 10, match number is 6, so Index will return the sixth cell in the array = LOVE 12

With nested formulas like these, you need to resolve them from the inside out.

A great help for this is the Evaluate Formula tool, found on the Formulas ribbon (or in my QAT, coz I can't live without it).

Does that help?

cheers,

[DonkeyOte]

Being the pedant I am - if you opt to use concatenation it is always a good idea to use a delimiter thereby reducing risk of false positives (however unlikely they may seem)

[MarvinP]

Teylyn -
Your explaination was perfect. Better yet was the Evaluate suggestion which I rairly use. Perhaps it is time to cozy up to it.

I normally figure out what's going on by just looking at it.

Now I need to look at DonkeyOte's to see what he's been smokin to see if it makes more sense to me.

[teylyn]

Marvin, why are you up at this time of day? It's ungodly in NZ already, but Seattle...??

[MarvinP]

Hi DO,
Firstly - I'm glad all the strings are unique so I don't have to Evaluate your second equation.

Secondly - My brain hurts as I make new boxes in it and start to understand Array Formulas, like "$A$5:$A$12=$D5" is not a single answer but a bunch (ok an array) of 1s and 0s.

Thirdly - Then you do an Array to see if the string has been used above with a #N/A. This was a real stretch for me because you start one line above where I think you should. (what happens if you are already at the top?)

Forthly - Then you do an AND using "*" for Array's (reminded me of WFF and Proof game from the past) to pick the correct string from the INDEX.

Fifthly - You suggest teylyn had a weak Row(A1) and could be better if s/he did a relative ref using Rows(A$5:A5).

I understood all of it until you crossed teylyn. This is my rule number 1, don't cross teylyn.

BTW - I had to look up pedant http://www.merriam-webster.com/dictionary/pedant and realized you are a (male?) teacher, based on my new learning. And I can now refer to you as just "he" and not "s/he"

Thanks again to both of you.

[MarvinP]

I'd been working on two Help problems. One related to this thread. They kept bounding around in my head and I wanted to get them off my plate. It was 3am when I decided to finish them up.

You are correct about the time differences. 6:30am now that my brain has Arrays boxed. I still want to package the answers off to the wine guy in Chile. The other one is on his own.

[DonkeyOte]

Then you do an Array to see if the string has been used above with a #N/A. This was a real stretch for me because you start one line above where I think you should. (what happens if you are already at the top?)

If there was no row above the approach would not work.

Fifthly - You suggest teylyn had a weak Row(A1) and could be better if s/he did a relative ref using Rows(A$5:A5).

I understood all of it until you crossed teylyn. This is my rule number 1, don't cross teylyn.

The point I made is just my repeating a point made to me several times in the past - for the very same reason.
It is true though that by referencing the row above in my ISNA test I am guilty of the same (ie referring to cells that are not of "real" interest - ie header row)

BTW - I had to look up pedant http://www.merriam-webster.com/dictionary/pedant and realized you are a (male?) teacher, based on my new learning. And I can now refer to you as just "he" and not "s/he"

In this context I think the definition of 2b from your link is the more appropriate:

2b: one who is unimaginative or who unduly emphasizes minutiae in the presentation or use of knowledge

I think most would accept that the above is an accurate reflection of my posting style

[MarvinP]

DO,

I'm learning a lot from your Array prowess. I'm starting to understand a little more than half of your suggestions. Give me a few more months and I'll be up to 80%

I'm still amazed at the "blow up the spaces by the length of the string" trick you pulled a few months back.

If I wasn't learning anything myself, answering questions wouldn't be as much fun.

[DonkeyOte]

@MarvinP - rest assured that very few (read: virtually none) of the solutions you see on the board are "original".

The greater majority of us are simply regurgitating / modifying techniques we've picked up from others in the past

I think all would agree with you that answering questions is the quickest way to learn whilst also being quite good fun (depending on who asked the question of course).

[Markica85]

Hi guys, I need your help. I am sorting two columns of numbers, and I have two formulas that do it.
Data looks like this:
5 0
4 0
3 0
5 1
2 1
0 0
5 0

Formulas are written in columns D and E and they are similar to yours but use small function instead. My problem is when I have more than one same number, program sorts them in order they were written, I need them sorted so number with number one next to it in column B is written first. I hope you could understand me, I will try to upload this workbook too.

I need it to look like this:
0 0
2 1
3 0
4 0
5 1
5 0
5 0

[DonkeyOte]

@Markica85,

Unfortunately:

Your post does not comply with Rule 2 of our Forum RULES.
Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[chemguy]

Thanks guys.... this thread has been really useful to solve my problem