0

I have a list of customers. Every time they place an order, it can be a little different.

My list of orders by customer looks something like this:

sample customer orders

Although all the orders are different, there is a trend by customer for each field.

So every time there is an order from a specific customer, I make an analysis about the trend for that customer. (My analysis is done field by field and not about each order as a group of fields.)

I’m currently using formula =INDEX(C2:C164; MODE(MATCH(C2:C164; C2:C164; 0))) and my analysis looks like this:

analysis

But this formula only allows me to analyze customer by customer. How can I make this analysis for all the customers at the same time? What I would like to do is to create an overall table for all the customers from the data of the initial table.

The idea is to transform the initial table into a table of trends by customer. That is a table of the predominant patterns for each customer, by field (the most frequent choice by column, by customer).

Is this possible?

  • I’m working with Excel 97-2003 Workbook, but I can use Macro-Enabled if useful. I also might use google-sheets if necessary. Thanks for your help! – Ceu Melo May 10 '18 at 13:41
  • What's the desired result?Unclear: But this formula only allows me to analyze customer by customer. How can I make this analysis for all the customers at the same time? – TheMaster May 10 '18 at 19:12
  • The desired result is a table of trends by customer. That is a table of the predominant patterns for each customer, by field (the most frequent choice by column, by customer). A table like the second print in the question. – Ceu Melo May 11 '18 at 9:05
  • So,What's wrong with the second print's formula? – TheMaster May 11 '18 at 9:14
  • I'm doing line by line manually, with formula =INDEX(C2:C164; MODE(MATCH(C2:C164; C2:C164; 0))). I want to do all the lines at the same time, that is to create a table from the original table. – Ceu Melo May 11 '18 at 9:51
1

  • First screenshot table: A1:F15
  • Second table: A21:E24

B22(for Black):

=INDEX(QUERY(FILTER(C$2:C$15,$A$2:$A$15=$A22),"Select Col1,count(Col1) group by Col1 order by count(Col1) desc",0),2,1)
  • FILTER to filter out Colors for the customer in (A22).
  • QUERY to sort descending by COUNT.
  • INDEX to choose the top Color.
  • Note: Will Not Work in Excel. Try SuperUser or StackOverflow if you want excel-vba. However,Using Excel only formulas,B22: =ARRAYFORMULA(INDEX(C$2:C$15,MATCH(MAX(IF($A22=$A$2:$A$15,COUNTIF($A$2:$A$15&C$2:C$15,$A$2:$A$15&C$2:C$15))),IF($A22=$A$2:$A$15,COUNTIF($A$2:$A$15&C$2:C$15,$A$2:$A$15&C$2:C$15)),0),1)) CSE instead of arrayformula in excel – TheMaster May 11 '18 at 15:50
  • Lemme know if it works in Excel. – TheMaster May 11 '18 at 22:09
  • I'm sorry, but it's not working, the excel returns error in formula :( I've tried to understand/solve, but with no success :'( can you please re-check? [I've replaced all "," by ";"] What do you mean with "CSE instead of arrayformula in excel"? – Ceu Melo May 14 '18 at 16:20
  • @Ceu Hi.. Try the "excel formula" in gSheets. It works. It might help in understanding. CSE means CTRL+SHIFT+ENTER .It's a array formula and hence should be entered as such with CSE in excel. I tried dummifying it for excel more: =INDEX(C$2:C$15,MATCH(MAX(IF($A22=$A$2:$A$15,COUNTIF($A$2:$A$15&C$2:C$15,$A$2:$A$15&C$2:C$15),""),0),IF($A22=$A$2:$A$15,COUNTIF($A$2:$A$15&C$2:C$15,$A$2:$A$15&C$2:C$15),""),0),1) Enter with CSE. – TheMaster May 14 '18 at 16:54
  • Dear I'-'I, just pasted the formula in gSheets and it worked fine!!! Thank you very much!!! You have no idea how much this will improve my life :D – Ceu Melo May 15 '18 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.