Results 1 to 8 of 8

Thread: Help!!!!!!!!

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help!!!!!!!!

    I have data in excel as mentioned below

    Input
    1a 1b 1c 4a 4b 4c 7a 7b 7c
    2a 2b 2c 5a 5b 5c 8a 8b 8c
    3a 3b 3c 6a 6b 6c 9a 9b 9c

    Output
    1a 1b 1c
    2a 2b 2c
    3a 3b 3c
    4a 4b 4c
    5a 5b 5c
    6a 6b 6c
    7a 7b 7c
    8a 8b 8c
    9a 9b 9c

    As mentioned in the above i have data in input format,
    and i need data as mentioned in output format.

    Is there any formula for this........?

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Location
    Northwest Arkansas
    Posts
    90
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help!!!!!!!!

    Abhilash1503,
    Use the InStr function to find the starting position of the “a” , then use a combination of Left(), Right(), and Mid() functions to return the data

    hth,
    Computerman

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help!!!!!!!!

    Is the input in 7 different columns and the output in 3?

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,611
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help!!!!!!!!

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    1a
    1b
    1c
    4a
    4b
    4c
    7a
    7b
    7c
    2
    2a
    2b
    2c
    5a
    5b
    5c
    8a
    8b
    8c
    3
    3a
    3b
    3c
    6a
    6b
    6c
    9a
    9b
    9c
    4
    5
    1a
    1b
    1c
    A5: =INDEX($A$1:$I$3, INT((ROWS($A$5:A5) + 2) / 3), MOD(3 * (ROWS($A$5:A5) - 1), 9) + COLUMNS($A$5:A5))
    6
    4a
    4b
    4c
    7
    7a
    7b
    7c
    8
    2a
    2b
    2c
    9
    5a
    5b
    5c
    10
    8a
    8b
    8c
    11
    3a
    3b
    3c
    12
    6a
    6b
    6c
    13
    9a
    9b
    9c

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help!!!!!!!!

    Quote Originally Posted by shg View Post
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    1a
    1b
    1c
    4a
    4b
    4c
    7a
    7b
    7c
    2
    2a
    2b
    2c
    5a
    5b
    5c
    8a
    8b
    8c
    3
    3a
    3b
    3c
    6a
    6b
    6c
    9a
    9b
    9c
    4
    5
    1a
    1b
    1c
    A5: =INDEX($A$1:$I$3, INT((ROWS($A$5:A5) + 2) / 3), MOD(3 * (ROWS($A$5:A5) - 1), 9) + COLUMNS($A$5:A5))
    6
    4a
    4b
    4c
    7
    7a
    7b
    7c
    8
    2a
    2b
    2c
    9
    5a
    5b
    5c
    10
    8a
    8b
    8c
    11
    3a
    3b
    3c
    12
    6a
    6b
    6c
    13
    9a
    9b
    9c
    Thanks for helping out but the output differs....

    It's not in the format i requested....

    It would be grateful if you help

    Thanks in advance....

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help!!!!!!!!

    Thanks for replying

    Would you please workout a sample on that...

    It will be great help for me...

    Thanks in advance

  7. #7
    New Member
    Join Date
    Jul 2015
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help!!!!!!!!

    Hi,

    A5: =INDEX($A$1:$I$3,MOD(ROWS(A$5:A5)-1,3)+1,3*(ROUNDUP(ROWS(A$5:A5)/3,0)-1)+COLUMNS($A5:A5))




    Greetings



    Christian

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help!!!!!!!!

    Quote Originally Posted by Besserwisser View Post
    Hi,

    A5: =INDEX($A$1:$I$3,MOD(ROWS(A$5:A5)-1,3)+1,3*(ROUNDUP(ROWS(A$5:A5)/3,0)-1)+COLUMNS($A5:A5))




    Greetings



    Christian



    Thanks that works fine...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •