Find date duplicates Col 2 or Col 3 then combine Col 1 text
 

Greetings,
I have over 400 rows of data where Column 1 is text (project titles) and
Column 2 is a start date and Column 3 is an end date. In each of the date
columns, there are duplications.

I want to get these start and end dates into another spreadsheet which is
formatted like a calendar. However, I gather VLOOKUP on its own won't work
with the duplications, and when the dates in the first spreadsheet aren't in
chronological order.

There may be other ways of solving this, but my immediate thoughts were to
insert a new sheet search for all duplications in Column 2 and then
concatenate the corresponding text in column 1. I then need to find some way
of getting the concatenated text into the right day in the calendar.

Any suggestsions gratefully received.

Find date duplicates Col 2 or Col 3 then combine Col 1 text
 

Perhaps it might suffice to have the various projects time-lined
calendar-wise neatly on the same sheet using CF, as illustrated in this
sample construct:

http://www.savefile.com/files/610176
Project TimeLining.xls

In cols A to C from row 2 down are listed the Projects, Startdates &
Enddates (dates are real dates)

In E1 across to W1 are listed consecutive real dates
eg; 25-2-07, 26-2-07, 27-2-07, etc

Select E2:W4 (E2 active), then apply CF using the formula:
=AND(E$1=$B2,E$1<=$C2)
Format to taste, ok out

Adapt and extend to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Greetings,
I have over 400 rows of data where Column 1 is text (project titles) and
Column 2 is a start date and Column 3 is an end date. In each of the date
columns, there are duplications.

I want to get these start and end dates into another spreadsheet which is
formatted like a calendar. However, I gather VLOOKUP on its own won't work
with the duplications, and when the dates in the first spreadsheet aren't in
chronological order.

There may be other ways of solving this, but my immediate thoughts were to
insert a new sheet search for all duplications in Column 2 and then
concatenate the corresponding text in column 1. I then need to find some way
of getting the concatenated text into the right day in the calendar.

Any suggestsions gratefully received.

Find date duplicates Col 2 or Col 3 then combine Col 1 text
 

Hi Max, Thanks for your input. Your idea had also occured to me, but it
doesn't really achieve what I'm after. I'm trying to take a whole bunch of
information (not just projects) from other spreadsheets and accumulate them
all in a calendar spreadsheet. My other information is not a problem - one
column is the date (chronological) and the next columns are categorised text
(ie meetings, report deadlines, signifcant events, etc), which is then picked
up, column by column, by a VLOOKUP into the calendar spreadsheet, where the
top row is the date, and the rows beneath it pick up the text in meetings,
report deadlines, etc.

With the project data, I'm really trying to mimic MS Project, so I can keep
project information in one sheet just like the CF idea, but also transfer the
start and end dates into the calendar. However, as mentioned before, the
duplication of dates across the many projects means I can only get the first
project ending or starting on a particular date using VLOOKUP. If there was a
way to get Excel to accumulate all the multiple projects starting or ending
on a particular day (and it would be acceptable to have a row in the calendar
for "Projects starting" and "projects ending") - well, that would be
wonderful!

"Max" wrote:

Perhaps it might suffice to have the various projects time-lined
calendar-wise neatly on the same sheet using CF, as illustrated in this
sample construct:

http://www.savefile.com/files/610176
Project TimeLining.xls

In cols A to C from row 2 down are listed the Projects, Startdates &
Enddates (dates are real dates)

In E1 across to W1 are listed consecutive real dates
eg; 25-2-07, 26-2-07, 27-2-07, etc

Select E2:W4 (E2 active), then apply CF using the formula:
=AND(E$1=$B2,E$1<=$C2)
Format to taste, ok out

Adapt and extend to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Greetings,
I have over 400 rows of data where Column 1 is text (project titles) and
Column 2 is a start date and Column 3 is an end date. In each of the date
columns, there are duplications.

I want to get these start and end dates into another spreadsheet which is
formatted like a calendar. However, I gather VLOOKUP on its own won't work
with the duplications, and when the dates in the first spreadsheet aren't in
chronological order.

There may be other ways of solving this, but my immediate thoughts were to
insert a new sheet search for all duplications in Column 2 and then
concatenate the corresponding text in column 1. I then need to find some way
of getting the concatenated text into the right day in the calendar.

Any suggestsions gratefully received.

Find date duplicates Col 2 or Col 3 then combine Col 1 text
 

