Hi Demigod,

I was all ready to restructure your data so I could benefit from the use of Tables and Names but then I read "the placements of the columns are fixed" so I've resorted to worker columns. Once you've copied the formulae down the rows as far as the maximum you'll ever need then of course you can hide worker columns E to I.

I didn't know what codes you may use for other Products and I was sure you'd not want to confuse a Mazda Yaris with a Toyota Yaris so it got complex.

Columns A to C are your data

Columns E to I are my worker columns, including some INDIRECT and ADDRESS functions so the LoV is limited to those which are valid (i.e. no blanks in the list). Lots of INDEX, AGGREGATE and COUNTIF functions to limit how many rows are displayed and to retrieve the data.

Columns K to N are where you select the Manufacturer and Product dropdowns to see the list of available colours.

A B C D E F G H I J K L M N 1 $H$3:$H$6 $I$3:$I$4 2 Product Colour Manufacturer ManU ManProd ProdU Man Prod Manufacturer Product Colours 3 Q7 White Audi 8 Q7Audi 4 Audi Q7 Audi Q5 Blue 4 Q7 Blue Audi 7 Q7Audi 3 BMW Q5 White 5 Q7 Red Audi 6 Q7Audi 2 Mazda Grey 6 Q7 Black Audi 5 Q7Audi 1 Toyota Yellow 7 Q5 Blue Audi 4 Q5Audi 4 8 Q5 White Audi 3 Q5Audi 3 Sheet2

Worksheet Formulas

Cell Formula H1 =ADDRESS(ROW(H3),COLUMN())&":"&ADDRESS(ROW(H3)+COUNTIF(H3:H43,"> ")-1,COLUMN()) I1 =ADDRESS(ROW(I3),COLUMN())&":"&ADDRESS(ROW(I3)+COUNTIF(I3:I43,"> ")-1,COLUMN()) E3 =COUNTIF($C3:$C$20,C3) F3 =A3&C3 G3 =IF(C3=K$3,COUNTIF($F3:$F$20,A3&C3),"") H3 =IF(COUNTIF(E:E,1) H$2:H2C$3:C$20,AGGREGATE(15,6,ROW(E$3:E$20)-ROW(H$2)/(E$3:E$20=1),ROWS(H$2:H2)) I3 =IF(COUNTIF(G:G,1) I$2:I2A$3:A$20,AGGREGATE(15,6,ROW(F$3:F$20)-ROW(I$2)/(G$3:G$20=1),ROWS(I$2:I2)) N3 =IF(COUNTIF($F$3:$F$20,L$3&K$3) N$2:N2B$3:B$20,AGGREGATE(15,6,ROW(N$3:N$20)-ROW(N$2)/(F$3:F$20=L$3&K$3),ROWS(N$2:N2))

## Like this thread? Share it with others