+ Reply to Thread
Results 1 to 11 of 11

Convert Excel table into another

  1. #1
    Registered User
    Join Date
    04-15-2019
    Location
    France
    MS-Off Ver
    Office 2016
    Posts
    7

    Convert Excel table into another

    Hello everybody,

    I would like to convert this kind of excel table into the second one
    The idea is to put a formula in the table2 to find in A2: the first choice for alex is to take car. For Mat the 1st choice is to take plane etc...
    If the idea is not clear, I have share the excel file.

    Car Plane Boat Moto walk
    Alex 1 2 3 4 5
    Mat 3 2 1 5 4
    Jo 4 1 2 3 5


    1 2 3 4 5
    Alex Car Plane Boat Moto walk
    Mat Boat Plane Car walk Moto
    Jo Plane Boat Moto Car walk

    Many thanks in advance.
    Attached Files Attached Files
    Last edited by alfpsx; 04-19-2019 at 04:14 AM. Reason: Solved

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Convert Excel table into another

    Your output is wrong both in your description and file

    in your description Jo should be Moto Car Plane Boat Walk

    B9 should be car
    B11 should be Moto
    C11 should be car
    D10 should be car
    D11 should be Plane
    E11 should be Boat

    in A9
    =A2
    in B9
    =INDEX($B$1:$F$1,1,B2)
    copy B9 across to F9

    copy A9:F9 down as far as row 11
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    04-15-2019
    Location
    France
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Convert Excel table into another

    It works for the first 2 rows but not for the last:

    For Jo it should be Plane, Boat, Moto, Car and Walk

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Convert Excel table into another

    Quote Originally Posted by alfpsx View Post
    It works for the first 2 rows but not for the last:

    For Jo it should be Plane, Boat, Moto, Car and Walk
    No thats wrong, the first number for Jo is 4.
    4 is not Plane its Moto so how can it be Plane for 4 ?

    Jo is 4 (moto) 1 (Car) 2 (Plane) 3 (Boat) 5 (Walk)

    If it works for the other rows it will work for ALL rows.

    Look at your data closely.

  5. #5
    Registered User
    Join Date
    04-15-2019
    Location
    France
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Convert Excel table into another

    I have to explain better in this case.

    Imagine you have to travel from one point to another, and you have to precise which type of vehicle you prefer to take
    For example the jo's choice (1st table) is to take at the 1st choice the plane, after the boat (2), etc...

    it's a kind of best option for him.

    Technically, for Jo, I try to find the 1 into B4:F4 and return the means of transport for that table (Boat). After I have to find his 2nd best choice into the same array (B4:F4) etc...

    Sorry for the lack of explanation.

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Convert Excel table into another

    OK, now it's been explained properly

    change the second formula to this

    =INDEX($B$1:$F$1,1,MATCH(COLUMN()-1,$B2:$F2,0))

  7. #7
    Registered User
    Join Date
    04-15-2019
    Location
    France
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Convert Excel table into another

    Perfect ! It's exactly that.

    Many thanks /clap

  8. #8
    Registered User
    Join Date
    04-15-2019
    Location
    France
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Convert Excel table into another

    Perhaps a last thing, did you think it's easy to change depend on the name (Alex, Mat Jo)

    In fact, in my real Excel file I have an unique ID and I have to check in function of that.

    In your formula it correspond to $B2:$F2 part.

    I already try with "B"&MATCH(A9,A2:A4,0) but it doesn't work because this is not recognise as formula

    I Hope

  9. #9
    Registered User
    Join Date
    04-15-2019
    Location
    France
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Convert Excel table into another

    Hello gain,

    In the other Excel file temp.xlsx, you can see that in the Sheet2 column1 I have name of persons in a different order than the sheet1 column1

    Any idea ?
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Convert Excel table into another

    1. You shouldnt have any 7s in the first table as you only have 6 methods of travelling. You need to fix this first.

    2. In Sheet2!B2
    =INDEX(Sheet1!$B$1:$G$1,1,INDEX(Sheet1!$B$2:$G$7,MATCH($A2,Sheet1!$A$2:$A$7,0),COLUMN()-1))
    copy across and down the grid

  11. #11
    Registered User
    Join Date
    04-15-2019
    Location
    France
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Convert Excel table into another

    Quote Originally Posted by Special-K View Post
    1. You shouldnt have any 7s in the first table as you only have 6 methods of travelling. You need to fix this first.

    2. In Sheet2!B2
    =INDEX(Sheet1!$B$1:$G$1,1,INDEX(Sheet1!$B$2:$G$7,MATCH($A2,Sheet1!$A$2:$A$7,0),COLUMN()-1))
    copy across and down the grid
    Just perfect !

+ 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. Replies: 4
    Last Post: 11-29-2015, 07:20 AM
  2. [SOLVED] How to convert flatten table to 2D Excel table
    By martinkabe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-15-2015, 07:22 AM
  3. Convert a PDF table into Excel
    By jrios58tx in forum Excel General
    Replies: 4
    Last Post: 12-23-2013, 03:16 AM
  4. Replies: 1
    Last Post: 09-06-2011, 02:22 AM
  5. Convert an Excel table to a list with VBA
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-01-2006, 06:45 PM
  6. I need to convert a word table to excel
    By Aaron in forum Excel General
    Replies: 1
    Last Post: 12-02-2005, 07:50 AM
  7. [SOLVED] Convert Excel table into Word
    By Harjinder S. Purewal in forum Excel General
    Replies: 1
    Last Post: 05-10-2005, 03:06 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