# How to transform a table into a table of trends

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: 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: 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! May 10, 2018 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?` May 10, 2018 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. May 11, 2018 at 9:05
• So,What's wrong with the second print's formula? May 11, 2018 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. May 11, 2018 at 9:51

• 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 May 11, 2018 at 15:50
• Lemme know if it works in Excel. May 11, 2018 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"? May 14, 2018 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. May 14, 2018 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 May 15, 2018 at 7:57