Excel challenge: Which combinations do columns have, and how many

S

snezana

New Member
Joined
Sep 4, 2014
Messages
20
Hi all,

So I have a nice challenge that I can't solve since I'm not a big excel expert.. hope someone can help me out!

So i have this product that is being sold. And a single product code (let's say the code is 0001) can have different entries in the columns that follow. E.g. if we sell a product with code 0001, there are 8 different extra options for that product that the consumer can buy. Sometimes people will choose the simple version of product 0001, but they can mix and match what they want added. e.g what type of material the product is made of, whether it can be adjusted yes or no, whether it has a expensive fabric or not... these options are reflected in columns 'Option 1' up until 'Option 8'.

Now what I need to find out is, for a particular product (here, 0001), which combinations of extra options appear, and how many times. I've made a hypothetical table below as an example to work with, but the actual data set would probably have 1000 rows for product 0001 alone.

As you can see there are many different options, and within those options there are a set number of options (which fortunately are always written in the same way so they are exact matches). I would like to create a list from this data saying, e.g.:

[TABLE="width: 1034"]
<tbody>[TR]
[TD]Combination name [/TD]
[TD]Combination content [/TD]
[TD]Number of times appeared[/TD]
[/TR]
[TR]
[TD]Combination 1[/TD]
[TD] iron; 1; option 1.moves up; yes; regular; height and width adjustment; synchroon blokkering elke posi; partial[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Combination 2[/TD]
[TD]iron, plastic; 1; option 1, moves up; no; regular; height adjustment; synchroon blokkering elke posi; double[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Combination 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Combination 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, it should (1) automatically make a list of combinations to show me WHICH combinations there are in the first place, and (2) then count the number of times that combination appear in the data set. I wouldn't be surprised if there were 20 different combinations.

[TABLE="width: 858"]
<tbody>[TR]
[TD]Option 1[/TD]
[TD]Option 2[/TD]
[TD]Option 3[/TD]
[TD]Option 4[/TD]
[TD]Option 5[/TD]
[TD]Option 6[/TD]
[TD]Option 7[/TD]
[TD]Option 8[/TD]
[/TR]
[TR]
[TD]iron[/TD]
[TD]1[/TD]
[TD]option 1, moves up[/TD]
[TD]yes[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]1[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]1[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD]1[/TD]
[TD]option 1, moves down[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD]1[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD]1[/TD]
[TD]option 2, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]other[/TD]
[TD]2[/TD]
[TD]option 1, moves down[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 2, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]regular[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 1, moves up[/TD]
[TD]no[/TD]
[TD]special[/TD]
[TD]height and width adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 3[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]2[/TD]
[TD]option 3[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]3[/TD]
[TD]option 3[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]soft[/TD]
[TD]3[/TD]
[TD]option 5[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]partial[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]3[/TD]
[TD]option 6[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]3[/TD]
[TD]option 7[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
[TR]
[TD]iron, plastic[/TD]
[TD]3[/TD]
[TD]option 8[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
[TR]
[TD]iron[/TD]
[TD]3[/TD]
[TD]option 9[/TD]
[TD]yes[/TD]
[TD]special[/TD]
[TD]height adjustment[/TD]
[TD]synchroon blokkering elke posi[/TD]
[TD]double[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Sort by date Sort by votes

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Ok, and if you're interested in the SQL method it's very easily done in Access with the query wizard, as well as Excel (Microsoft Query, which I learned about from Fazza, shown below):

fr8vSpu.png


which can appear in the spreadsheet as:

afSncxy.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
C
  • Question Question
ole db CopyFromRecordset skipping cell values
Replies
0
Views
619
Clif McIrvin
C
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
Agnarr
  • Question Question
VBA problem with creating new sheets
Replies
0
Views
805
Agnarr
Agnarr
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,223,350
Messages
6,171,578
Members
452,411
Latest member
colpie

Share this page

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
Back
Top