Creating a new column

Hi,

Just wondering if anyone could help me with this. In the attached I have data regarding drug development projects. In column C I have Event types and in B I have the dates at which each event took place. As you can see in each cell I have several events and dates separated by a line break. Each line in, let's say, cell B2 corresponds exactly with the line in the same position in cel C2. The same applies to all cell in columns B and C. I need to create an extra column (D) called "First Launches" that take the value in B that is in the same line as the term "first launches" in column C. The new column in the attach is in blue, I have done this one manually as an example. The values in B and C that are used to create the new column are in red. I have more than 3000 rows like this. Is there any easy way of doing this?
THanks
AntonioDrug sample.xlsx

Hi Mehmetcick,

Thanks for that. I tried but got a #Value! error message. Just wondering if there is anything I am missing? Thanks a lot
Regards
Antonio

Thanks Siva,

I tried your formula but what I get is all the dates that are in column B and not just the one corresponding to first launches. I guess this is close enough though?
Thanks
Antonio

Hi Silva,

I tried your last suggestion and I get no values. See attached.
Thanks a lot
Regards
Antonio

Hi,
Sorry.
Don't know what is wrong but when I open your file I just see blank values in the column. See screenshot.
Cheers

I just tried in a PC and I can see it now. Weird!! Thanks a lot, very useful!!!

Hi,

Just one more question. What if I want to do the same thing for "Aditional Launches". In this case as you can see in the attached there can be more than one value. Using the formula as its now it would return the first value, but if I want the last value (see example in the third row)?
Thanks

Thanks a lot xladept,
That works great for first launches.
I adapted your routine to do something. This time I want to get the date for additional launches. In some entries there are more than one additional launches so if use your routine I get the first one, but what if I want the last one (the oldest), how would you change the below? Thanks a lot!

Sub Agarzon(): Dim r As Long, i As Long, j As Long, k As Long, c As Long
Dim X As String, Y As String, Z As String
r = Range("B:B").Find("*", , , , xlByRows, xlPrevious).Row
For i = 2 To r: X = Range("B" & i): Y = Range("C" & i)
j = InStr(1, Y, "First Launches")
If j = 0 Then GoTo GetNext
For k = 1 To j
If Mid(Y, k, 1) = Chr(10) Then
c = c + 1: End If: Next k: j = Len(X)
Z = Mid(X, c * 12 + 1, 12): Range("D" & i) = Z: c = 0
GetNext: Next i: End Sub

Hi xladept

Thanks for that. I run your macros (see attached document). It seems that the macros as the are now they return the first value in B. What I would need for Agarzon macro is the date in B that is in the same position as First Launches in C. For macro AgarzonAL what I would need is the date in B that is in the same line of the oldest Additional Launches. I have added extra columns to each of the showing what values I would be looking for. Sorry if this is a bit too confusion. I would really appreciate your help with this.

Thanks A lot

Hi Xladept,
Thanks
That is pretty cool. I tried by entering first launches and I got the attached. See screen shot. Do you get the same values as in the screen shot? Another solutions before was not displaying in my Mac but it did in a PC. I wonder this could be because I am using Mac, although that would be odd!!
Thanks

Screen Shot 2015-02-08 at 09.43.07.pngHI NFsales,

This solution does not show values in a Mac .... I guess is the same problem as before. One of the very first solutions I got here didn't work in Mac but when I tried in a PC at work it worked. I will try on Monday on the PC at work. See below what I see from your doc., Thanks

Thanks a lot xladept! That works very well. Just one last thing. When I use for additional launches I get all the values for all the additional launches it can find. Is there a way that it would return only the oldest date?
Thanks

HI!
Thanks. Sorry all the back and forward. By doing what I still get all dates for additional launches. In the attached, for example, I would like to get only oldest date in this case 15 Mar 1990. I wonder if I am not getting it right cause it is a mac! :roll eyes:
Thanks a lot

Brilliant!!!! Thanks a million!
Best Regards

Forum Example.xlsxHi again!

How would you change the above macro if what I have in B are are sentences instead of dates (like the attached)?
Thanks

Thanks Xladept,
Sorry I didn't explain myself well. What if what I have in B instead of numeric values was string values? Basically I need the same macro do in the same thing but with string instead of numeric. It should return the string value in the same line as Additional Launches. Thanks a lot for your help!
Regards

Sorry I didn't explain myself well there.
I have attached a better example. Basically what I need is very similar to before. In this case I need what is in column D. So the idea is to get what is in the same line as "Discontinued Products" in column C. In the attached all the values for "Discontinued Products" happen to be in the first line but this might not always be the case. Also sometimes there might be more than one "Discontinued Product" in the cell, in this case, I would need to look at only the first line starting from the top (see last row for an example). Thanks a lot.Drug sample (xladept).xlsx
Antonio

Thanks a lot Xladept,

I am applying the above to a larger dataset (around 8000 records like the ones I attached before). When I try to run the macro I get the following error: Run-time error '9'. Subscript out of range, and when I click debug I get the below (it seems the problem is in what I put in red). I know it works with the sample I sent you but I wonder why it doesn't in a larger data set? Thanks

Option Explicit
Sub AgarzonCrit(): Dim r As Long, h As Long, i As Long, j As Long, k As Long, c As Long
Dim Criterion As String: Criterion = "Discontinued Products"
Dim X, Y, Z As String: Range("AC:AC").ClearContents
r = Range("R:R").Find("*", , , , xlByRows, xlPrevious).Row
For i = 2 To r: X = Split(Range("R" & i), Chr(13)): Y = Split(Range("S" & i), Chr(13))
Z = "": For j = LBound(X) To UBound(X)
If (X(j)) = Criterion Then Z = Z & Y(j) & Chr(13)
Next j: Range("AC" & i) = Z
GetNext: Next i: End Sub

Hi,
I am attaching here the full document with the macro you suggested. In this one I get the out of range error. Drug sample (xladept)2.xlsThanks a lot!

Thanks a lot! It works beautifully!!
You have been great help!
Thanks!

Hi xladept,

Thanks again for your help before. I have another issue and it would be great to get your help. I have the attached data and I need to extract first date available in column "marketing" to "marketingdate". Sometimes the first date you find in there it is just the year in this case I would be looking to add 1st of January before the year. Other times you can find the whole date and this case it should appear as it is. Any idea how to do this? I have given you a example outcome for each example. Thanks a lot!

Antonio

sample marketing date.xlsx

Hi Xladept,

Thanks a lot! Not they are not. Unfortunately the dates are part of a text which I guess makes things more challenging. Thanks for your time!

Thanks a a lot again!!!!
Best regards

Hi Xladept again!
Sorry for all the questions! But the data set I have it is quite messy. I hope you can help me with this one.
In the attached I have a column called overview. In this column I am looking for the phrase: "Phase I". If the the overview cell contains the expression then I would like to get "Phase I " (note I am putting a space at the end to differentiate from Phase II in the search) in the new column Phaseoverview. I guess this is somehow similar to my previous queries. Thanks a lot!

Phaseoverview.xlsx

Brilliant!
Thanks a lot!

Date Overview.xlsxHi Xladept,

Just wondering if you could help me with a similar query. This time I want to extract the first date that we find in overview. Sometimes it is a complete date with day, month and year. But other times there is only month and year or only year. If there is only month and year then the first day of the month should be assigned and if there is only a year then it should add the the 1st of January. I guess this is similar to my previous requests. Thanks a lot

Date Overview.xlsxHi Xladept,

Thanks a lot for that. It works well in the sample I sent you, but when I try to use it my larger file I get i few discrepancies (see in the attached in red_. For example, for some of them it returns the wrong date (see first one in red) and for others it does not return anything at all?

Thanks

Yes, that is right. The initial values in red in the document were copied from a larger document with more than 8000 rows. I put them in there for convenience. I wonder if there is any chance of the macro "going wrong" when applied to a larger document? What could go wrong? I tried to figure out but can't think of anything. Thanks

Sorry! I didn't explain myself well there. What I am saying is that the two last records in the last document I sent you were copied from my other larger document. Basically the macro does not seem to work after some point (either get a wrong value or no value) ... It is strange because with the other macros it worked for the entire 8000 rows ... ??

Thanks, just sent you an email
regards

Thanks,

It seems to not work for the whole document, for example for row 951 I get 11/11/10 when it should be 7/07/06. After this point it doesn't seem to return anything at all? Thanks

Thanks a lot!
No worries, really appreciate your help here.
There still seems to be some problems. For example in row 8822 I get a date when I shouldn't be getting anything?
Cheers

HI Xladept,

It works now!!!! Perfectly!
I wonder what happened before! Sure did something wrong!!

Thanks again!!!! You have been great help!!

Hi again Xladept!
I actually would need your help again. This time the task seems easier. I have a column with different values separated by a break (licensee column in the attached) and need to create n amount of columns called licensee1, licensee2 ... licensee(n). n would be determined by the maximum number of lines in licessee. Those columns would take the value of each of the lines in lincesee. How would you operationalise this? I gave you an example in the attached.

Thanks a lot
Regards
Query Xladept 11.5.15.xlsx

Thanks a lot!!
Great!!

Thanks a lot!!
Great!!