1

I want to create a new column of data from one column sorted by another column.

It's easiest to explain with an example. This is not actually what I'm doing, but I think it explains it well. Let's say column A has people's names in alphabetical order:
Adam
Betty
Colin
Debbie

and column B has the year of their birth:
1985
1973
1954
1973

I'd like to create a column C, which lists the people's names by their birth year:
Colin
Betty
Debbie
Adam

This should be done in a way without changing or sorting column A or B.
This should be done in a way that will automatically update column C if column A or B are edited.

Thanks! (I'm working in Excel on Windows 10 if it makes any difference.)

  • I'm unable to understand what exactly you are trying to achieve since both Name & Birth year are already available, better share the desire output !! – Rajesh S Sep 6 '18 at 9:05
  • The desired output is a the same data as column A, but in a different column and sorted (listed in a different order according to the dates in column B) – Mark Green Sep 8 '18 at 2:03
0
Cx=INDIRECT(ADDRESS(COUNTIF(B:B,">=" & Bx),1))

The birth year values must be unique.

If data starts from row with the number more than 1 - add proper number to COUNTIF() value.

  • 1
    What are Cx & Bx !! – Rajesh S Sep 6 '18 at 9:03
  • @RajeshS С1 and B1, C2 and B2, ... but not C1 and B2. – Akina Sep 6 '18 at 9:53
  • Sorry I didn't get back to my question for a couple of days. I did want it to cope with duplicate years if possible. But I'm intrigued by this function. It works (apart from the duplicate problem). But how is it pulling text from column A with no reference to column A in the formula? – Mark Green Sep 8 '18 at 1:57
0

This would be easy if the years in column B were unique, but we can adjust for duplicate years. In D1 enter:

=B1

and in D2 enter:

=B2+COUNTIF(B$1:B1,B2)*0.001

and copy downward. Column D replicates column B with duplicates "de-conflicted". This allows the MATCH() function to retrieve all the values. Finally, in C1 enter:

=INDEX(A:A,MATCH(SMALL(D:D,ROWS($1:1)),D:D,0))

and copy downward:

enter image description here

0

@Garry's Student for some reason I didn't find the formulae I needed earlier. When I couldn't get back to this page for a couple of days a little more research found me the RANK formula. (In the newest Excel, RANK is depreciated so I used the newer RANK.EQ instead. And then I thought of VLOOKUP but with a bit more searching I found INDEX MATCH which seems better, and you used that too.
So here's what I came up with and how I coped with the duplicate years: spreadsheet ranking names by year

(My formulae start at row #2 due to the headers)
Column C formula:

=RANK.EQ(B2,B$2:B$15,1)

It's important to know how the ranking works. If there are three items in position number 5, they are all given a rank of 5, and there will be no items with a rank of 6 or 7. The next position is a rank of 8.

Column D formula:
in the first cell

=C2

and in subsequent cells

=C3+COUNTIF(C$2:C2,C3)

This gets rid of the duplicate ranking. The ranking is increased by 1 if there is a duplicate rank above in the list. So those duplicate number 5s will be given the rank of 6 & 7.

Column E is simply a list of consecutive numbers, pretty easy to autofill.

Column F formula brings it all together:

=INDEX($A$2:$A$15,MATCH(E2,$D$2:$D$15,0))

Anyway I wanted to share what I had as it works and is a little different. But probably the answer from Garry's Student is better as it uses fewer columns to get there. (Possibly I could combine some and use fewer columns too with my method.)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.