Formula to filter results from data

Hi all,

Tricky one for me this - bit of logic help with an index/match coupled with IF and AND commands.

In the attached sheet, the YEAR tab contains a record of emails used in a campaign. The same email will be sent to different types of customers - columns L-O are where these differ.

The OVERVIEW tab needs to pull in info from YEAR but ONLY for the main send of an email (column H), not the REMAIL.

In cells A2 thru K2 of the OVERVIEW tab, I can find the data by keying to the EMAIL ID.

But, in cell L2 I am trying to say;

- using the same find criteria as columns B-K...
- check if the cell contents of YEAR column L are the same as the adjacent row in YEAR column L
- if so, return that value
- if different, return the value below

... and am getting tied up in knots as the formula gets so long, and the number of parenthesis needed gets muddled.

Ideally, I want to;
- still do this IN formula
- be able to use the same for columns to right of L by dragging along

Can anyone look at what I've tried and suggest a solution please, or steer me where I've gone wrong?

Thanks in advance,

Ian

I'm not sure that I follow your requirements exactly, but I would do it this way:

Put this formula in T2 of the YEAR sheet:

=IF(H2="Main",MAX(T$1:T1)+1,"-")

then copy down as far as you like in order to accommodate more data being added. This formula will generate unique sequential numbers for those records which match your criteria (i.e. column H is "Main").

Put this formula in T2 of the Overview sheet:

=IFERROR(MATCH(ROWS($1:1),YEAR!T:T,0),"")

and copy this down. This will return the row where the sequential numbers occur on the YEAR sheet. You can hide the two columns T if you want the file to look the same as before.

Then you can use this formula in A2 of the Overview sheet:

=IF($T2="","",INDEX(YEAR!A:A,$T2))

and this can be copied across and down to generate your table automatically. You might like to change the formula in N2 to this:

=IF($T2="","",INDEX(YEAR!N:N,$T2)&"")

so that it generates a blank rather than zero if the corresponding field in the YEAR sheet is blank. You can do this to O2 also.

I've attached a file showing this.

Hope this helps.

Pete

@canapone - your solution seems to do the job, except that when I add and drag, column J of OVERVIEW is blank, where it should say TACTICAL.

Unsure I understand what it's doing to find the data given that the formula isn't using the email id in column A as the lookup value to find the row? It's likely that, the OVERVIEW sheet will be on row 400 (for example) looking at a new YEAR sheet for that year -so it won't always follow that row 2 on one is looking for row 2 on the other, if that makes sense.

@canapone

Have found another issue when applied to my live data.

The attached has 9 'MAIN' sends under jobno 02349-02 yet the OVERVIEW sheet is only 'seeing' 4 of them. Why is this please? Although the majority of the data for each of the 9 emails is the same, it differs when it comes to M, N and O columns where the same email is sent to different people.

Also, how would I stop a 0 appearing where segment ID is blank (columns M,N and O of the OVERVIEW).

Thanks,

Ian

The reason is that for the missing data "Main" is actual "Main " i.e trailing blank.

To "hide" zeros CUSTOM format as 0;;;@

@JohnTopley - you're a true gent - thanks for spotting - have cleaned my live data of those erroneous blanks

Will try that hide solution...

@JohnTopley - formatting worked a treat, thanks

@canapone - let me know if you've any suggestions please for post #6 above?

Have managed to sort all except summing data in MAIN and REMAIL rows for same email (based on status of columns L-O).

Have attempted changing AGGREGATE to SUM (9) and added a filter - both on the yellow highlighted column R of OVERVIEW tab - can anyone assist with making this formula work please?

Where am I going wrong?

Thanks,

Ian

No problem, sheet attached with an extra EXPECTED which has sums in to illustrate.

Last row is a wildcard - if there is no second REMAIL number, formula should still just collate MAIN data for that column.

Thank you,

Ian

Thanks - have implemented - attached - but it's only seeing first value for the MAIN status in column H of year, not adding (for example);

- where these values match on MAIN and REMAIL of that JOB NO;

RECIPIENT INTEREST
Model2 & Model7 P2
SEGMENT ID 1
Model2
SEGMENT ID 2
Users

- should be finding value 33 from MAIN on row 2 of YEAR (cell R2) and value 102 from row 11 of YEAR (cell R11)
- and adding them together

Thanks for trying - am THIS close to nailing this sheet build, just need that last part to work.

Hoping canapone will see and jump in as his initial solution was quite lovely

Thanks - on the one I shared, formula in OVERVIEW cell R2 was;

=IFERROR(INDEX(YEAR!$A$1:$R$103,AGGREGATE(15,6,ROW($1:$103)/--SUMPRODUCT((YEAR!$H$1:$H$103="main")+(YEAR!$H$1:$H$103="remail")),ROWS($1:1)),MATCH(R$1,YEAR!$A$1:$R$1,0)),"")

Unsure how it returned correct value for you, for me it still shows '33'

BUT your sheet with the much longer formula in OVERVIEW cell R2;

=IFERROR(INDEX(YEAR!$R$1:$R$20,AGGREGATE(15,6,ROW($1:$20)/((YEAR!$M$1:$M$20=M2)*(YEAR!$H$1:$H$20="main")),COUNTIF($M$2:M2,M2))),0)+IFERROR(INDEX(YEAR!$R$1:$R$20,AGGREGATE(15,6,ROW($1:$20)/((YEAR!$M$1:$M$20=M2)*(YEAR!$H$1:$H$20="remail")),COUNTIF($M$2:M2,M2))),0)

Works - but I am lost as to why/how.

Have you repeated the formula by doing it twice & adding the result?

THANK YOU for coming back to me however.

Ian

Thanks - works on the sample data but having real trouble getting correct output when I apply to my actual data. The above isn't taking into account column N or O differences also. Will pick up again in morning as my brain is fried.