# find multiple and repeating combinations

#### danonanno

##### New Member
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

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``````

Hi,

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

kind regards,
Erik

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

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

Name: hArray

Refers to:

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

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))

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!

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.)

(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.

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.

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.

Replies
2
Views
154
Replies
2
Views
324
Replies
11
Views
695
Replies
10
Views
760
Replies
1
Views
228

1,219,939
Messages
6,151,077
Members
451,006
Latest member
dhinze84

### 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.

### Which adblocker are you using?

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

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