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.

 

[Greg Truby]

...and the combination of products ranges from 6 to 38...
-danonanno

There are 38 combinations? Or there are combinations that would include as many as 38 products?

 

[erik.van.geit]

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.

thanks, Greg and Domenic, for the assist

danonanno, if you want to go VBA: seems like you know how to do it ? else I'll give you a hand

 

[Greg Truby]

Pivot + VBA by the looks of it, but let's hear more from the OP. I haven't dug deep into Dom's formulae, but I'm thinkin' that a formula-based solution might get pretty expensive pretty quickly.

 

[SydneyGeek]

Another pivot table approach:

Book2
	A	B	C	D
3	CountofProduct
4	Store	Product	Total
5	210	27142	1
6		27143	1
7	210Total		2
8	211	12345	1
9		12346	1
10		27142	1
11	211Total		3
12	212	27142	1
13		27143	1
14	212Total		2
15	213	12345	1
16		12346	1
17		27142	1
18	213Total		3
19	214	12345	1
20		12346	1
21		27142	1
22	214Total		3
23	GrandTotal		13
Sheet4

The data table has only 2 fields -- Store and Product.

Drag Store to the Row Fields section, then Products to first Data, then Rows.

That will give you products by store in the Rows section, with an adjacent Count. If you have XP or higher (it may also work in 2000), select the products you are interested in from the drop-down next to Products. The table adjusts to suit.

Denis

 

[danonanno]

greg, i think the pivot box you suggested may get me what i need. I'm not sure how you got "combo1" and "combo2". They mark which combo each store gets and thats what i need. Is there then a way to sort the info into all stores that get combo 1? thanks

 

[danonanno]

eric, I know very little about vba or excel for that matter but am learning a great deal from this awsome site. I think the pivot table approach will help me get what i need if I can then sort it in some way to reflect all the stores that gets a particular combo and can paste that list into a field within excel. Otherwise vba seems to be what i need. This file has to be sorted 1 time per month. Up to 6000 stores and up to 38 different combo's of product. file is completely different each month. Can be very time consuming I think it's time i learned more vba. If you can get me started in the right direction i'd love to give it a shot. And thanks to all of you on this awsome site!!!!!!!

 

[SydneyGeek]

danonanno,

Have you tried the pivot table I mentioned?
In the example I got the fields the wrong way around -- Store before Product -- but you can easily switch them. Then you will get a list of Products in the first column, filtered by the Stores that stock them in the second column.
Filtering the products is a case of checking / unchecking options on the drop-down list.

If you don't want to see all the Totals down the table, right-click any one of them and select Hide.

Denis

 

[danonanno]

thanks denis, i couldn't quite get what i needed from either pivot table. For instance i need a list of all the stores that get only products 12345, 12346, and 27142 since some stores get only 27142 and 27143 these stores show up when filtering for stores that get the first 3 products only because of (27142). Does this make sense? While the pivot table is helpful it still isn't quite what i need.

 

[danonanno]

here is another look at what i have. Up to 38 different combo.s of product sku's. One combo would be sku's 27142 and 27143. Another would be sku's 12345, 12346, 27142. up to 6000 store numbers. I need something that will search the file and identify all store #'s that get each different combo. and have a different list for each combo. pivot tables were helpful but i couldn't quite get what i needed. Currently i do this using vlookups and sorting but is time consuming not to mention mind boggling at least to me. javascript:emoticon('')
Confused

   B     C           D  E                                                 
 1 store product sku                                                      
 2 210   27142          stores that get only 12345,12346,27142 ( combo 1) 
 3 210   27143          211                                               
 4 211   12345          213                                               
 5 211   12346          214                                               
 6 211   27142                                                            
 7 212   27142                                                            
 8 212   27143                                                            
 9 213   12345                                                            
10 213   12346                                                            
11 213   27142                                                            
12 214   12345                                                            
13 214   12346                                                            
14 214   27142                                                            

Sheet1

[Table-It] version 05 by Erik Van Geit

 

[erik.van.geit]

I'll figure out some VBA.
Where will the different combos be located ?
And where do you want to get the results ? Separate sheet ? Layout ?