+ Reply to Thread
Results 1 to 25 of 25

from vertical, in horizontal , automatically

  1. #1
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    from vertical, in horizontal , automatically

    from vertical, in horizontal , automatically
    Attached Files Attached Files

  2. #2
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Hi lomanosa,
    . I have a code that i think does the job for you...

    A few important assumptions..
    .1) in sheet 2 the names list is complete and names are unique there.
    .2) name spellings in both sheets must be identical
    .3) occurrences are always listed in pairs with regard to date, that is to say a start and stop date
    . 4) All dates from sheet 1 are to be found on sheet 2 ( Which would be the case in a real file with all year dates, as you indicated )
    . 5) I assume the output sheet 2 initially has no data entries,
    . 6) I assume the output sheet initially has the names list in column1 and all dates in row 1

    ..... I modified your test data to fit it on screen and demonstrate better: I added George Hjipieris, a Greek I happen to know who is a perpetual Wanker.

    So this would be your Data for input from sheet 1

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    1
    name
    Inicial Date
    Final Date
    Days
    Type
    2
    James
    01.01.2015
    01.05.2015
    5
    holiday
    3
    John
    01.04.2015
    01.06.2015
    3
    half day holiday
    4
    James
    06.03.2015
    06.05.2015
    7
    leiu day
    5
    Richard
    08.01.2015
    08.03.2015
    5
    half leiu day
    6
    Richard
    01.03.2015
    01.04.2015
    2
    sick
    7
    George
    01.01.2015
    06.06.2015
    TFMTC Wanking
    8
    Hjipieris
    01.04.2015
    08.03.2015
    TFMTC Wanking
    F????1

    .. I assume as noted in . 5) and . 6) that only the names column 1 and first row Dates are present initially. Then after running the code with the above sheet 1 Data you get this in the second sheet

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    1
    name
    01.01.2015
    01.02.2015
    01.03.2015
    01.04.2015
    01.05.2015
    01.06.2015
    01.07.2015
    06.03.2015
    06.04.2015
    06.05.2015
    06.06.2015
    08.01.2015
    08.02.2015
    08.03.2015
    2
    James holiday holiday holiday holiday holiday leiu day leiu day leiu day
    3
    John half day holiday half day holiday half day holiday
    4
    Richard sick sick half leiu day half leiu day half leiu day
    5
    Ptang
    6
    PTang
    7
    OLE
    8
    Wiggy
    9
    Wam
    10
    Biscuit
    11
    Barrel
    12
    Du
    13
    WollyWop
    14
    George Wanking Wanking Wanking Wanking Wanking Wanking Wanking Wanking Wanking Wanking Wanking
    15
    Hjipieris Wanking Wanking Wanking Wanking Wanking Wanking Wanking Wanking Wanking Wanking Wanking
    F????2

    ..........

    . The code for the above ( Sub gelomanosaDonkeyWanks() ) I present in the next Post, #3
    Last edited by Doc.AElstein; 08-18-2015 at 07:16 PM. Reason: Wanking a bit more until my plonker falls off
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Code ( gelomanosaDonkeyWanks() ) to give the results discussed in Post #2

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    Thank Doc.AElstein to answer , can the code to put it in a excel file???

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Quote Originally Posted by manos77 View Post
    Thank Doc.AElstein to answer , can the code to put it in a excel file???
    Hi,

    . Just to update everyone. You supplied me per PM and Email a new file Yesterday and asked for help as you were having difficulty logging in to Excel Forum .

    ......................................

    So you changed your input to this: (showing also the new test data you included )

    Using Excel 2007
    Row\Col
    B
    C
    D
    E
    F
    G
    5
    Inicial Date
    Final Date
    Days
    Obs
    Type
    Name
    6
    01.01.2015
    01.10.2015
    10
    Half Day Holiday
    James
    7
    8
    9
    10
    11
    DB

    ..and here is part of your output, ( again showing the hand filled in results you wrote to indicate what you wanted the code to do. )

    Using Excel 2007
    Row\Col
    H
    I
    AU
    AV
    AW
    AX
    AY
    AZ
    BA
    BB
    BC
    BD
    BE
    BF
    BG
    BH
    BI
    BJ
    BK
    BL
    BM
    BN
    BO
    BP
    BQ
    BR
    BS
    BT
    BU
    BV
    BW
    BX
    2
    1/1/2015.
    1/1/2015.
    1/1/2015.
    2/1/2015.
    2/1/2015.
    2/1/2015.
    3/1/2015.
    3/1/2015.
    3/1/2015.
    4/1/2015.
    4/1/2015.
    4/1/2015.
    5/1/2015.
    5/1/2015.
    5/1/2015.
    6/1/2015.
    6/1/2015.
    6/1/2015.
    7/1/2015.
    7/1/2015.
    7/1/2015.
    8/1/2015.
    8/1/2015.
    8/1/2015.
    9/1/2015.
    9/1/2015.
    9/1/2015.
    10/1/2015.
    10/1/2015.
    10/1/2015.
    3
    NAMES
    condition CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    4
    James
    Half Day Holiday Half Day Holiday Half Day Holiday Half Day Holiday Half Day Holiday Half Day Holiday Half Day Holiday Half Day Holiday Half Day Holiday Half Day Holiday
    5
    Robert
    6
    John
    DATA PLAN

    ... so you have a new requirement and need a new code, or the existing one modified quite a bit. Never less the changes are fairly simple so it may be considered a follow up to this Thread.
    . I have therefore written the following code for you which appears to meet the new requirements.
    . I have tested it with the limited test data you gave and it reproduces your hand filled in output results.

    Alan

    P.s.
    Note: There were some issues with different date formats in your two sheets. It is impossible for me at this distance to advise specifically on this considering our different land conventions. You will simply have to try to maintain consistent date formats in those two sheets, as dates are compared for a match in the code , and errors may occur if formats are different.


    Code , ( Sub gelomanosaDonkeyWanks2(), I give in next Post

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Code ( Sub gelomanosaDonkeyWanks2() ) for last Post, Post # 5

    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 08-28-2015 at 07:15 AM.

  7. #7
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    I was wrong to date .
    The date will be the same on both sheets .

  8. #8
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    Thank Doc.AElstein, your code can get into the excel file that sends your
    I do not know , will help too.
    Attached Files Attached Files

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Quote Originally Posted by manos77 View Post
    Thank Doc.AElstein, your code can get into the excel file that sends your
    I do not know , will help too.
    . ? I not do can understand you is difficult. I sorry not in Greek talk is not my to speak language.


    . Do you help want still like have to ?

    . Do you difficulty still having

    . My code you good is working?..
    .. or
    . You not my code to work make kann it?

    Alan


    ........
    . Let me guess kann maybe..

    . You want File back with code in.. You too not good to do that can is

  10. #10
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    YES. I Want file back with code in. Thanks.

  11. #11
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Quote Originally Posted by manos77 View Post
    YES. I Want file back with code in. Thanks.
    Hi,
    . File I here do attached have done you for is ( SpoonFeedADonkeyWanks.xlsm )

    . Note: I do must haved to date Format changed is. It may Greek Wolly will not work . You check change if experiment say that do to work then is will ( Must maybe is . enit? )

    . Please do say if work good for you is ( or not ! )

    ..

    Using Excel 2007
    Row\Col
    B
    C
    D
    E
    F
    G
    5
    Inicial Date
    Final Date
    Days
    Obs
    Type
    Name
    6
    01.01.2015
    10.01.2015
    10
    Half Day Holiday
    James
    7
    01.01.2015
    06.01.2015
    Wanking
    George
    DB



    Using Excel 2007
    Row\Col
    H
    I
    AU
    AV
    AW
    AX
    AY
    AZ
    2
    01.01.2015
    01.01.2015
    01.01.2015
    02.01.2015
    02.01.2015
    02.01.2015
    3
    NAMES
    condition CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    4
    James
    Half Day Holiday Half Day Holiday
    5
    Robert
    6
    John
    7
    William
    8
    Richard
    9
    George
    Wanking Wanking
    10
    Charles
    11
    Thomas
    12
    Michael
    DATA PLAN

    ( George is Wanking again ! )
    ...................................
    Alan
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    What changes should I make to the office to work

  13. #13
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Hi.
    Quote Originally Posted by manos77 View Post
    What changes should I make to the office to work
    . 1 ) Changing the Office to encourage a nice Working Atmosphere.

    . You could bring some Flowers in, maybe.
    . Hang some nice Pictures on the wall
    ................................................

    .2 ) Date Format issues
    .
    . 2a) File I did you give to have already, I did put correct matching format in has for you , .
    . Should be ? Enit?
    .
    . By me works still does me good File

    OR:

    . 2b) You make must:

    Date Format Sheet “DATA PLAN” - Row 2 ( AU2 : AQW2 )

    same as Date format for

    Date Format sheet “DB” - Range B6 : C11

    ....Enit ?



    .Alan

  14. #14
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    google translate

    It does not help to laugh
    p.s 1 we in this forum (Non English Excel) do not forget
    p.s 2 your code does not work
    p.s 3 Thank you for your time

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Alan translation ( Try do )


    . Laughing always good is? Healthy is

    . File and code here work still do

    . You want not working File send do ? – Then I check it later ?

    . If Not. Sorry I no help Better did. ( I very hard try did!! )

    . I try best. All Codes I do work have always.

    . Problem maybe Greek Excel Different is??

    . Alan

    P.s.1. Google translate very badly is. You no friends that English speak to help can ?

    p.s. 2. Let me try guess again:

    You want code start when you date type in?? You must try better to explain exactly what you want.
    Quote Originally Posted by manos77 View Post
    from vertical, in horizontal , automatically
    . - Then you send file back . - I did do normal code. I try can it change to a "Worksheet_Change code"

  16. #16
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Quote Originally Posted by Doc.AElstein View Post
    .......#.... Let me try guess again:

    You want code start when you date type in?? .......... I try can it change to a "Worksheet_Change code"
    .
    . This code MUST go in WORKSHEETS “DB” Module, to be there enit?


    Please Login or Register  to view this content.

    Using Excel 2007
    Row\Col
    B
    C
    D
    E
    F
    G
    5
    Inicial Date
    Final Date
    Days
    Obs
    Type
    Name
    6
    01.01.2015
    03.01.2015
    10
    Half Day Holiday
    James
    7
    01.01.2015
    02.01.2015
    Wanking
    George
    8
    DB


    Using Excel 2007
    Row\Col
    H
    I
    AU
    AV
    AW
    AX
    AY
    AZ
    BA
    BB
    2
    01.01.2015
    01.01.2015
    01.01.2015
    02.01.2015
    02.01.2015
    02.01.2015
    03.01.2015
    03.01.2015
    3
    NAMES
    condition CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    effect
    condition
    CAUSE OF ABSENCE
    4
    James
    Half Day Holiday Half Day Holiday Half Day Holiday
    5
    Robert
    6
    George
    Wanking Wanking
    DATA PLAN

    Alan.

    P.s. I do guess you file want have “SpoonFeedAWanka_Change.xlsm” ? I test therein did it good.
    Last edited by Doc.AElstein; 09-02-2015 at 11:10 AM.

  17. #17
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    google translate

    SpoonFeedADonkeyWanks
    gelomanosaDonkeyWanks
    is not Healthy

    p.s I do something wrong
    p.s Thank you sincerely

  18. #18
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    SpoonFeedAWanka_Change.xlsm ?????
    who is????

  19. #19
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Quote Originally Posted by manos77 View Post

    SpoonFeedADonkeyWanks
    gelomanosaDonkeyWanks
    is not Healthy...I do something wrong...... Thank you sincerely
    Quote Originally Posted by manos77 View Post
    SpoonFeedAWanka_Change.xlsm ?????
    who is????

    alan translate.

    . No you good wanks is healthy. Try it.

    . Then Wank George another Day in Sheet “DB”..
    Attachment 416963




    .... then George Wanks across Sheet “DATA PLAN”
    Attachment 416964


    .. Now had good healthy Wank with George ?
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    google translate

    perfect

    Thank you very much

  21. #21
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    change columns
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically

    google translate

    When I go to Inicial Date-Final Date-Days-Obs I go to the next sheet, I want to go to me only when I give the name

  23. #23
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically. Update only on name column entry

    Hi manos
    As ever communication in English is difficult for you.
    Η επικοινωνία στα αγγλικά είναι δύσκολη για εσάς

    So it is very difficult to understand what you want
    Γι 'αυτό είναι πολύ δύσκολο να καταλάβεις τι θέλεις

    Google translate not too good is
    Η μετάφραση του Google δεν είναι πολύ καλή

    Also you often do not give us enough information
    Επίσης, συχνά δεν μας δίνετε αρκετές πληροφορίες

    _....................
    _.____________________________-

    I am guessing this is the same request that you asked here: ** Duplicate post: https://www.excelforum.com/excel-pro...ml#post4684017
    You included a File in that duplicated post, so it is more understandable what you want.
    I think xladept has given you the answer:
    As he suggested, this code line_....
    Please Login or Register  to view this content.
    _... in conjunction with this code line_...
    Please Login or Register  to view this content.
    _...determines the range which will set off the Events triggered code, _ ( Sub Worksheet_Change(ByVal Target As Range) )

    So , for example, if you change to_...
    Please Login or Register  to view this content.
    _....
    then only the range F2:F7 will set of the Events triggered code, _ ( Sub Worksheet_Change(ByVal Target As Range) )
    manos2017.JPG http://imgur.com/9x4KHWB
    manos2017.JPG
    Using Excel 2007 32 bit
    Row\Col
    F
    1
    Name
    2
    James
    3
    4
    5
    6
    7
    Worksheet: DB

    _......
    _._______________________________

    Note:
    You must not
    Δεν πρέπει να

    _ 1 Duplicate Post **
    Ask same question in many Threads
    Ρωτήστε την ίδια ερώτηση σε πολλά Threads

    _2 Cross post without URL link to other Forums where you have cross posted.

    Εσυ πρεπει:Δώστε σύνδεση URL Για όλες τις ερωτήσεις σας σε διαφορετικά μέρη


    Alan
    Last edited by Doc.AElstein; 06-26-2017 at 05:13 AM.

  24. #24
    Registered User
    Join Date
    12-20-2010
    Location
    athens
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: from vertical, in horizontal , automatically. Update only on name column entry

    google translate
    Doc.AElstein thank you.
    I did the same question in other Threads, because the answer was delayed. sorry

  25. #25
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: from vertical, in horizontal , automatically

    Hi manos

    For, feedback, thank you
    Για, ανατροφοδότηση, ευχαριστώ

    It is also good to thank xladept. https://www.excelforum.com/excel-pro...ml#post4684021
    Καλό είναι επίσης να ευχαριστήσω το xladept https://www.excelforum.com/excel-pro...ml#post4684021

    _..
    Please also:
    Παρακαλώ επίσης:
    You should: Give a URL link For all your questions in different places
    Give a URL link to the Forum where you are making the same question:
    Δώστε έναν σύνδεσμο URL για το φόρουμ όπου κάνετε την ίδια ερώτηση


    Alan

+ 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 to vertical
    By godfredafrifa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2011, 10:18 AM
  2. Replies: 1
    Last Post: 09-29-2011, 08:20 AM
  3. vertical to horizontal
    By giant in forum Excel General
    Replies: 4
    Last Post: 08-14-2010, 09:06 PM
  4. Horizontal to vertical
    By aaslezak in forum Excel General
    Replies: 4
    Last Post: 03-30-2009, 11:47 AM
  5. Horizontal To Vertical
    By khanjee in forum Excel General
    Replies: 3
    Last Post: 02-22-2008, 03:48 AM
  6. Vertical to Horizontal
    By billykiller05 in forum Excel General
    Replies: 3
    Last Post: 12-06-2007, 12:04 PM
  7. Horizontal to Vertical
    By Jett in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-04-2007, 03:37 PM
  8. Vertical to horizontal
    By swchee in forum Excel General
    Replies: 5
    Last Post: 06-19-2005, 11:25 PM

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