Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Converting specific columns to vertical one record

  1. #11
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting specific columns to vertical one record

    thank you for the links.
    I have one more question plz.
    If I want to add more columns to your code, I need to type the column name for each one in {"COLUMN NAME", type text} or there is another way like to select the fields and then excel transfer it to code

    Thank you

  2. #12
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,854
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Converting specific columns to vertical one record

    Quote Originally Posted by dankar View Post
    If I want to add more columns to your code
    You mean what?

    you've 8 columns so where you want to add more columns?

    FILE NO RCVD DATE COUNTRY ID_NO PID_NO TEST RESULT REPORT DATE
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST1 NOT TESTED
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST2 NOT TESED
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST3 POSITIVE
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH FINALRESULT POSTTIVE
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST4 NOT TESTED
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST5 NOT TESTED
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST6 PENDING
    25/05/2018
    Last edited by sandy666; Jul 10th, 2019 at 09:40 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #13
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,854
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Converting specific columns to vertical one record

    you can add more rows in source table, like (yellow)

    FILE NO RCVD DATE COUNTRY ID_NO PID_NO TEST RESULT REPORT DATE
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST1 NOT TESTED
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST2 NOT TESED
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST3 POSITIVE
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH FINALRESULT POSTTIVE
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST4 NOT TESTED
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST5 NOT TESTED
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST6 PENDING
    25/05/2018
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST7 PENDING
    25/05/2018
    2
    06/01/2018
    USA
    1234567891
    ABCDEFGH TEST8 NOT TESTED
    26/05/2018


    firsrt yellow has only TEST added , the rest is the same
    second yellow has TEST added and all the rest is different so in the result you will get two rows
    just add what you want and refresh green table or use Ctrl+Alt+F5

    FILE NO RCVD DATE COUNTRY ID_NO PID_NO TEST1 TEST2 TEST3 TEST4 TEST5 TEST6 FINALRESULT REPORT DATE TEST7 TEST8
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH NOT TESTED NOT TESED POSITIVE NOT TESTED NOT TESTED PENDING POSTTIVE
    25/05/2018
    PENDING
    2
    06/01/2018
    USA
    1234567891
    ABCDEFGH
    26/05/2018
    NOT TESTED


    I'll be back in a few hours so if you've more questions try describe in detail what you want to achieve
    Last edited by sandy666; Jul 10th, 2019 at 10:01 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  4. #14
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting specific columns to vertical one record

    the excel table was exported from access query.(contains the main columns I need)
    I was asking if I created a bigger table which included these columns and another ones also.

    I will use your code and alter it to add the new columns, so I was asking do I need to type each new columns in the same format you used,or there is an easier way..like for example selecting the columns and excel will translate it the code you gave me..
    thank you

  5. #15
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,854
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Converting specific columns to vertical one record

    with new columns

    FILE NO RCVD DATE COUNTRY ID_NO PID_NO TEST RESULT REPORT DATE NewColumn NewColumn1
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST1 NOT TESTED
    25/05/2018
    DATA DATA1
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST2 NOT TESED
    25/05/2018
    DATA DATA1
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST3 POSITIVE
    25/05/2018
    DATA DATA1
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH FINALRESULT POSTTIVE
    25/05/2018
    DATA DATA1
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST4 NOT TESTED
    25/05/2018
    DATA DATA1
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST5 NOT TESTED
    25/05/2018
    DATA DATA1
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH TEST6 PENDING
    25/05/2018
    DATA DATA1


    result

    FILE NO RCVD DATE COUNTRY ID_NO PID_NO TEST1 NewColumn NewColumn1 TEST2 TEST3 TEST4 TEST5 TEST6 FINALRESULT REPORT DATE
    1
    05/01/2018
    USA
    1234567890
    ABCDEFGH NOT TESTED DATA DATA1 NOT TESED POSITIVE NOT TESTED NOT TESTED PENDING POSTTIVE
    25/05/2018


    why don't you test it?! It's easy, even with MS Access

    I strongly recommend to read information from links in previous post and here also: Unpivot Columns
    Last edited by sandy666; Jul 10th, 2019 at 12:22 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  6. #16
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting specific columns to vertical one record

    I'm really grateful for your help.

    Surly I will read the links you sent, but the issue for now that I need to show the result tomorrow.

    I already tested the adding more columns before I see your reply
    I just fallowed your code and added the new columns.


    Honestly I needed this to be solved in Access but no one gave me a solution in the access forum except you when I posted it here.

    Can I use the same code in access ? please can you explain to me how to do it in Access .

    again thank you so much!

  7. #17
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,854
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Converting specific columns to vertical one record

    No, You can't use this code IN MS Access directly

    You can get table from access db via Power Query and use appropriate M-code and on the end show result in the sheet
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  8. #18
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,854
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Converting specific columns to vertical one record

    if you want all in MS Access try this forum: https://www.mrexcel.com/forum/microsoft-access/
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  9. #19
    Board Regular
    Join Date
    Mar 2016
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting specific columns to vertical one record

    I already posted it in this link but no one gave a correct answer,its ok to use Excel.

    sorry to bother you again.

    I noticed in my data result that there is another field that have different results.

    in my main example : "REPORT DATE " could also have different entries like the "Result" which preventing from having one record for every patient.

    I tried to add it to your code but I got an error :

    "Expression.Error: We cannot convert the value "REPORT DATE" to type Function.
    Details:
    Value=REPORT DATE
    Type=Type

    This is what I did ( I added "REPORT DATE") here:

    Pivot = Table.Pivot(Type, List.Distinct(Type[TEST]), "TEST", "RESULT","REPORT DATE")

    Is what I did wrong?

    Thank you.

  10. #20
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,854
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Converting specific columns to vertical one record

    ab ovo:

    I hope your source data is an Excel Table
    so ....
    select any cell in your source table,
    then
    ribbon - Data tab - From Table

    it will open new window (Power Query Editor)
    set proper data type for each column then
    select TEST column and from the ribbon - Transform - Pivot Column
    choose : RESULT
    Advanced options: don't aggregate - ok
    then Close&Load to..
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

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
  •