Date When Certain Amount of Text Occurs

Hi, try this

C1: =IF(AND(B1="a",COUNTIF($B$1:B1,"a")=3,A1<TODAY()),A1,"")

Copy C1 and copy it down the column. You should see the formulas like this:

C2: =IF(AND(B2="a",COUNTIF($B$1:B2,"a")=3,A2<TODAY()),A2,"")
C3: =IF(AND(B3="a",COUNTIF($B$1:B3,"a")=3,A3<TODAY()),A3,"")
... etc


This will show the date from column A only when both conditions are met, the 3rd 'a' occurs and the date in column A is less than the current date.

Cheers

Try this.......

Please Login or Register  to view this content.

Is this what you want?

Do you want this output?

Please Login or Register  to view this content.

Copy it down as far as it is needed.

Do you want the formula to display date when the 3rd occurrence of "a" took place very first time in the range? Or you want formula to display date of column A every time when it finds every 3rd occurrence of "a" in column B? I think you are not able to explain your requirement well.

It would be better if you upload a sample workbook with two sheets, Before and After. Where in the Before sheet you just simply put some sample data and in After sheet, you put the complete desired output applicable to the whole data set of Before sheet.

The formula I gave you will look the first 3rd occurrence of "a" in col. B and then display the corresponding col. A date in corresponding cell in col. C. Then it goes further and once it finds next 3rd occurrence of "a" in col. B, it displays the corresponding col. A date in the corresponding cell in col. C.
Was that not your requirement?

jhudson444 - finding the 1st instance of a text is easy using VLookup(). But finding the 2nd, 3rd,..., nth instance is a little complicated.

If you want all the calculation to take place in one cell, then using a macro/VBA code would be the best and easiest way.

But if you don't mind the calculation spread over a few cells, try using the attached excel.

You'll see that I've moved the columns around (A has become B and B has become A) to make the VLookup work.

Let me know if this works for you.

Cheers

Originally Posted by sktneer

Do you want the formula to display date when the 3rd occurrence of "a" took place very first time in the range? Or you want formula to display date of column A every time when it finds every 3rd occurrence of "a" in column B?

Yes, I want the formula to display the date of column A every time it finds every 3rd occurrence of "a" in column B (it should display the first date of occurrence prior to the computer's date).

Originally Posted by amit.wilson

jhudson444 - finding the 1st instance of a text is easy using VLookup(). But finding the 2nd, 3rd,..., nth instance is a little complicated.

If you want all the calculation to take place in one cell, then using a macro/VBA code would be the best and easiest way.

Yes, I would like the calculation to take place in one cell. I don't know how to use a macro/VBA code.

I've attached a new workbook with 4 sheets to better explain what the formula should do. I hope this helps. Thanks for trying to help, everyone.

try this UDF (User Defined Function) i created:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
the arguments i placed for this function are the Range required, to count what text, and how many times you require the count to be

so that means in Sheet1, i used:
=GetDate(A1:A7,"a",3)

if it's always "a" & 3 counts, you can use this:

Please Login or Register  to view this content.

then just:
=GetDate2(A1:A7)

i am always interested in non-VBA ways, so let me ask if someone awesome can help chip in.

ps: you have to enable macros if you try my file

I lol for this idea i had .
helper column for character count then multiple if statement for index /sumproduct (last entry,2nd to last,3rd to last...) .
look attachment.

jhudsonvlad.xlsx

I can't think a way NOT to have the helper column there, you can hide the column or use other column that is far from those data say column Z.

to combine the two formula
=EOMONTH(the first long formula here,2)+1


BTW the UDF works fine on my side.. and it's shorter.

a shorter version provided by our friends. BUT still with the helper there.
array..

Formula:

Please Login or Register  to view this content.

hope someone could help further analyze on applying a formula without helper column.

using post #13 formula
array entered i came up with this without helper column.

Formula:

Please Login or Register  to view this content.

edited above::: due to last statement c1:c9
you can include your eof the just as in post #14

i don't know if I can do it in offset though. post #15

I don't want to make an entry into column C every time I make an entry into column B

the helper cells are formulas no need to enter then just drag them all the way down as you want.

look below attachment


jhudsonvlad3rd.xlsx

Thank you!

Two more things, and I think my problem will be solved:

1. Can you tell me how to modify the formula to look for 6 occurrences (>=6) instead of 3?

2. Can you tell me how to make the formula also look for "b"?