0

I have an Excel sheet that I use to keep my users' information.

This is a huge file and I have multiple rows and multiple columns per username. I am trying to sort the user names alphabetically without messing up the user's data that is saved in different columns.

Below is an example of my database structure to get an idea.

Please see screenshot example

It would be great if I could somehow select all the cells that belong to an specific user so when I sort the user names, their data get sorted as well.

1

A sort on column A with blank data will not work.

My suggestion would be to insert a column next to A which you can use as a temporary sort column and then delete afterwards. Quick and easy. You can then sort as per usual on column B.

To quickly populate the name values you can use the formula as per the below image and drag / copy down the spreadsheet. (I use pipe so ignore the | pipe symbol in the image) You will need to start the formula next to a value. eg. "Mark" in the example below.

Once you have populated the sort column you should copy and paste to values on that column to clear the formulas before doing your sort.

http://tinypic.com/r/wwkroi/9

=IF((A2=""),B1,A2)

  • the basic idea of support column is good, however sorting on a column which have a formula referencing to done cell doesn't seem to be a reliable solution. He should concert formulas to constants before sorting. – Máté Juhász Mar 3 '16 at 4:44
  • @MátéJuhász thanks, that's a good point. I'll edit to include. – Mark Mar 3 '16 at 4:51
  • Great solution. I actually made a small change to your suggestion and copied all the names down to fill the blank cells right below them, under column A, and then changed the color of the duplicated names to White to visually "hide" them from the list. I will make this a permanent practice on this list and I am now able to sort them by names! Thanks everyone for your feedback and suggestions. Cheers – Mehrdad Mar 3 '16 at 14:02
0

Select Column A and Column B together then press Ctrl+G

Select "Blanks"

without clicking anywhere in the sheet type the following

=(press above arrow key) then Ctrl+enter

Select Column A and Column B together again ... COPY >> Paste Spl >>> Values

then you can sort it

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.