0

How can in Excel sort multiple collumns with data?

Column B = Subscribe date Column A = Subscribe name

I have multiple columns with a lot of doubles names (A) and different subscribe dates(B) How can this be sorted that all names are sorted, but the highest subscribe date is flagged as HIGHEST in column C. In this way you can see directly wich is the highest date.

Improve this question

2 Answers 2

Reset to default
5

Update

Here's a bit of magic using an array formula. This type of formula require you to press Ctrl+Shift+Enter, when entering the formula into a cell (notice the {}-brackets surrounding the formula in the screenshot when doing this). In other words - type in the formula, and the press Ctrl+Shift+Enter instead of just Enter.

Use the following formula in column C to mark the highest dates within a group in column A:

=IF($B2=MAX(IF($A$2:$A$100=$A2;$B$2:$B$100; 0));"HIGHEST";"")

The result being:

alt text

Note that the formula only handles the first 100 rows. You can change this easily if needed, though. E.g.:

=IF($B2=MAX(IF($A$2:$A$65000=$A2;$B$2:$B$65000; 0));"HIGHEST";"")

This formula will handle the first 65.000 rows.

Download example file here (download link valid until aug 24 2009).

Original answer

You could put the following formula in the C column:

=IF($B2=MAX($B:$B);"HIGHEST";"")

The result being:

alt text

Improve this answer
5
  • 1
    I'm sorry for not being clear enough. The sorting is allmost ok Bernhof, the only thing it needs to do is pick the highest subscribe date(column B) from multiple double subscribe names (A) So there can be in column A 3 x John Doe (A) and for example 4 x Mary Apple with different Subscribe dates (B) The output in C should be a flag with Highest behind the highest subscribe date with each person.
    – Chris
    Commented Aug 18, 2009 at 13:05
  • 2
    Chris, I've updated my answer with a formula that does the job.
    – Bernhof
    Commented Aug 18, 2009 at 13:43
  • @ Bernhof, That is just what I need, strangly enough, it doesn't work for me. I see #NAME? as answer. I'm a bit lost, I'm working on Excel 2007
    – Chris
    Commented Aug 18, 2009 at 14:15
  • 1
    Are you using a non-english version of Excel 2007? The functions referred to in the formula might have different names in your version of Excel. I've added a link to an example file in the post, where you should be able to read the names of the formulas when you open the file in your version of Excel.
    – Bernhof
    Commented Aug 18, 2009 at 14:29
  • @ Bernhof, I still don't understand why it didn't work, but after I paste the data in your excel sheet it worked. Thank you!
    – Chris
    Commented Aug 18, 2009 at 14:52
0

Select all the data first (if there's a lot, go the the first cell and press Ctrl-Shift-Down to quickly select the whole list). From the menus choose Data -> Sort. This will give you the option of which column to sort by first (column A in your case) and second (column B).

(EDIT: The instructions below assume that you want the highest date flagged for each unique name. For the highest overall, see Bernhof's answer.)

If you sort this way, then the latest date is just the last one for that particular name. You can show the flag using this formula in C1:

=IF(A1<>A2,"HIGHEST","")

Then just select the cell containing the formula, and use your mouse to drag the small handle (in the bottom right-hand corner of the cell) down to copy the formula do the other cells in column C.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .