How to transform a table into a table of trends

Question

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?

Answer 1

google-sheets

• 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.