Need formula to identify identical row values across columns and assign them to groups if identical

[michaelm55]

My spreadsheet has hundreds of stores on the Y axis and hundreds of part numbers on the X axis. The values are quantities of the parts per store. I'm aiming to use a formula to find the all of the stores that have the same numbers of parts and group them.

 

[etaf]

i would sum the columns and then you can use that column to sort and group together
you could add the word "group" using a concatenate if needed
="Group "&SUM(B3:J3)

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Dynamic functions.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Part Numbers
2	Stores	F262338	F262339	F262977	F262978	G253739	G253741	G253751	G253748	G253760	Group
3	1761	4	1		2		1			1	1
4	1781	4	1		2		1			1	1
5	1746	4		1	2		1	2	1		2
6	1745	4		1		2	1			2	3
7	1762	4		1		2	1			2	3
8	1763	4	1	1		2	1			2	4
9	1777	5	2		8		1			2	1
Sheet6

Cell Formulas
Range	Formula
K3:K9	K3	=IFERROR(INDEX(K$2:K2,MATCH(MMULT(--(B3:J3<>""),2^{0;1;2;3;4;5;6;7;8}),MMULT(--(B$2:J2<>""),2^{0;1;2;3;4;5;6;7;8}),0)),MAX(K$2:K2)+1)

Since you have Excel 365, you can use SEQUENCE instead:

=IFERROR(INDEX(K$2:K2,MATCH(MMULT(--(B3:J3<>""),2^SEQUENCE(9)),MMULT(--(B$2:J2<>""),2^SEQUENCE(9)),0)),MAX(K$2:K2)+1)

 

[michaelm55]

Welcome to the MrExcel forum!

Try:

Dynamic functions.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Part Numbers
2	Stores	F262338	F262339	F262977	F262978	G253739	G253741	G253751	G253748	G253760	Group
3	1761	4	1		2		1			1	1
4	1781	4	1		2		1			1	1
5	1746	4		1	2		1	2	1		2
6	1745	4		1		2	1			2	3
7	1762	4		1		2	1			2	3
8	1763	4	1	1		2	1			2	4
9	1777	5	2		8		1			2	1
Sheet6

Cell Formulas
Range	Formula
K3:K9	K3	=IFERROR(INDEX(K$2:K2,MATCH(MMULT(--(B3:J3<>""),2^{0;1;2;3;4;5;6;7;8}),MMULT(--(B$2:J2<>""),2^{0;1;2;3;4;5;6;7;8}),0)),MAX(K$2:K2)+1)

Since you have Excel 365, you can use SEQUENCE instead:

=IFERROR(INDEX(K$2:K2,MATCH(MMULT(--(B3:J3<>""),2^SEQUENCE(9)),MMULT(--(B$2:J2<>""),2^SEQUENCE(9)),0)),MAX(K$2:K2)+1)

Welcome to the MrExcel forum!

Try:

Dynamic functions.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Part Numbers
2	Stores	F262338	F262339	F262977	F262978	G253739	G253741	G253751	G253748	G253760	Group
3	1761	4	1		2		1			1	1
4	1781	4	1		2		1			1	1
5	1746	4		1	2		1	2	1		2
6	1745	4		1		2	1			2	3
7	1762	4		1		2	1			2	3
8	1763	4	1	1		2	1			2	4
9	1777	5	2		8		1			2	1
Sheet6

Cell Formulas
Range	Formula
K3:K9	K3	=IFERROR(INDEX(K$2:K2,MATCH(MMULT(--(B3:J3<>""),2^{0;1;2;3;4;5;6;7;8}),MMULT(--(B$2:J2<>""),2^{0;1;2;3;4;5;6;7;8}),0)),MAX(K$2:K2)+1)

Since you have Excel 365, you can use SEQUENCE instead:

=IFERROR(INDEX(K$2:K2,MATCH(MMULT(--(B3:J3<>""),2^SEQUENCE(9)),MMULT(--(B$2:J2<>""),2^SEQUENCE(9)),0)),MAX(K$2:K2)+1)

Hi and thank you very much for your help. After implementing the formula, I see that it works on the cell contents as uploaded. I also see that if I modify an empty cell in a row, it responds by correctly creating a new group, as it knows that it is now different than the other row(s) in the group. However, if I change an existing cell value in the row, it does not recognize the change and does not create a new group.

 

[michaelm55]

i would sum the columns and then you can use that column to sort and group together
you could add the word "group" using a concatenate if needed
="Group "&SUM(B3:J3)

Hello and thank you very much for your help here. Your approach works perfectly for the data uploaded and assuming it does not change. However, if I change G3 to zero and H3 to one, they will still sum to 9 and therefore be seen as part of the same group, which they are not since the two stores are no longer receiving the same qtys of the same parts, which is what makes them members of the same group. Is there a tweak that you could offer to accommodate for changes?

 

[Eric W]

Hi and thank you very much for your help. After implementing the formula, I see that it works on the cell contents as uploaded. I also see that if I modify an empty cell in a row, it responds by correctly creating a new group, as it knows that it is now different than the other row(s) in the group. However, if I change an existing cell value in the row, it does not recognize the change and does not create a new group.

I didn't realize that you also wanted the values to match, not just whether you had some of each part or not. You can try this variation:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Part Numbers
2	Stores	F262338	F262339	F262977	F262978	G253739	G253741	G253751	G253748	G253760	Group
3	1761	4	1		2		1			1	1
4	1781	4	1		2		1			1	1
5	1746	4		1	2		1	2	1		2
6	1745	4		1		2	1			2	3
7	1762	4		1		2	1			2	3
8	1763	4	1	1		2	1			2	4
9	1777	4		1		2	1			2	3
10	1234		6		1				1		5
Sheet2

Cell Formulas
Range	Formula
K4:K10	K4	=IFERROR(INDEX(K$3:K3,MATCH(MMULT(--B4:J4,10^{0;1;2;3;4;5;6;7;8}),MMULT(--B$3:J3,10^{0;1;2;3;4;5;6;7;8}),0)),MAX(K$3:K3)+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Put a 1 in K3, then put in the K4 formula and drag down. The 10 in the formula (2 places) must be bigger than the largest number of parts you can find in the table. However, if it gets too big you might start running against maximum precision restrictions of Excel. I'll play around a bit more, but this may be more suited to a User-Defined Function written in VBA.