If that Manufacturer data moves like this:

AA 
AB 
AC 
399 



400 

Manufacturer 

401 

Audi 

402 

BMW 

403 

Audi 

404 

Audi 

405 

BMW 

406 

BMW 

407 

Audi 

408 

BMW 

409 

Audi 

410 

Audi 

411 

BMW 

412 

BMW 

413 



Sheet1
...and can continue down to AB3600 then the formula in column E becomes =COUNTIF($AB401:$AB$3600,AB401)
Don't forget all those columns with formulae must also be copied down 3,200 rows and the Product and Color must also have 3,200 rows of data.
F3 becomes =A3&AB401
G3 becomes =IF(COUNTIF(G:G,1) < ROWS(I$2:I2),"",INDEX(A$3:A$3203,AGGREGATE(15,6,ROW(F$3:F$3203)ROW(I$2)/(G$3:G$3203=1),ROWS(I$2:I2))))
Like this thread? Share it with others