• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match formula with condition

Pitcher

Member
Hi guys,

I am using the match formula in the attached file to establish the row in which the first activity takes place on a certain ID. The issue I have is that there are multiple activities that take place on an ID, some of which take place before the Lead ID was created.

Hoping that someone has a way to establish the row in which the first activity takes please post the Lead ID creation date using the match formula.

I have attached a sample of my data which will make the task more clear but please feel free to request more information or instruction.

FYI - rationale for using match formula is due to the size of the actual file, attached is just a sample.

Thanks,
Pitcher
 

Attachments

  • Chandoo File 18.12.2014.xlsx
    796.9 KB · Views: 11
Hi Pitcher,

Can you explain more with a detailed example (may be smaller in size) with the result expected for the cases that you are describing.

Regards,
 
Hi SM,

My query relates the column N within the "Pushlead Lead" tab, please read detailed comments.

In this column I have established the row in which the first activity on the Lead ID takes place from a a time perspective (from the "activities on Leads" tab) but in some cases the first activity (from the "activities on Leads" tab) is before the created date on the Lead ID ("Pushlead" tab). What I want to capture is row in which the first touch takes place post the creation date.

Does that make sense?

Thanks,
Pitcher
 
Hi Pitcher,

I am unable to understand your requirement. You already have a formula in Column N, is it not working properly? Do you wan the formula for column N only or a separate column?

Secondly there are lot of helper columns in both the sheets, is it required?

Let takes some examples:

Say for Lead ID: 00Q6000000ebsLA on Push lead sheet there is one entry on row 4 with created date as 01-08-2014 09:20. But this ID is not there on Activities sheet.

What to do with this ID?

Second, say for Lead ID: 00Q6000000yGm1T, there are two entry on row 21 & 22 on push lead sheet with different created date. This ID has 8 entry on activities sheet from row 532 to 539. What has to be done here?



Regards,
 
Thanks SM,

The formula in column N works but does not give me what I want.

Example - 00Q60000012uuM9 on pushlead lead tab (row 1225)
Row of first contact is 2258 which when looking at the activities on leads tab is 16/10/2014. The issue I have is that the Lead Id on the pushlead tab was created on 23/10/2014 and what I would like to establish is the row the first activity took place post the creation date so in this case it would be row 2262 (27/10/2014 08:24).

Does this help?

Thanks @NARAYANK991 I could not see the comment you left? Looking at what you sent through, it does not appear to address the example issue above?

Thanks,
Pitcher
 
Hi Andrew ,

I did not understand this from the initial explanation alone ; an example always helps. Let me understand this more clearly :

On the PLL tab , for every ID there is a created date ; let us call this PLL_Created_Date.

This ID is to be matched with the IDs on the AOL tab ; what is to be retrieved is the first entry in the AOL_Created_Date column which is greater than or equal to the PLL_Created_Date. Is this correct ?

Narayan
 
OK got it,

Try below array formula:

=IFERROR(INDEX(ROW(tblAOL[Created Date2]),MATCH(1,(tblAOL[Lead ID]=[@[Lead ID]])*(tblAOL[Created Date2]>=[@[Created Date]]),0)),"")

Confirm with Ctrl+Shift+Enter in N4 and copy down.

EDIT: Use this formula

=IFERROR(INDEX(ROW(tblAOL[Created Date2]),MATCH(1,(tblAOL[Lead ID]=[@[Lead ID]])*(tblAOL[Created Date]>=[@[Created Date]]),0)),"")

I think I took date 2 from activity sheet which is wrong (I think So)


Regards,
 
Thanks @NARAYANK991,

That is correct. The formula I currently have in there does not incorporate the greater than or equal to element.

In some cases on the PLL tab for the ID there may not be a corresponding entry on the AOL tab. For these instances I would like to keep this cell blank.

Cheers,
Pitcher
 
Thanks @Somendra Misra,

The file I sent is a sample file, my actual file is much larger. The fear I have in using an array formula is that it will cause too much latency for my file to actually calculate.

Is there an alternative workaround?

Cheers,
Pitcher
 
Hi @Somendra Misra,

On second observation, while the formula captures an activity that takes place after the Lead ID creation date it doesn't appear to capture the first activity post the Lead ID creation date.

Can the formula be updated to achieve this or would a secondary sort on the activty (oldest to newest) the same result?

Thanks,
Pitcher

@NARAYANK991
 
@Pitcher

Can you tell some ID where the same is happening. Note this condition was not there initially. Can't you sore the activity table on two level first by ID and second by time?

Regards,
 
Hey @Somendra Misra ,

Sorting by ID and then by time allows formula to work. The only issue is latency within the file, it took me an hour to update.

If there was a work around to the array formula (which works great, thanks), it would be much appreciate and help me out a lot!

Thanks again for your help,
Andrew
 
Hi Andrew ,

Can you say how many rows your workbook has ? I cannot imagine any data processing taking one hour to process ; if so , then probably we are using the wrong tool.

If you can upload a sample workbook which takes even 10 minutes to process , then we will have a benchmark to improve ; sample files which do not resemble the real one in any manner except for the data layout , do not offer any scope to test different approaches.

Is there any way in which you can upload a scaled down version , which takes around 10 minutes to recalculate ? If this 10 minutes can be brought down to 1 minute , then we can be confident that when this is scaled up , your time of 1 hour may come down to anywhere around 10 minutes , which would be a good step forward.

Narayan
 
@Pitcher

Beside what @NARAYANK991 Sir mentioned above, I tested the formula on 50000 row of data and it took 45 seconds to complete the whole calculations.

Note we don't know the purpose of column G,H & I on activity sheet and column K,L & M on Push lead sheet, may be these columns can be eliminated.

Column K,L & M uses whole column ref. which can also add to time. So you have to see the whole sheet and than decide.

For the problem that you posted I don't think there can be a non-array solution (based on my knowledge).

Regards,
 
Hi guys,

Really appreciate your help on this, I removed multiple rows and and columns of data before sending which are probably the cause of the latency. What I have now works and am willing to wait an hour for a weekly refresh.

A quick question, is there a way I can calculate just one column of my worksheet as opposed to the whole sheet (shift F9)?

Thanks again for all the help,
Pitcher
 
Hi Andrew ,

Using VBA you can ; in the Immediate window , type in the statement :

Range("$F:$F").Calculate

This will recalculate all the cells in column F.

You can specify any range you want recalculated using this syntax.

Narayan
 
Hi,

See the file, I had included a small VBA code, with a button, you select cells that you want to calculate and press button.

Try this.

Regards,
 

Attachments

  • Chandoo File 18.12.2014.zip
    986.9 KB · Views: 1
Back
Top