# How to Rearrange OR sort Number of columns based on one column

• ### Question

• Dear Sir,

Good evening. I need help from you people to do the following arrangement in excel. There are two sets of data in the same sheet. First Set is column A only. Seond set is column C to I. Now the second set of data need to be sorted/arranged like column A (column A and column D should match). Output expected is also given. Kindly help. Thanks for your efforts.

 A B C D E F G H I 10MAT31 C1 10CSL37 69 56 67 45 82 10CS32 C1 10MAT41 78 60 41 45 36 10CS33 D2 10CS35 65 66 60 49 82 10CS34 C1 10CSL38 72 80 72 58 81 10CS35 D3 10CS34 40 12 25 20 47

 EXPECTED OUTPUT A B C D E F G H I 10MAT31 C1 10MAT31 47 51 41 44 51 10CS32 C1 10CS32 44 40 46 36 47 10CS33 F1 10CS33 48 43 26 27 48 10CS34 D3 10CS34 40 12 25 20 47 10CS35 D2 10CS35 65 66 60 49 82 10CS36 E1 10CS36 45 46 40 40 47

Tuesday, July 17, 2018 3:44 PM

### All replies

• Use a column of formulas. In B2:

=MATCH(D2,A:A,False)

(IFyou don't have headers, adjust for use in row 1)

Copy that down, convert the formulas to values, then sort B:I based on B Ascending.

Tuesday, July 17, 2018 5:28 PM
• Hello MyselfMSFan,

Your example show the result if your data in column A and column D are One-to-one correspondence.

If the data in column A and column D are not One-to-one correspondence, you need consider how to handle below situation

1. For the data in column A, there is no corresponded data in column D/ there is multiple corresponded data in Column D

2. For the data in column D, there is no corresponded data in column A/ there is multiple corresponded data in Column A

Best Regards,

Terry

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Wednesday, July 18, 2018 2:52 AM
• Hello Terry Xu,

Good Morning.

Thank you for the speedy reply. The data in column A and column D are exactly same. But they are in different order. What I want is when you arrange both columns A and D as same....simultaneously the other columns C, D, E, F, G, H, I are also need to be arranged w.r.t D. In short the values in C, D, E, F, G, H and I are related to column D. Please suggest simplest solution. Thanks

Wednesday, July 18, 2018 4:11 AM
• Use my suggestion - that is the simplest.
Wednesday, July 18, 2018 1:20 PM