Unique return based on data in adjacent column values and return averages

[nickpavlov]

Hello,

I have an example attached. (notes in file)

I am looking to pulls unique customer ID's based on a value in an adjacent column (and for unique customers ID's that have two or more entries) and average all returns in other adjacent columns.

This is pretty complicated, I appreciate any help.

Thank you,

[Glenn Kennedy]

Your WORDS can be interpreted in a couple of different ways. This MIGHT be what you want for the ID. Please populate your sample with manually calculated expected answers, to remove pointless guesswork.

=UNIQUE(FILTER(Table1[CUSTOMER ID],(Table1[NPS]>=7)*(Table1[NPS]<=8)*(COUNTIF(Table1[CUSTOMER ID],Table1[CUSTOMER ID])>1)))

[nickpavlov]

Thank you for the feedback Glenn.

I attached an updated example that has return values.

[CARACALLA]

Spilled array formulas aren't supported in Excel tables. Try moving your formula out of the table, or converting the table to a range (click Table Design > Tools > Convert to range)


A12=UNIQUE(FILTER(Table1[CUSTOMER ID],ISNUMBER(MATCH(Table1[NPS],'DATA ENTRY'!B10:B11,0))))

B12=IF($A12<>"",SUMPRODUCT(COUNTIFS(Table1[CUSTOMER ID],SUMMARY!$A12,Table1[NPS],'DATA ENTRY'!$B$10:$B$11)),"")

Copy down

C12=IFERROR(AVERAGEIFS('DATA ENTRY'!C$3:C$100,'DATA ENTRY'!$A$3:$A$100,SUMMARY!$A12,'DATA ENTRY'!$B$3:$B$100,">="&'DATA ENTRY'!$B$10),"")

Copy across and down

[nickpavlov]

Hello, update attached. I removed the table from the filter worksheet.

Is there a way to have the same filter formula pull the data across and down, currently in A2 to gather the values in columns B-F?

My master copy may return results in the hundreds.

Thanks for any help.

[Glenn Kennedy]

There's a better way to do this... but my head's not working today.

Convert the table to a range and delete ALL the expected answers. Then use this in A12:

=LET(T,Table1,ID,INDEX(T,,1),NPs,INDEX(T,,2),UID,UNIQUE(FILTER(ID,(NPs>=7)*(NPs<=8)*(COUNTIF(ID,ID)>1))),n,COUNTIFS(ID,UID,NPs,">=7",NPs,"<=8"),F,AVERAGEIFS(INDEX(T,,3),ID,UID,NPs,">=7",NPs,"<=8"),G,AVERAGEIFS(INDEX(T,,4),ID,UID,NPs,">=7",NPs,"<=8"),D,AVERAGEIFS(INDEX(T,,5),ID,UID,NPs,">=7",NPs,"<=8"),C,AVERAGEIFS(INDEX(T,,6),ID,UID,NPs,">=7",NPs,"<=8"),CHOOSE({1,2,3,4,5,6},UID,n,F,G,D,C))

[oeldere]

I converted the data with power query.

After that a formula to determine the criteria.

After that a pivot table to get the result.


You don't add the expected result in your file, so I can't check the result.


See the attached file.

[nickpavlov]

Glenn, that works perfect! Thank you.

[nickpavlov]

Hi, I already closed this thread, but I did run into one issue.

I was looking only to display those who had 2 or more NPS of 7 and or 8.

If there are two entries one being 7 and the other being for example 9, the customer id will be displayed with a result return of 1.

I attached the example. I am able to work around this, but adding a sort to the formula and having it sort larger to small, but if there is a solution not to display that would work better.

I appreciate the help on this, and as it is, I am able to use it in my workbook.

thank you,
Nick

[nickpavlov]

Actually, now that I am working with it, it may be useful to see who rated more than once, and how many of those were 7 or 8. Thank you!

[oeldere]

You don't add the expected result in your file, so I can't check the result.


See the attached file.

[Glenn Kennedy]

If you're still looking for help here, please upload a fresh sample file, with an explanation & expected results.