Grouping values in table

[excelos]

Hello

I have this data:

	Property1	Property2	Property3	Property4
Record1	1​	2​	3​	4​
Record2	2​	3​	4​	5​
Record3	3​	4​	5​	6​
Record4	4​	5​	6​	7​
Record5	5​	6​	7​	8​

I would like to group it based on the following criteria:

Group1	Property2>3 and Property3>5
Group2	Property2>4 and Property4>6

You will notice that some records may belong to more than one groups.

What is the right format to be able to pivot the data so that I show:

	Count
Group1	2​
Group2	1​

I was thinking to create a new column in the first table called Group and calculate if a record belongs to Group1 or Group2 but the problem is that a record may belong to more than one groups. Using TEXTJOIN would be problematic and would prevent pivoting.
Creating two new columns in the first table called Group1 and Group2 and populate them with Y/N would be very tedious if the groups are too many.

Any idea?

Thanks!

 

[excelos]

Any idea?

 

[cyrilbrd]

Hi, just a question, what is the identifier for 'groups' in your data? You mentioned Group 1 with a count of 2 and Group2 with a count of 1.
This might help others.

 

[excelos]

These are the criteria for the Grouping:

Group1	Property2>3 and Property3>5
Group2	Property2>4 and Property4>6

 

[Akuini]

Creating two new columns in the first table called Group1 and Group2 and populate them with Y/N would be very tedious if the groups are too many.

If you're ok to use macro, I think I can write a macro to do that

 

[excelos]

Thanks but I am rather looking for the output format rather than how to achieve the format.

I.e. an output format like the below does not allow pivoting to achieve the final table (shown last):

	Property1	Property2	Property3	Property4	Group1	Group2
Record1	1	2	3	4	N	N
Record2	2	3	4	5	N	N
Record3	3	4	5	6	N	N
Record4	4	5	6	7	Y	Y
Record5	5	6	7	8	Y	Y

	Count
Group1	2
Group2	1