Formula to sort text and return reference value
 

I have a column (Column A) of text data in one column with anywhere between 1
and 144 rows of unique text data. Next, in Column B, I have another series
of text data with the same amount of rows, but contains duplicate text fields.

In Column C, I want to sort the data in Column B and return the associated
data in Column A. For one more trick, see below...

For example:

---A------B---
Name1___C
Name2___A
Name3___D
Name4___B
Name5___D
Name6___A
Name7___C
Name8___A
Name9___C
Name10__D
Name11__B
Name12__B

Now in Columns C and D I want the output to be:
---C------D
Name2___A
Name6___A
Name8___A
Name4___B
Name11__B
Name12__B
Name1___C
Name7___C
Name9___C
Name3___D
Name5___D
Name10__D

Now, finally, here's the tricky one. In Columns E and F, I want to sort the
range in an "ABCD" format. Like so:

E---F---
Name2___A
Name4___B
Name1___C
Name3___D

And so on...

What do I use to return this?

Thanks!

Formula to sort text and return reference value
 

For your 1st question, you could use essentially the same tiebreaker set-up
concept as I had responsed in one of your earlier similar threads a few days
ago (Incidentally, you should close off the discussions over there, in that
thread. Could you?)

Source data is assumed in A1:B1 down
In C1: =IF(B1="","",CODE(UPPER(B1))+ROW()/10^10)
In D1: =INDEX(A:A,MATCH(SMALL($C:$C,ROWS($1:1)),$C:$C,0))
Copy D1 to E1. Select C1:E1, copy down to the last row of source data. Hide
away col C. Cols D and E returns an automatic alpha/ascending sort of cols A
and B, by the single letter grades in col B. Ties are fully catered for, with
returns in the same relative order that they appear within the source.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Scott" wrote:
I have a column (Column A) of text data in one column with anywhere between 1
and 144 rows of unique text data. Next, in Column B, I have another series
of text data with the same amount of rows, but contains duplicate text fields.

In Column C, I want to sort the data in Column B and return the associated
data in Column A. For one more trick, see below...

For example:

---A------B---
Name1___C
Name2___A
Name3___D
Name4___B
Name5___D
Name6___A
Name7___C
Name8___A
Name9___C
Name10__D
Name11__B
Name12__B

Now in Columns C and D I want the output to be:
---C------D
Name2___A
Name6___A
Name8___A
Name4___B
Name11__B
Name12__B
Name1___C
Name7___C
Name9___C
Name3___D
Name5___D
Name10__D

Now, finally, here's the tricky one. In Columns E and F, I want to sort the
range in an "ABCD" format. Like so:

E---F---
Name2___A
Name4___B
Name1___C
Name3___D

And so on...

What do I use to return this?

Thanks!

Formula to sort text and return reference value
 

Max, I closed the previous thread.

I tried the formula for this situation. It seems to sort Column A, but
Column B is the one I want sorted, with Column A being referenced off of
Column B.

Also, how do I tackle the "ABCD" format?

Thanks!



"Max" wrote:

For your 1st question, you could use essentially the same tiebreaker set-up
concept as I had responsed in one of your earlier similar threads a few days
ago (Incidentally, you should close off the discussions over there, in that
thread. Could you?)

Source data is assumed in A1:B1 down
In C1: =IF(B1="","",CODE(UPPER(B1))+ROW()/10^10)
In D1: =INDEX(A:A,MATCH(SMALL($C:$C,ROWS($1:1)),$C:$C,0))
Copy D1 to E1. Select C1:E1, copy down to the last row of source data. Hide
away col C. Cols D and E returns an automatic alpha/ascending sort of cols A
and B, by the single letter grades in col B. Ties are fully catered for, with
returns in the same relative order that they appear within the source.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Scott" wrote:
I have a column (Column A) of text data in one column with anywhere between 1
and 144 rows of unique text data. Next, in Column B, I have another series
of text data with the same amount of rows, but contains duplicate text fields.

In Column C, I want to sort the data in Column B and return the associated
data in Column A. For one more trick, see below...

For example:

---A------B---
Name1___C
Name2___A
Name3___D
Name4___B
Name5___D
Name6___A
Name7___C
Name8___A
Name9___C
Name10__D
Name11__B
Name12__B

Now in Columns C and D I want the output to be:
---C------D
Name2___A
Name6___A
Name8___A
Name4___B
Name11__B
Name12__B
Name1___C
Name7___C
Name9___C
Name3___D
Name5___D
Name10__D

Now, finally, here's the tricky one. In Columns E and F, I want to sort the
range in an "ABCD" format. Like so:

E---F---
Name2___A
Name4___B
Name1___C
Name3___D

