none
How to Rearrange OR sort Number of columns based on one column RRS feed

  • 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