Think I'm out here. But do hang around awhile for possible responses from
others.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote in message
...
Hi Max, Thanks for your input. Your idea had also occured to me, but it
doesn't really achieve what I'm after. I'm trying to take a whole bunch
of
information (not just projects) from other spreadsheets and accumulate
them
all in a calendar spreadsheet. My other information is not a problem -
one
column is the date (chronological) and the next columns are categorised
text
(ie meetings, report deadlines, signifcant events, etc), which is then
picked
up, column by column, by a VLOOKUP into the calendar spreadsheet, where
the
top row is the date, and the rows beneath it pick up the text in meetings,
report deadlines, etc.

With the project data, I'm really trying to mimic MS Project, so I can
keep
project information in one sheet just like the CF idea, but also transfer
the
start and end dates into the calendar. However, as mentioned before, the
duplication of dates across the many projects means I can only get the
first
project ending or starting on a particular date using VLOOKUP. If there
was a
way to get Excel to accumulate all the multiple projects starting or
ending
on a particular day (and it would be acceptable to have a row in the
calendar
for "Projects starting" and "projects ending") - well, that would be
wonderful!

Find date duplicates Col 2 or Col 3 then combine Col 1 text
 

Not to worry - and thank you for trying! Since my earlier post, I've been
playing with the following idea: I've created Sheet 3, where Column A2:357
is the consecutive days of 2007 (and Column B is the serial date, for ease).

Now all I need to do is to figure out a function that will find every
occurence of A2 (1/1/07) in the start date column of my projects worksheet,
grab the project task info from the next column, and plonk it all back in C2
of Sheet 3!!

It should be easy, but it ain't.

Regards, & thanks again for your efforts.

"Max" wrote:

Think I'm out here. But do hang around awhile for possible responses from
others.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote in message
...
Hi Max, Thanks for your input. Your idea had also occured to me, but it
doesn't really achieve what I'm after. I'm trying to take a whole bunch
of
information (not just projects) from other spreadsheets and accumulate
them
all in a calendar spreadsheet. My other information is not a problem -
one
column is the date (chronological) and the next columns are categorised
text
(ie meetings, report deadlines, signifcant events, etc), which is then
picked
up, column by column, by a VLOOKUP into the calendar spreadsheet, where
the
top row is the date, and the rows beneath it pick up the text in meetings,
report deadlines, etc.

With the project data, I'm really trying to mimic MS Project, so I can
keep
project information in one sheet just like the CF idea, but also transfer
the
start and end dates into the calendar. However, as mentioned before, the
duplication of dates across the many projects means I can only get the
first
project ending or starting on a particular date using VLOOKUP. If there
was a
way to get Excel to accumulate all the multiple projects starting or
ending
on a particular day (and it would be acceptable to have a row in the
calendar
for "Projects starting" and "projects ending") - well, that would be
wonderful!

Find date duplicates Col 2 or Col 3 then combine Col 1 text
 

Hi

Maybe if you created an extra column, which concatenated Task&Date, then
use this for your Vlookup, as each entry would then be unique.

--
Regards

Roger Govier


"Vibeke" wrote in message
...
Not to worry - and thank you for trying! Since my earlier post, I've
been
playing with the following idea: I've created Sheet 3, where Column
A2:357
is the consecutive days of 2007 (and Column B is the serial date, for
ease).

Now all I need to do is to figure out a function that will find every
occurence of A2 (1/1/07) in the start date column of my projects
worksheet,
grab the project task info from the next column, and plonk it all back
in C2
of Sheet 3!!

It should be easy, but it ain't.

Regards, & thanks again for your efforts.

"Max" wrote:

Think I'm out here. But do hang around awhile for possible responses
from
others.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote in message
...
Hi Max, Thanks for your input. Your idea had also occured to me,
but it
doesn't really achieve what I'm after. I'm trying to take a whole
bunch
of
information (not just projects) from other spreadsheets and
accumulate
them
all in a calendar spreadsheet. My other information is not a
problem -
one
column is the date (chronological) and the next columns are
categorised
text
(ie meetings, report deadlines, signifcant events, etc), which is
then
picked
up, column by column, by a VLOOKUP into the calendar spreadsheet,
where
the
top row is the date, and the rows beneath it pick up the text in
meetings,
report deadlines, etc.