And so on...

What do I use to return this?

Thanks!

Formula to sort text and return reference value
 

I was able to get the first request to work. Still having trouble with the
"ABCD" format though. I want it sorted as a repeating series. For example,
A, B, C, D, A, B, C, D, ...

Formula to sort text and return reference value
 

For your 2nd "tricky" question,
viz: .. I want to sort the range in an "ABCD" format

This set-up reads the automated alpha sort results derived earlier in cols D
and E
(as per my response to your 1st question)

You could place this in F1:
=INDEX(D:D,MOD(ROWS($1:1)-1,4)*3+1+INT((ROWS($1:1)-1)/4))
Copy F1 to G1, fill down to return the desired "ABCD" results, viz:

Name2 A
Name4 B
Name1 C
Name3 D
Name6 A
Name11 B
Name7 C
Name5 D
Name8 A
Name12 B
Name9 C
Name10 D

Success? Celebrate it, click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Scott" wrote:
I have a column (Column A) of text data in one column with anywhere between 1
and 144 rows of unique text data. Next, in Column B, I have another series
of text data with the same amount of rows, but contains duplicate text fields.

In Column C, I want to sort the data in Column B and return the associated
data in Column A. For one more trick, see below...

For example:

---A------B---
Name1___C
Name2___A
Name3___D
Name4___B
Name5___D
Name6___A
Name7___C
Name8___A
Name9___C
Name10__D
Name11__B
Name12__B

Now in Columns C and D I want the output to be:
---C------D
Name2___A
Name6___A
Name8___A
Name4___B
Name11__B
Name12__B
Name1___C
Name7___C
Name9___C
Name3___D
Name5___D
Name10__D

Now, finally, here's the tricky one. In Columns E and F, I want to sort the
range in an "ABCD" format. Like so:

E---F---
Name2___A
Name4___B
Name1___C
Name3___D

And so on...

What do I use to return this?

Thanks!

Formula to sort text and return reference value
 

See my other response for the 2nd, "ABCD" repeating sort part of it (my
response crossed your reply here)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Scott" wrote:
I was able to get the first request to work. Still having trouble with the
"ABCD" format though. I want it sorted as a repeating series. For example,
A, B, C, D, A, B, C, D, ...

Formula to sort text and return reference value
 

I'm returning semi-successful information. Maybe if I understand a few
things I can fix it. First off, I was able to get your formula to return the
results in the test range, but unable to get it to work for my actual
database range. Now my full database starts in row 5. I believe that is the
reason behind why I'm receiving "0"s in some of the referenced cells.

Secondly, in your formula, what does the number "4" refer to? Is it the
number of letters in the "ABCD" format? I used the "ABCD" format as an
example, however in my data range it can range anywhere between A to L, so
that's 12 letters. Would that make any difference?

Thanks again!



"Max" wrote:

For your 2nd "tricky" question,
viz: .. I want to sort the range in an "ABCD" format

This set-up reads the automated alpha sort results derived earlier in cols D
and E
(as per my response to your 1st question)

You could place this in F1:
=INDEX(D:D,MOD(ROWS($1:1)-1,4)*3+1+INT((ROWS($1:1)-1)/4))
Copy F1 to G1, fill down to return the desired "ABCD" results, viz:

Name2 A
Name4 B
Name1 C
Name3 D
Name6 A
Name11 B
Name7 C
Name5 D
Name8 A
Name12 B
Name9 C
Name10 D

Success? Celebrate it, click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Scott" wrote:
I have a column (Column A) of text data in one column with anywhere between 1
and 144 rows of unique text data. Next, in Column B, I have another series
of text data with the same amount of rows, but contains duplicate text fields.

In Column C, I want to sort the data in Column B and return the associated
data in Column A. For one more trick, see below...

For example:

---A------B---
Name1___C
Name2___A
Name3___D
Name4___B
Name5___D
Name6___A
Name7___C
Name8___A
Name9___C
Name10__D
Name11__B
Name12__B

Now in Columns C and D I want the output to be:
---C------D
Name2___A
Name6___A
Name8___A
Name4___B
Name11__B
Name12__B
Name1___C
Name7___C
Name9___C
Name3___D
Name5___D
Name10__D

Now, finally, here's the tricky one. In Columns E and F, I want to sort the
range in an "ABCD" format. Like so:

E---F---
Name2___A
Name4___B
Name1___C
Name3___D

And so on...

What do I use to return this?

Thanks!

Formula to sort text and return reference value
 

.. my full database starts in row 5

