19

For my current daily use of Excel, I have a sheet where hundreds of consecutive rows have the same value for a particular column. I’d like a way to quickly skip down to the next different value.

ctrl + ↓ goes to edge (i.e. the end or the next break in data), but I want to only skip identical cells.

I am looking for a keyboard command, not a macro or an extension-dependent solution.

The solution must work for a column of formula-populated cells. The formula in question:

=VLOOKUP($N19377,'Dates and Codes'!$B:$D,2,FALSE)
Tyler James Young
  • 295
  • 1
  • 2
  • 9
  • I don't think there is a native solution for this. Are you open to a macro or third-party solution that is **invoked** via keyboard command? – meatspace Feb 03 '15 at 19:28
  • @meatspace That explains why I can’t find one on any shortcut lists, but I could have sworn I’d heard of a native solution at some point and it seemed pretty reasonable that one would exist... I think my next move would be to write an AHK script for myself. – Tyler James Young Feb 03 '15 at 20:02
  • That was going to be my suggestion. – meatspace Feb 03 '15 at 20:10
  • Ctrl+Shift+| works for me - even with =vlookup. Not sure why it won't work for you. –  Feb 03 '15 at 23:28
  • I’m not sure either. I keep getting the error “No cells were found.” It works everywhere else, though, so I’ll accept your answer. Thanks! – Tyler James Young Feb 04 '15 at 15:59

2 Answers2

19

You can use the Go To feature to get to the next different cell.

  1. Highlight the column you are interested in
  2. Press Ctrl+Shift+\

The first different cell within that column will be selected.

Step 2 is the shortcut for the following steps:

  1. Press F5 to bring up the Go To menu
  2. Click Special..., click Column Differences, and then click OK.

For more information see the Excel Help Documentation: Find and select cells that meet specific conditions.

Scransom
  • 113
  • 6
  • 1
    This is an interesting route to take, but doesn’t appear to work for columns of formula-populated cells. – Tyler James Young Feb 03 '15 at 22:20
  • Strange, I tested this out on a column with formula-populated cells before posting an answer. The entire column, or at least the section of data you want to compare for differences must be selected before bringing up the Go To menu. Maybe you can update your question with the formula you're using - I can take a closer look. –  Feb 03 '15 at 22:27
  • 4
    Putting this into practice, I hold `ctrl` + `shift`, press `↓` to highlight the rest of the column and then press `|` to jump to the next change. This can all happen pretty fast, so it’s a good shortcut. Thanks! – Tyler James Young Feb 04 '15 at 16:08
  • This solution appears to move to the next *unique* value, not the next *different* value. I.e. in a list: cat, dog, dog, cat, fish; ctrl+shift+\ would move from cat to dog to fish, but moving among different values should move from cat to dog to cat to fish. – Scransom May 08 '17 at 06:04
5

Actually there is a shortcut called CTRL+\ for selecting the cells that don’t match the formula or static value in the active cell (which accomplishes the same as the method in the answer)

However, that is not the answer for what the OP asked, as it does not return a different Value for formula-determined cells, but the next different Formula.

"The solution must work for a column of formula-populated cells."

This is particularly annoying if you are using a single formula to determine different values, as I am currently doing. If the same formula gives different values, the method described in the answer does not recognise differences. The easiest way to make it work would be to Paste as Value in a separate column, and then run Ctrl-| (which is the same as Ctrl-Shift-\)

Note: there is no such combination as Ctrl-Shift-| , that is like saying Shift-! exists when it is in fact Shift-1

Sorry to seem overly critical but the question is a very pertinent one and the answer does not do it justice.

David Andrei Ned
  • 151
  • 1
  • 6
  • 1
    Welcome to Superuser! The question is open (if it was closed, you would not have been able write this answer). If you're referring to the fact that there is an accepted answer, that's determined by the user that asked the question. This is supposed to simply indicate that that answer worked for *them*. For the broken link, there should be an "edit" or "improve this answer" link just under the left side of the answer, you can use that to suggest an edit that fixes the link, if you're aware of a replacement. – 8bittree Aug 19 '16 at 19:53
  • If I ask more or less the same question because the above answer did not work for _me_, will it be marked as a duplicate question and referred back to this 'answer'? – David Andrei Ned Aug 20 '16 at 10:34
  • There would certainly be a good possibility of it being closed as a duplicate. You should definitely include a link back to this question and an explanation of why it doesn't satisfy you. However, I would recommend first seeking advice on [meta] or in [chat](http://chat.stackexchange.com/rooms/118/root-access), so you can bring it to the attention of some of the folks that would be doing the dupe voting, and see what they suggest. – 8bittree Aug 22 '16 at 13:17