Excel - Checking identical rows for a product-CodePudding

I need to establish if all rows are identical, excluding blank cells, for a particular product. "Nice cake" would be true as it has 3 for Sun and Mon with 2 for the remainder of the week (excluding blank rows). "Fish cake" would be false as Depots 1,9 & 11 are different to the other depots.

I have thought about using "exact()" but each product has a different amount of rows so unsure how to get that to work. Any help muchly appreciated.

CodePudding user response：

If you use Microsoft365 you can use this formula in column 'Diff LT by Depot'

in german version:

=ZEILEN(EINDEUTIG(FILTER([Sun]:[Sat];([Product]=[@Product])*([Sun]<>""))))=1

in US should be:

=ROWS(UNIQUE(FILTER([Sun]:[Sat],([Product]=[@Product])*([Sun]<>""))))=1

CodePudding user response：

In J2 try:

=ROWS(UNIQUE(FILTER(C$2:I$24,MMULT((C$2:I$24<>"")*(A$2:A$24=A2),SEQUENCE(7,,,0)))))=1

CodePudding user response：

you can compare line with line by

=EXACT(CONCAT(N1:Q1);CONCAT(N2:Q2))

or compare all line with first line per each product by

=EXACT(CONCAT($N$1:$Q$1);CONCAT(N2:Q2))

it could help more to add if functions in table

=IF([@product]=OFFSET([@product];-1;0);EXACT(CONCAT(Table1[@[sun]:[sat]]);CONCAT(OFFSET(Table1[@[sun]:[sat]];-1;0)));"")