Merging different sheets
in [Functions]
|
Prev: left/right or center ?
Next: Macro question
From: vassilis on 11 Mar 2010 13:18 hello everybody, I have to work with 2 sheets. In the 1st one my data are arranged as follows: Payment Date Type 15/02/2010 XS 15/02/2010 LS 16/02/2010 XS .... ... and in the 2nd sheet: Task Activity XS Activity1 XS Activity2 XS Activity3 LS Activity4 LS Activity5 .... .... and I want to create in a new sheet the following set of data Payment Date Type Activity 15/02/2010 XS Activity1 15/02/2010 XS Activity2 15/02/2010 XS Activity3 15/02/2010 LS Activity4 15/02/2010 LS Activity5 16/02/2010 XS Activity1 16/02/2010 XS Activity2 16/02/2010 XS Activity3 ..... ... ... I tried several combinations between offset, match, countif but nothing is working. I would appreciate any help thank you vassilis
From: Dennis Tucker on 11 Mar 2010 22:45 What rules apply? How do the records on the 1st sheet associate with the 2nd sheet? Or how do you know which date goes where on the 2nd sheet? Dennis "vassilis" <vassilis(a)discussions.microsoft.com> wrote in message news:E80C3CFF-3F3E-474F-8456-1D4921D86D60(a)microsoft.com... > hello everybody, > > I have to work with 2 sheets. In the 1st one my data are arranged as > follows: > > Payment Date Type > 15/02/2010 XS > 15/02/2010 LS > 16/02/2010 XS > ... ... > > and in the 2nd sheet: > Task Activity > XS Activity1 > XS Activity2 > XS Activity3 > LS Activity4 > LS Activity5 > ... .... > > and I want to create in a new sheet the following set of data > > Payment Date Type Activity > 15/02/2010 XS Activity1 > 15/02/2010 XS Activity2 > 15/02/2010 XS Activity3 > 15/02/2010 LS Activity4 > 15/02/2010 LS Activity5 > 16/02/2010 XS Activity1 > 16/02/2010 XS Activity2 > 16/02/2010 XS Activity3 > .... ... ... > > > I tried several combinations between offset, match, countif but nothing is > working. I would appreciate any help > > thank you > > vassilis
From: vassilis on 12 Mar 2010 01:52 thank you for your question... the common key in the two sheets is the "type", i.e. XS, LS, etc. I named it in the 2nd sheet as "task" but actually is the same as type. So in the 1st sheet you have the payment date and the type of the financial instrument and in the 2nd you have the activities associated with each type of instrument. so the new table should be composed of the two tables. for example the 1st 3 rows of the new table refer to the 1st payment on 15/2/2010 which is presented 3 times as "XS" has 3 different activities please come back to me if it is still not clear, much appreciated "Dennis Tucker" wrote: > What rules apply? How do the records on the 1st sheet associate with the > 2nd sheet? > > Or how do you know which date goes where on the 2nd sheet? > > > Dennis > > "vassilis" <vassilis(a)discussions.microsoft.com> wrote in message > news:E80C3CFF-3F3E-474F-8456-1D4921D86D60(a)microsoft.com... > > hello everybody, > > > > I have to work with 2 sheets. In the 1st one my data are arranged as > > follows: > > > > Payment Date Type > > 15/02/2010 XS > > 15/02/2010 LS > > 16/02/2010 XS > > ... ... > > > > and in the 2nd sheet: > > Task Activity > > XS Activity1 > > XS Activity2 > > XS Activity3 > > LS Activity4 > > LS Activity5 > > ... .... > > > > and I want to create in a new sheet the following set of data > > > > Payment Date Type Activity > > 15/02/2010 XS Activity1 > > 15/02/2010 XS Activity2 > > 15/02/2010 XS Activity3 > > 15/02/2010 LS Activity4 > > 15/02/2010 LS Activity5 > > 16/02/2010 XS Activity1 > > 16/02/2010 XS Activity2 > > 16/02/2010 XS Activity3 > > .... ... ... > > > > > > I tried several combinations between offset, match, countif but nothing is > > working. I would appreciate any help > > > > thank you > > > > vassilis > > . >
From: Dennis Tucker on 13 Mar 2010 12:20 Does each payment date with the TYPE(XS) get Activity1, Activity2 and Activity3? Does each payment date with the TYPE(LS) get Activity4 and Activity5? "vassilis" <vassilis(a)discussions.microsoft.com> wrote in message news:32D95325-38C2-42F8-BE16-FC264DA75274(a)microsoft.com... > thank you for your question... > > the common key in the two sheets is the "type", i.e. XS, LS, etc. I named > it > in the 2nd sheet as "task" but actually is the same as type. So in the 1st > sheet you have the payment date and the type of the financial instrument > and > in the 2nd you have the activities associated with each type of > instrument. > > so the new table should be composed of the two tables. for example the 1st > 3 > rows of the new table refer to the 1st payment on 15/2/2010 which is > presented 3 times as "XS" has 3 different activities > > please come back to me if it is still not clear, > > much appreciated > > "Dennis Tucker" wrote: > >> What rules apply? How do the records on the 1st sheet associate with the >> 2nd sheet? >> >> Or how do you know which date goes where on the 2nd sheet? >> >> >> Dennis >> >> "vassilis" <vassilis(a)discussions.microsoft.com> wrote in message >> news:E80C3CFF-3F3E-474F-8456-1D4921D86D60(a)microsoft.com... >> > hello everybody, >> > >> > I have to work with 2 sheets. In the 1st one my data are arranged as >> > follows: >> > >> > Payment Date Type >> > 15/02/2010 XS >> > 15/02/2010 LS >> > 16/02/2010 XS >> > ... ... >> > >> > and in the 2nd sheet: >> > Task Activity >> > XS Activity1 >> > XS Activity2 >> > XS Activity3 >> > LS Activity4 >> > LS Activity5 >> > ... .... >> > >> > and I want to create in a new sheet the following set of data >> > >> > Payment Date Type Activity >> > 15/02/2010 XS Activity1 >> > 15/02/2010 XS Activity2 >> > 15/02/2010 XS Activity3 >> > 15/02/2010 LS Activity4 >> > 15/02/2010 LS Activity5 >> > 16/02/2010 XS Activity1 >> > 16/02/2010 XS Activity2 >> > 16/02/2010 XS Activity3 >> > .... ... ... >> > >> > >> > I tried several combinations between offset, match, countif but nothing >> > is >> > working. I would appreciate any help >> > >> > thank you >> > >> > vassilis >> >> . >>
From: Dennis Tucker on 13 Mar 2010 12:24
Also, would it be better to list these things like this... 15/02/2010 XS Activity1 Activity2 Activity3 15/02/2010 LS Activity4 Activity5 16/02/2010 XS Activity1 Activity2 Activity3 Are these dates in DD/MM/YYY format? "vassilis" <vassilis(a)discussions.microsoft.com> wrote in message news:32D95325-38C2-42F8-BE16-FC264DA75274(a)microsoft.com... > thank you for your question... > > the common key in the two sheets is the "type", i.e. XS, LS, etc. I named > it > in the 2nd sheet as "task" but actually is the same as type. So in the 1st > sheet you have the payment date and the type of the financial instrument > and > in the 2nd you have the activities associated with each type of > instrument. > > so the new table should be composed of the two tables. for example the 1st > 3 > rows of the new table refer to the 1st payment on 15/2/2010 which is > presented 3 times as "XS" has 3 different activities > > please come back to me if it is still not clear, > > much appreciated > > "Dennis Tucker" wrote: > >> What rules apply? How do the records on the 1st sheet associate with the >> 2nd sheet? >> >> Or how do you know which date goes where on the 2nd sheet? >> >> >> Dennis >> >> "vassilis" <vassilis(a)discussions.microsoft.com> wrote in message >> news:E80C3CFF-3F3E-474F-8456-1D4921D86D60(a)microsoft.com... >> > hello everybody, >> > >> > I have to work with 2 sheets. In the 1st one my data are arranged as >> > follows: >> > >> > Payment Date Type >> > 15/02/2010 XS >> > 15/02/2010 LS >> > 16/02/2010 XS >> > ... ... >> > >> > and in the 2nd sheet: >> > Task Activity >> > XS Activity1 >> > XS Activity2 >> > XS Activity3 >> > LS Activity4 >> > LS Activity5 >> > ... .... >> > >> > and I want to create in a new sheet the following set of data >> > >> > Payment Date Type Activity >> > 15/02/2010 XS Activity1 >> > 15/02/2010 XS Activity2 >> > 15/02/2010 XS Activity3 >> > 15/02/2010 LS Activity4 >> > 15/02/2010 LS Activity5 >> > 16/02/2010 XS Activity1 >> > 16/02/2010 XS Activity2 >> > 16/02/2010 XS Activity3 >> > .... ... ... >> > >> > >> > I tried several combinations between offset, match, countif but nothing >> > is >> > working. I would appreciate any help >> > >> > thank you >> > >> > vassilis >> >> . >> |