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.

 

[SydneyGeek]

Danonanno, I just tried the unmodified code with the same test layout as the original solution and it worked first time. I haven't tried it on a huge dataset so I don't know if large numbers of chars will break the first pivot table. Try it and see how you go.

Note: I used the native file format (created a .xlsx file, then inserted the module via the Developer tab).

Denis

 

[SydneyGeek]

Hmmm... struck some sort of integer limit when I tried to create the pivotcache. Worked fine with 32000 rows but not 100000. I'll go for a dig and see what comes up.

Denis

 

[SydneyGeek]

I asked around and was reminded that Excel has a 32K limit on the possible combinations of items in a pivot table. When I checked my list, 30000 rows had 900*200 combinations but the 100000 list had 900*48000.

If that's the case, your problem needs another approach to get there. Maybe Erik's solution is a possibility?

Denis

 

[SydneyGeek]

...or, you could try to run it in Access. If that's an option I'd envisage 2 steps once the data is in Access:

1. Build a dummy table, listing each store against the concatenated SKU combinations. This will require some code but it's not hard to do.
2. Run a report to group the combinations by store.

Denis

 

[danonanno]

thanks for looking into my problem Denis. I thought the problem was with the pivottable and was worried that i may have to approach it differently. Your solution worked sooooo good. Saved me a lot of time and i want to thank you again. Guess we will start over again. It's always sumpin!! danny

 

[SydneyGeek]

Pleasure. Hope you get there one way or another!

Denis

 

[SydneyGeek]

I had another thought. Maybe the list can be broken into chunks and processed separately. Air code --

1. Sort by Store.
2. Tally unique listings of stores and SKUs, taking the last complete store listing to stay under 32784.
3. Send that listing through the first pivot table in the sequence, and keep the output.
4. Grab the next chunk and process to step 3. Continue until all items processed.
5. Take the combined output through the next step of the code to generate the final list.

Worth a try?

Denis

 

[danonanno]

processing the file in chunks is how i have been doing it. Takes longer but gets me by. For instance the last file was broke into 4 chunks and each chunk was run through code which gives you the unique sets of sku's and the store numbers that go with that unique set. then I determine which sets are duplicates of another chunk. If a set of sku's from #1 chunk is a duplicate of another set in another chunk those stores are all combined into 1 list that gets that particular list of sku #'s. I wound up with 150 unique sets of sku's this last time. Total time to prepare all files to print was about 40 hours. Most of this time is due to things that have to be done to each file after all the unique sets of sku's are found. Its a real challenge sometimes!! We are looking into some different ways to automate some of the steps. Your code was a tremendous help even having to do it in chunks. Before we used your code this size file would have taken 1 person 40 hours to sort. It takes about 4 with your code. Can't thank you enough!!!!!!