 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / April 2007Find 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. 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 ..  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Greetings, > I have over 400 rows of data where Column 1 is text (project titles) and [quoted text clipped - 12 lines] > > Any suggestsions gratefully received. 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! > Perhaps it might suffice to have the various projects time-lined > calendar-wise neatly on the same sheet using CF, as illustrated in this [quoted text clipped - 30 lines] > > > > Any suggestsions gratefully received. Think I'm out here. But do hang around awhile for possible responses from others. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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 [quoted text clipped - 27 lines] > for "Projects starting" and "projects ending") - well, that would be > wonderful! 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. > Think I'm out here. But do hang around awhile for possible responses from > others. [quoted text clipped - 29 lines] > > for "Projects starting" and "projects ending") - well, that would be > > wonderful! 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. SignatureRegards Roger Govier > Not to worry - and thank you for trying! Since my earlier post, I've > been [quoted text clipped - 64 lines] >> > be >> > wonderful! 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(Projects!$B:$B,SMALL(OFFSET(Projects!$D$2:$D$1000,,MATCH($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. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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 [quoted text clipped - 8 lines] > > Regards, & thanks again for your efforts. 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! > 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 [quoted text clipped - 34 lines] > > > > Regards, & thanks again for your efforts. Glad that last shot was of help to you ! SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.