Then you could adjust the index portion of it to reflect
Use in F5:
=INDEX(D$5:D$100,MOD(ROWS($1:1)-1,4)*3+1+INT((ROWS($1:1)-1)/4))
Copy F5 to G5, fill down. Adapt the range D$5:D$100 to suit the actual
extents of the derived data in cols D and E.

The "4" in the MOD & INT parts of the expression are associated with the 4
elements in the repeating structure ABCD, while the "*3" multiplier (for the
MOD) is related to the number of repeating rows for each letter/element in
col E, viz: AAA,BBB, etc

If you have say, 3 elements (ABC), and each repeats 4 times in col E ie:
AAAA,BBBB,CCCC, you need to change the "4" to "3", and the multiplier to "*4"
viz the expression used in F5 would then be:
=INDEX(D$5:D$100,MOD(ROWS($1:1)-1,3)*4+1+INT((ROWS($1:1)-1)/3))

Note that col E's derived data (the letter grades) must of course be regular
in structure throughout, eg: AAA,BBB,CCC,DDD. It won't work if its irregular,
for eg: AAAA,BB,CCC,DDD.

Trust the above clarifies it for you to get it up n going over there.
Moments to saviour? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Scott" wrote:
I'm returning semi-successful information. Maybe if I understand a few
things I can fix it. First off, I was able to get your formula to return the
results in the test range, but unable to get it to work for my actual
database range. Now my full database starts in row 5. I believe that is the
reason behind why I'm receiving "0"s in some of the referenced cells.

Secondly, in your formula, what does the number "4" refer to? Is it the
number of letters in the "ABCD" format? I used the "ABCD" format as an
example, however in my data range it can range anywhere between A to L, so
that's 12 letters. Would that make any difference?

Thanks again!

Formula to sort text and return reference value
 

Max,

I'm still running into some issues here on this particular formatting. But,
I'm beginning to change my direction - I think. Basically in our range, we
have text values in the first column, numeric values in the second column. I
have anywhere between 1 and 144 unique rows of text data, some with possible
duplicate numeric values associated with it. Every text data cell will have
a numeric value associated with it in the second column.

Basically what I want to do is go through the list and group together (in
groups of 2, 3, or 4 max) text values with a balanced numeric value
associated with it. Thus, that was my direction towards the ABCD format.

For instance.
Text1 has a value of 10
Text2 has a value of 20
Text3 has a value of 30
Text4 has a value of 40
Text5 has a value of 50
Text6 has a value of 60
Text7 has a value of 70
Text8 has a value of 80

What I want to do with those 8 values is to create 2 evenly matched groups
of 4.
So I want the output to be:
Text10 = 10
Text30 = 30
Text60 = 60
Text80 = 80
Total Pts = 180 <-- notice the even, or at least close to even, total points
between groups.

Text20 = 20
Text40 = 40
Text50 = 50
Text70 = 70
Total Pts = 180 <--



"Max" wrote:

.. my full database starts in row 5

Then you could adjust the index portion of it to reflect
Use in F5:
=INDEX(D$5:D$100,MOD(ROWS($1:1)-1,4)*3+1+INT((ROWS($1:1)-1)/4))
Copy F5 to G5, fill down. Adapt the range D$5:D$100 to suit the actual
extents of the derived data in cols D and E.

The "4" in the MOD & INT parts of the expression are associated with the 4
elements in the repeating structure ABCD, while the "*3" multiplier (for the
MOD) is related to the number of repeating rows for each letter/element in
col E, viz: AAA,BBB, etc

If you have say, 3 elements (ABC), and each repeats 4 times in col E ie:
AAAA,BBBB,CCCC, you need to change the "4" to "3", and the multiplier to "*4"
viz the expression used in F5 would then be:
=INDEX(D$5:D$100,MOD(ROWS($1:1)-1,3)*4+1+INT((ROWS($1:1)-1)/3))

Note that col E's derived data (the letter grades) must of course be regular
in structure throughout, eg: AAA,BBB,CCC,DDD. It won't work if its irregular,
for eg: AAAA,BB,CCC,DDD.

Trust the above clarifies it for you to get it up n going over there.
Moments to saviour? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Scott" wrote:
I'm returning semi-successful information. Maybe if I understand a few
things I can fix it. First off, I was able to get your formula to return the
results in the test range, but unable to get it to work for my actual
database range. Now my full database starts in row 5. I believe that is the
reason behind why I'm receiving "0"s in some of the referenced cells.

Secondly, in your formula, what does the number "4" refer to? Is it the
number of letters in the "ABCD" format? I used the "ABCD" format as an
example, however in my data range it can range anywhere between A to L, so
that's 12 letters. Would that make any difference?

Thanks again!

Formula to sort text and return reference value
 

Scott,
Suggest you start a new thread for your new query
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---