Formula to sort text and return reference value

S

Scott

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!
 
M

Max

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
 
M

Max

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
 
S

Scott

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!
 
S

Scott

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!
 
S

Scott

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, ...
 
S

Scott

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, ...
 
M

Max

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
 
M

Max

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
 
S

Scott

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!
 
S

Scott

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!
 
M

Max

.. 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
 
M

Max

.. 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
 
S

Scott

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 <--
 
S

Scott

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 <--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

return cells below stop when word found 1
Sort/Reference formula help needed. 1
Formula to match words 2
Rank and return Names 5
Sort/Reference formula help needed. 7
Transpose, but how? 0
Formula To Return True 5
Tree from Parent/Child Pairs 1

Top