Merge Rows and Multiply Column Values

[a68tbird]

Hello all -
I have a number of products in my table identified by a ProductID, that list the Color variants and the Size variants in separate rows. What I want to achieve is the total number of variant possibilities for each productID. I've attached some sample data.

Book1
	A	B	C	D
1	ProductId	Name	Options	Variants
2	7	Color	Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	12
3	7	Size	XS,S,M,L,XL	5
4	9	Color	White,Charcoal,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	10
5	9	Size	XS,S,M,L,XL	5
6	11	Color	Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	12
7	11	Size	XS,S,M,L,XL	5
8	14	Color	White,Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	13
9	14	Size	XS,S,M,L,XL	5
10	16	Color	Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	12
11	16	Size	XS,S,M,L,XL	5
12	20	Color	White,Charcoal,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	10
13	20	Size	XS,S,M,L,XL	5
14	21	Color	White,Charcoal,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	10
15	21	Size	XS,S,M,L,XL	5
16	23	Color	White,Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	13
17	23	Size	XS,S,M,L,XL	5
18	25	Color	White,Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	13
19	25	Size	XS,S,M,L,XL	5
Sheet1

My intended outcome would be for ProductID 7, total variants would be 60 (12 * 5), for ProductID 9, total variants = 50 and so on.

Any suggestions on how to achieve this?
Thanks

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post a sample of your expected output?

 

[kweaver]

One way (using 365):

042423MiscMrExcel.xlsm
	A	B	C	D	E	F	G
1	ProductId	Name	Options	Variants		Product ID	Total V
2	7	Color	Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	12		7	60
3	7	Size	XS,S,M,L,XL	5		9	50
4	9	Color	White,Charcoal,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	10		11	60
5	9	Size	XS,S,M,L,XL	5		14	65
6	11	Color	Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	12		16	60
7	11	Size	XS,S,M,L,XL	5		20	50
8	14	Color	White,Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	13		21	50
9	14	Size	XS,S,M,L,XL	5		23	65
10	16	Color	Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	12		25	65
11	16	Size	XS,S,M,L,XL	5
12	20	Color	White,Charcoal,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	10
13	20	Size	XS,S,M,L,XL	5
14	21	Color	White,Charcoal,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	10
15	21	Size	XS,S,M,L,XL	5
16	23	Color	White,Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	13
17	23	Size	XS,S,M,L,XL	5
18	25	Color	White,Black,Navy,Charcoal,Purple,Heather Grey,Royal Blue,Light Blue,Teal Ice,Caribbean Blue,Heather Royal,Charity Pink,Heather Hot Pink	13
19	25	Size	XS,S,M,L,XL	5
Sheet14

Cell Formulas
Range	Formula
F2:F10	F2	=UNIQUE(A2:A19)
G2:G10	G2	=PRODUCT(FILTER($D$2:$D$19,F2=$A$2:$A$19))
Dynamic array formulas.

One alternative (not using 365):

=PRODUCT(IF(TRUE=(F2=$A$2:$A$19),$D$2:$D$19,1))

 

[a68tbird]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post a sample of your expected output?

I'm Windows, 365. Account details updated.

My expected output would just be one row for each unique ProductID and the Total Variants for that ID.

 

[Fluff]

My expected output would just be one row for each unique ProductID and the Total Variants for that ID.

In that case it looks as though kweaver has you sorted.

 

[a68tbird]

@Fluff @kweaver thank you both for your speedy responses. That's a great solution, and works exactly as I wanted.

Cheers!

 

[kweaver]

Thanks for the profile update and happy to hear the solution worked for you. Cheers.