+ Reply to Thread
Results 1 to 8 of 8

Formula to take data from horizontal row

  1. #1
    Forum Contributor
    Join Date
    09-07-2015
    Location
    indonesia
    MS-Off Ver
    Microsoft Office 2010 and 2013
    Posts
    160

    Formula to take data from horizontal row

    Dear Friends,
    I create a summary data vertical/column but source is row style.
    Need your advise which formula I need.
    details as per attached file.

    Thanks in advance for your help.
    Regards,
    Wie
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,793

    Re: Formula to take data from horizontal row

    if the layout is that consistent then one option would be:

    D2: =INDEX(SHIP!$C$2:$H$4,CEILING(ROWS(A$2:A2)/6,1),1+(MOD(ROWS(A$2:A2)-1,6)))
    copied down

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,573

    Re: Formula to take data from horizontal row

    D2=INDEX(SHIP!$C$2:$I$1000,MATCH($A2&LEFT($B2,LEN($B2)-3),INDEX(SHIP!$A$2:$A$1000&SHIP!$B$2:$B$1000,0),0),MATCH(RIGHT($B2,2),INDEX(TEXT(SHIP!$C$1:$I$1,"00"),0),0))
    OR
    D2=AGGREGATE(14,6,SHIP!$C$2:$I$1000/((SHIP!$B$2:$B$1000&TEXT(SHIP!$C$1:$I$1,"-00")=$B2)*(SHIP!$A$2:$A$1000=$A2)),1)
    Try the above, copy and paste towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,580

    Re: Formula to take data from horizontal row

    Another way. In D2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    286

    Re: Formula to take data from horizontal row

    Hi, to all!

    Another option could be:

    =VLOOKUP(A2,SHIP!A$2:H$4,1+RIGHT(B2),)

    Blessings!

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,741

    Re: Formula to take data from horizontal row

    Here is an alternate solution. With Power Query (which is an addin for Excel 2010 at no charge from MS), bring your table into the PQ Editor. Highlight the first two columns and Unpivot the remaining columns. Merge the second and third column with a dash (-) separator. Here is the Mcode that results from those steps.

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    P. O. NO Merged Value
    2
    1234
    AAAA-3
    3
    3
    1234
    AAAA-4
    3
    4
    1234
    AAAA-5
    3
    5
    2345
    BBB-2
    1
    6
    2345
    BBB-4
    2
    7
    2345
    BBB-5
    2
    8
    2345
    BBB-7
    2
    9
    3456
    CCC-2
    2
    10
    3456
    CCC-3
    2
    11
    3456
    CCC-4
    2
    12
    3456
    CCC-5
    2
    13
    3456
    CCC-6
    2
    14
    3456
    CCC-7
    2
    Sheet: Sheet1

  7. #7
    Forum Contributor
    Join Date
    09-07-2015
    Location
    indonesia
    MS-Off Ver
    Microsoft Office 2010 and 2013
    Posts
    160

    Re: Formula to take data from horizontal row

    Thanks all for your advise. this is work

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,580

    Re: Formula to take data from horizontal row

    You are welcome. Thank you for the feedback and marking your thread Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Horizontal data Formula
    By minayat in forum Excel General
    Replies: 9
    Last Post: 01-28-2019, 07:39 PM
  2. Formula for transform data horizontal.
    By inform in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2017, 02:53 PM
  3. Need nested formula for my one line horizontal data
    By arman29 in forum Excel General
    Replies: 1
    Last Post: 08-17-2014, 10:06 AM
  4. [SOLVED] sumproduct formula for horizontal data
    By Jules Pop in forum Excel General
    Replies: 7
    Last Post: 08-08-2014, 05:25 AM
  5. formula for converting vertical data set into horizontal
    By bsapaka in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2014, 09:29 PM
  6. Help with a macro formula to convert horizontal data into veritical data
    By shapenote1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-26-2013, 06:27 PM
  7. [SOLVED] Copying and pasting Horizontal Data to Horizontal cells
    By jjin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2013, 01:14 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1