Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Inquiry
Thanks Thanks: 0 Likes Likes: 0

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

    Default Inquiry

    Good day guys!

    For example i have a set of data from sheet1 from column 1 to 10
    A1 0
    B1 0
    C1 0
    D1 2
    E1 34
    F1 65
    G1 32
    H1 23
    I1 15
    J1 90

    How can I transfer it to Sheet 2 and it will:
    *transfered all values to rows
    *it will transfer all values starting with the non zero value(in my example it must start with 2 etc.)

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,232
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Inquiry

    Quote Originally Posted by Duwin View Post
    For example i have a set of data from sheet1 from column 1 to 10
    A1 0
    B1 0
    C1 0
    D1 2
    E1 34
    F1 65
    G1 32
    H1 23
    I1 15
    J1 90

    How can I transfer it to Sheet 2 and it will:
    *transfered all values to rows
    When you say transferred to rows (plural), do you actually mean "across columns in a single row"? I would note your values are already in rows.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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

    Default Re: Inquiry

    Quote Originally Posted by Rick Rothstein View Post
    When you say transferred to rows (plural), do you actually mean "across columns in a single row"? I would note your values are already in rows.
    Sorry my bad..for example i have it in sheet 1 row a1 to j1

    How can I transfer it to Sheet 2 and it will:
    *transfered all values to one column
    *it will transfer all values starting with the non zero value(in my example it must start with 2 etc.)

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

    Default Inquiry

    Good day guys!

    For example i have a set of data from sheet1 from A1 to J1
    A1 0
    B1 0
    C1 0
    D1 2
    E1 34
    F1 65
    G1 32
    H1 23
    I1 15
    J1 90

    How can I transfer it to Sheet 2 and it will:
    *transfered all values to column A1 to A10
    *it will transfer all values starting with the non zero value(in my example it must start with 2 etc.)

    Thanks in advance

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inquiry

    Assuming original data in Sheet 1, enter this formula into A1 of sheet 2, then drag down.

    =IFERROR(AGGREGATE(15,6,1/(1/SHEET!!$A$1:$J$1),ROWS(A$1:A1)),"")

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

    Default Re: Inquiry

    Quote Originally Posted by jasonb75 View Post
    Assuming original data in Sheet 1, enter this formula into A1 of sheet 2, then drag down.

    =IFERROR(AGGREGATE(15,6,1/(1/SHEET!!$A$1:$J$1),ROWS(A$1:A1)),"")
    Thanks jason..it worked but the transfered values become increasing..is it possible to retain the same sequence of numbers..for example it will copy to sheet 2

    A1 2
    B1 34
    C1 65
    D1 32
    E1 23
    F1 15
    G1 90

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,736
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Inquiry

    I have merged your two threads with the same question together. Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will typically be locked or deleted (rule 12 here: Forum Rules).

    If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

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

    Default Re: Inquiry

    Quote Originally Posted by Joe4 View Post
    I have merged your two threads with the same question together. Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will typically be locked or deleted (rule 12 here: Forum Rules).

    If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.
    Im so Sorry joe..it will not happen again 😔

  9. #9
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inquiry

    [QUOTE=Duwin;5345647]is it possible to retain the same sequence of numbers../QUOTE]
    In that case, try

    =IFERROR(INDEX(Sheet1!$A$1:$J$1,AGGREGATE(15,6,COLUMN(Sheet1!$A$1:$J$1)/(Sheet1!$A$1:$J$1<>0),ROWS(A$1:A1))),"")

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

    Default Re: Inquiry

    [QUOTE=jasonb75;5345753]
    Quote Originally Posted by Duwin View Post
    is it possible to retain the same sequence of numbers../QUOTE]
    In that case, try

    =IFERROR(INDEX(Sheet1!$A$1:$J$1,AGGREGATE(15,6,COLUMN(Sheet1!$A$1:$J$1)/(Sheet1!$A$1:$J$1<>0),ROWS(A$1:A1))),"")
    It copied it in row but still in an inreasing order

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
  •