# Microsoft Office Application Help - Excel Help forum > Non English Excel >  >  from vertical, in horizontal , automatically

## manos77

from vertical, in horizontal , automatically

----------


## Doc.AElstein

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

----------


## Doc.AElstein

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




```
Please Login or Register  to view this content.
```

----------


## manos77

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

----------


## Doc.AElstein

> 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

----------


## Doc.AElstein

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




```
Please Login or Register  to view this content.
```

----------


## manos77

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

----------


## manos77

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

----------


## Doc.AElstein

> 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


........
.    :Roll Eyes (Sarcastic):   Let me guess kann maybe..

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

----------


## manos77

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

----------


## Doc.AElstein

> 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

----------


## manos77

What changes should I make to the office to work

----------


## Doc.AElstein

Hi.




> 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

----------


## manos77

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

----------


## Doc.AElstein

Alan translation ( Try do )


.  Laughing always good is?   :Smilie:  Healthy is  :Wink: 

.  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.




> 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"

----------


## Doc.AElstein

> .......#.... 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.

----------


## manos77

google translate

SpoonFeedADonkeyWanks
gelomanosaDonkeyWanks
is not Healthy

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

----------


## manos77

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

----------


## Doc.AElstein

> SpoonFeedADonkeyWanks
> gelomanosaDonkeyWanks
> is not Healthy...I do something wrong......  Thank you sincerely







> 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 ?

----------


## manos77

google translate

perfect

Thank you very much

----------


## manos77

change columns

----------


## manos77

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

----------


## Doc.AElstein

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*

----------


## manos77

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

----------


## Doc.AElstein

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

----------

