find multiple and repeating combinations

danonanno

New Member
Joined
Jan 20, 2006
Messages
31
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
Jan 20, 2006
Messages
31
Thanks for your help Eric. Here is a sample of file with desired results


Code:
   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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

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

kind regards,
Erik
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019

ADVERTISEMENT

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019

ADVERTISEMENT

Or - you could just drop in a dummy column and use a pivot...
book2
ABCDEFGH
3Sum of DummyProd ID
4Store12345123462714227143Combo1Combo2Grand Total
521011013
6211111104
721211013
8213111104
9214111104
10Grand Total33523218
Sheet4


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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
(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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
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

New Member
Joined
Jan 20, 2006
Messages
31
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.
 

Forum statistics

Threads
1,141,734
Messages
5,708,162
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top