Sorting cells in one column with dependent information in other columns

**shntenchi** · 06-28-2015, 03:36 AM

Hi everyone, I'm new here. However, I've been looking for a solution for this problem of mine for awhile now and figured that I either have a unique problem, or that I'm not wording my searches correctly. I hope it's the latter. Anyways, I'm going to attempt to explain the situation and what I need done with an example afterwards.

I would like to be able to sort the information for column A in alphabetical order. However, I want column B-D to sort accordingly so that it coincides with column A. In this example, I threw in random names, birth dates, cities, and state.

A B C D
1 Sam 05/25/1976 NY NY
2 John 11/29/1985 LA CA
3 Bob 02/06/1963 SF CA
4 James 08/13/1989 KC KS

After highlighting column A and hitting Sort A-Z, I would like to see the following:

A B C D
1 Bob 02/06/1963 SF CA
2 James 08/13/1989 KC KS
3 John 11/29/1985 LA CA
4 Sam 05/25/1976 NY NY

As you can see, column A is now in alphabetical order, with their respective birth dates, cities, and states. And if I can throw in another wrench or two into this, the actual values I'm working with will have blanks and repeated values. (Pretend there's multiple people with the same birthday, or from the same city, or from the same location).

(And as I scroll down... I realize I could have uploaded a mock sheet... oh well....)

EDIT:
As Glenn helped me realize, I forgot to mention that I'm looking for other ways to sort instead of highlighting the first value from column A and highlighting all involved data. I know that this is the easiest way to do so, however, on the sheet I'm actually working on, I'm constantly adding values to the sheet, where sometimes I will only get information for column A. Or, I may get updated information for one of the values. For example, instead of John, it's really Jonathan, to which I will have to re-sort.

The major issue I'm trying to solve is that I've caught myself (a few times already), forgetting to highlight the other columns to which throws off everything. But I won't catch my mistake until days later. (Thankfully it's a personal project) I was wondering if cells can be locked/linked to each other so that whenever a value from column A moves, B (and so forth) moves with it without having to highlight everything.

**Glenn Kennedy** · 06-28-2015, 03:55 AM

Select ALL the data, not just column A. (on PC) use DATA/SORT&Filter/Sort/Column A/A-Z

**shntenchi** · 06-28-2015, 04:04 AM

Thanks Glenn, you helped me realize that my post was incomplete (which I'll edit). I know that this is the easiest way to do so, however, on the sheet I'm actually working on, I'm constantly adding values to the sheet, where sometimes I will only get information for column A. Or, I may get updated information for one of the values, for example, instead of John, it's really Jonathan, to which I will have to re-sort. However, I've caught myself (a few times already), where I forget to highlight the other columns to which throws off everything. But I won't catch my mistake until days later. (Thankfully it's a personal project) I was wondering if cells can be locked/linked to each other so that whenever a value from column A moves, B (and so forth) moves with it.

**Glenn Kennedy** · 06-28-2015, 04:10 AM

I'm not sure if I follow you. there's not a lot that you can do to make things error-proof. i suppose you could sort using a formula onto a second sheet; but then you'd have to remember to change data ONLY on the first sheet...

**shntenchi** · 06-28-2015, 04:23 AM

Hmm, I guess the easiest way to explain it is that I'll forget once in awhile to highlight everything and instead only highlight column A. Obviously only column A will be rearranged while the values of the other columns will remain where they're at. I won't find this mistake until later on. I was wondering if there was a fool proof way by linking the data together so that if a value from column A moves, related data will also move without me having to highlight everything.

**Glenn Kennedy** · 06-28-2015, 04:34 AM

I think you may be hoping for too much... on the Mac - when you select just one column & apply A-Z sort, - do you not get a dialogue box coming up to ask you if you want to sort the adjacent data, too?

**shntenchi** · 06-28-2015, 05:36 AM

Unfortunately no. I guess I'll just have to be more careful and audit myself every so often, unless anyone else has a solution or suggestion.

**newdoverman** · 06-28-2015, 12:59 PM

This can be done with 2 helper columns:
Helper column 1 enter in E1 and fill down:

Formula:

Please Login or Register  to view this content.

Helper column 2 enter the numbers 1 to 4 (number to the number of entries you have in column A)

My helper column 2 is column H. Enter this in I1 and fill down and across.

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.