With the project data, I'm really trying to mimic MS Project, so I
can
keep
project information in one sheet just like the CF idea, but also
transfer
the
start and end dates into the calendar. However, as mentioned
before, the
duplication of dates across the many projects means I can only get
the
first
project ending or starting on a particular date using VLOOKUP. If
there
was a
way to get Excel to accumulate all the multiple projects starting
or
ending
on a particular day (and it would be acceptable to have a row in
the
calendar
for "Projects starting" and "projects ending") - well, that would
be
wonderful!

Find date duplicates Col 2 or Col 3 then combine Col 1 text
 

Just a closing shot here for you .. perhaps a formulas set-up which extracts
multiple results for any start date and lays it out neatly in a horizontal
manner ?

Illustrated in this sample construct:
http://www.savefile.com/files/613326
Project_TimeLining_v2.xls

In sheet: Projects,
Start dates in col A, Project Names in col B, from row2 down

List in E1 across the sequential dates, eg: 1-Jan-07, 2-Jan-07, etc
Put in E2: =IF($A2="","",IF($A2=E$1,ROW(),""))
Copy E2 right across and fill down as far as required

In Sheet3,
The start dates are sequentially listed in B2 down, eg: 1-Jan-07, 2-Jan-07,
etc

Put in C2:
=IF(COLUMN(A1)COUNT(OFFSET(Projects!$D$2:$D$1000, ,MATCH($B2,Projects!$E$1:$IV$1,0))),"",INDEX(Proje cts!$B:$B,SMALL(OFFSET(Projects!$D$2:$D$1000,,MATC H($B2,Projects!$E$1:$IV$1,0)),COLUMN(A1))))
Copy C21 across by say 6 cols, to cover the max expected # of projects per
any startdate, then fill down as far as required. This will gather all
projects with the same start dates in-line with the start date listed in B2
down, with results all neatly bunched to the left. This might be clean &
close enough to your intent.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Not to worry - and thank you for trying! Since my earlier post, I've been
playing with the following idea: I've created Sheet 3, where Column A2:357
is the consecutive days of 2007 (and Column B is the serial date, for ease).

Now all I need to do is to figure out a function that will find every
occurence of A2 (1/1/07) in the start date column of my projects worksheet,
grab the project task info from the next column, and plonk it all back in C2
of Sheet 3!!

It should be easy, but it ain't.

Regards, & thanks again for your efforts.

Find date duplicates Col 2 or Col 3 then combine Col 1 text
 

You are wonderful!!!!! This works perfectly, because I can now easily
concatenate the projects into Column I, and VLOOKUP from there tot he
calendar.

Thank you!

"Max" wrote:

Just a closing shot here for you .. perhaps a formulas set-up which extracts
multiple results for any start date and lays it out neatly in a horizontal
manner ?

Illustrated in this sample construct:
http://www.savefile.com/files/613326
Project_TimeLining_v2.xls

In sheet: Projects,
Start dates in col A, Project Names in col B, from row2 down

List in E1 across the sequential dates, eg: 1-Jan-07, 2-Jan-07, etc
Put in E2: =IF($A2="","",IF($A2=E$1,ROW(),""))
Copy E2 right across and fill down as far as required

In Sheet3,
The start dates are sequentially listed in B2 down, eg: 1-Jan-07, 2-Jan-07,
etc

Put in C2:
=IF(COLUMN(A1)COUNT(OFFSET(Projects!$D$2:$D$1000, ,MATCH($B2,Projects!$E$1:$IV$1,0))),"",INDEX(Proje cts!$B:$B,SMALL(OFFSET(Projects!$D$2:$D$1000,,MATC H($B2,Projects!$E$1:$IV$1,0)),COLUMN(A1))))
Copy C21 across by say 6 cols, to cover the max expected # of projects per
any startdate, then fill down as far as required. This will gather all
projects with the same start dates in-line with the start date listed in B2
down, with results all neatly bunched to the left. This might be clean &
close enough to your intent.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Not to worry - and thank you for trying! Since my earlier post, I've been
playing with the following idea: I've created Sheet 3, where Column A2:357
is the consecutive days of 2007 (and Column B is the serial date, for ease).

Now all I need to do is to figure out a function that will find every
occurence of A2 (1/1/07) in the start date column of my projects worksheet,
grab the project task info from the next column, and plonk it all back in C2
of Sheet 3!!

It should be easy, but it ain't.

Regards, & thanks again for your efforts.

Find date duplicates Col 2 or Col 3 then combine Col 1 text
 

Glad that last shot was of help to you !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote in message
...
You are wonderful!!!!! This works perfectly, because I can now easily
concatenate the projects into Column I, and VLOOKUP from there to the
calendar.

Thank you!