find multiple and repeating combinations

[danonanno]

Hi, all
Very new to this so go easy on me.
I have an excel file with many rows of data. Column B has store numbers and column C product id Numbers. For instance B1=210, B2=210. C1=27142,and C2=27143. This means that store 210 gets product id # 27142 and 27143. Simple huh. These 2 products will repeat throughout column B with different store numbers each time. The trick is to identify all the stores that get these 2 products. Now there is another twist. B3=211,B4=211,and B5=211. C3=12345,C4=12346,C5=27142. This means that store 211 gets products 12345,12346,27142. Notice that stores 210 and 211 both get product # 27142. These 3 products will also repeat throughout the file and the trick is to find all the stores that take these combinations of products. Probably an easy solution but when ya don"t know ya just don't know. Thanks for any help you can give me.

 

[erik.van.geit]

Hi, danonanno
Welcome to the Board !!!!!

the main reason for "no reponse to your thread" seems to me you have a lot of text which could easily be shown in a table
see colo's htmlmaker (link at bottom page)
or see "Table-It" (see my signature)

also show the expected result to avoid any confusion

kind regards,
Erik

 

[danonanno]

Thanks for your help Eric. Here is a sample of file with desired results

   A  B   C     D  E                                            
 1    210 27142    desired results                              
 2    210 27143                                                 
 3    211 12345    stores that get product # 27142 and 27143    
 4    211 12346    210                                          
 5    211 27142    212                                          
 6    212 27142                                                 
 7    212 27143                                                 
 8    213 12345    stores that get products # 12345,12346,27142 
 9    213 12346    211                                          
10    213 27142    213                                          
11    214 12345    214                                          
12    214 12346                                                 
13    214 27142                                                 

Sheet1

[Table-It] version 05 by Erik Van Geit

 

[erik.van.geit]

Hi,

surely this can be done with some VBA
but first I'll call some formulagurus or pivottablegurus

kind regards,
Erik

 

[Greg Truby]

How many stores and how many sku's (prod IDs) we talkin' about?

 

[Domenic]

I could be wrong, but I believe that VBA is probably the way to go. Nevertheless, I couldn't resist trying my hand at this one...

Assuming that A2:B14 contains the data, let G2:I2 contain the product numbers of interest, such as 12345, 12346, and 27142. Note that you can enter one product number, two product numbers, or three product numbers. (This range can be expanded to include more product numbers. If so, adjust the reference in the following formula accordingly.) Then try the folowing...

Defined names...

Select C2

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Add

Name: hArray

Refers to:

=TRANSPOSE(ROW(INDIRECT("1:"&COUNTIF(Sheet1!$A$2:$A$14,Sheet1!$A2))))

Click Add

Name: Val

Refers to:

=SUM(--(MMULT(hArray,--(N(OFFSET(Sheet1!$B$2,SMALL(IF(Sheet1!$A$2:$A$14=Sheet1!$A2,ROW(Sheet1!$A$2:$A$14)-ROW(Sheet1!$A$2)),vArray),0))=Sheet1!$G$2:$I$2))>0))

Click Add

Name: vArray

Refers to:

=ROW(INDIRECT("1:"&COUNTIF(Sheet1!$A$2:$A$14,Sheet1!$A2)))

Click Ok

Formulas...

C1: 0 (enter a zero)

C2, copied down:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),IF(Val>=COUNT($G$2:$I$2),LOOKUP(BigNum,$C$1:C1)+1,""),"")

D1:

=LOOKUP(BigNum,C:C)

E2, copied down:

=IF(ROWS(E$2:E2)<=$D$1,LOOKUP(ROWS(E$2:E2),$C$2:$C$14,$A$2:$A$14),"")

Hope this helps!

 

[Greg Truby]

Or - you could just drop in a dummy column and use a pivot...

book2
	A	B	C	D	E	F	G	H
3	Sum of Dummy	Prod ID
4	Store	12345	12346	27142	27143	Combo1	Combo2	Grand Total
5	210			1	1	0	1	3
6	211	1	1	1		1	0	4
7	212			1	1	0	1	3
8	213	1	1	1		1	0	4
9	214	1	1	1		1	0	4
10	Grand Total	3	3	5	2	3	2	18
Sheet4

(Dom, if I get the chance today, I'm coming back and trying to figure out what the heck you did.)

 

[Domenic]

(Dom, if I get the chance today, I'm coming back and trying to figure out what the heck you did.)

PivotTable...interesting! I'll have to check yours out later.

 

[Greg Truby]

Combo1 and Combo2 are calculated items so it's not really an "off the shelf" pivot; but not terribly hard.

Danonanno, let us know which route you wish to pursue and we'll be happy to fill in any blanks.

 

[danonanno]

thanks for everyones input. I think vba is the way to go with this one but i will look into other ideas as well. I can get some of the info i need using vlookup and some sorting. but i ultimately need an easy way to find out which stores get which compinations of products. there are 3000 stores and the combination of products ranges from 6 to 38 Hope this helps.