How to identify multiple product users

[ekirk]

I have a data set where each of our customers can have one or more than one of our 10 products. The data right now is organized like this:

Column Names: Customer name/product 1/product 2/ product 3/....
Entry: Emma/Y/N/N...

I need an easy way to not only see how many people have product 1, but how many of those people have product 1 and 2. I just cannot figure out how to organize my data to help me do this. I've tried pivot tables, but it's just not combining the data in a helpful way.

I'm really looking forward to your advice!

 

[hiker95]

ekirk,

Welcome to the MrExcel forum.


We can not tell where your raw data is located, sheet name(s), cells, cell formatting, cell formulae, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, cell formulae, rows, columns.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[FDibbins]

Agree that seeing some data would help, but assuming your data looks something like below, maybe you can adapt this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Col1[/td][td]Col2[/td][td]Col3[/td][td]Col4[/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td]

10​

[/td][td]

1​

[/td][td]

100​

[/td][/tr]

[tr][td]

3​

[/td][td]bb[/td][td]

20​

[/td][td]

2​

[/td][td]

200​

[/td][/tr]

[tr][td]

4​

[/td][td]cc[/td][td]

30​

[/td][td]

3​

[/td][td]

300​

[/td][/tr]

[tr][td]

5​

[/td][td]aa[/td][td]

40​

[/td][td]

4​

[/td][td]

400​

[/td][/tr]

[tr][td]

6​

[/td][td]bb[/td][td]

10​

[/td][td]

5​

[/td][td]

500​

[/td][/tr]

[tr][td]

7​

[/td][td]cc[/td][td]

20​

[/td][td]

6​

[/td][td]

600​

[/td][/tr]

[tr][td]

8​

[/td][td]aa[/td][td]

10​

[/td][td]

7​

[/td][td]

700​

[/td][/tr]

[tr][td]

9​

[/td][td]bb[/td][td]

40​

[/td][td]

8​

[/td][td]

800​

[/td][/tr]

[tr][td]

10​

[/td][td]cc[/td][td]

10​

[/td][td]

9​

[/td][td]

900​

[/td][/tr]

[tr][td]

11​

[/td][td]aa[/td][td]

20​

[/td][td]

10​

[/td][td]

1000​

[/td][/tr]

[tr][td]

12​

[/td][td]bb[/td][td]

30​

[/td][td]

11​

[/td][td]

1100​

[/td][/tr]

[tr][td]

13​

[/td][td]cc[/td][td]

40​

[/td][td]

12​

[/td][td]

1200​

[/td][/tr]
[/table]

For the extract...
[Table="width:, class:grid"][tr][td] [/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Col1[/td][td]Col2[/td][td]Col3[/td][td]Col4[/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td]aa[/td][td]

10​

[/td][td]

1​

[/td][td]

100​

[/td][/tr]

[tr][td]

3​

[/td][td]

10​

[/td][td]aa[/td][td]

10​

[/td][td]

7​

[/td][td]

700​

[/td][/tr]
[/table]
H2=IFERROR(INDEX(A:A,SMALL(IF(($A$2:$A$13=$G$2)*($B$2:$B$13=$G$3),ROW($A$2:$A$13)),ROWS($A$1:A1))),"")
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself."

 

[PaddyD]

"...to not only see how many people have product 1, but how many of those people have product 1 and 2..."

I would set up further columns to flag the conditions you are after and use them:

=and(b2="Y",c2="Y",d2="Y")

...or whatever. With the flags in place, you can then use simple sumif()s, pivot tables etc to get what you're after.

 

[ekirk]

Here's the data sample. It's like 4,000 lines total.

[TABLE="width: 1177"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="9" style="text-align: center;"><col span="6" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Internal ID [/TD]
[TD="align: center"]ID [/TD]
[TD="align: center"]Product 1[/TD]
[TD="align: center"]Product 2[/TD]
[TD="align: center"]Product 3[/TD]
[TD="align: center"]Product 4[/TD]
[TD="align: center"]Product 5[/TD]
[TD="align: center"]Product 6[/TD]
[TD="align: center"]Product 7[/TD]
[TD="align: center"]Product 8[/TD]
[TD="align: center"]Product 9[/TD]
[TD="align: center"]Product 10[/TD]
[TD="align: center"]Product 11[/TD]
[TD="align: center"]Product 12[/TD]
[TD="align: center"]Product 13[/TD]
[TD="align: center"]Product 14[/TD]
[TD="align: center"]Product 15[/TD]
[/TR]
[TR]
[TD="align: center"]1759[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]1762[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]2271[/TD]
[TD="align: center"]511[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]6948[/TD]
[TD="align: center"]2549[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]6955[/TD]
[TD="align: center"]2556[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]6957[/TD]
[TD="align: center"]2558[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]6961[/TD]
[TD="align: center"]2562[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]1763[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]1764[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]1765[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
[TR]
[TD="align: center"]1767[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[ekirk]

And I want to be able to say:

1000 people have product 1
Of those 1000 people, 10 have product 1 and 2; 700 have product 1 and 3; 25 have product 1, 2, and 3.

How should I set it up?

Thank you!

 

[Aladin Akyurek]

And I want to be able to say:

1000 people have product 1
Of those 1000 people, 10 have product 1 and 2; 700 have product 1 and 3; 25 have product 1, 2, and 3.

How should I set it up?

Thank you!

Try to forward result specifications in terms of the sample you posted, not in terms of the whole data.

 

[ekirk]

Okay, so in the sample I shared all 11 people have product 1. Of those 11 people how many have product 2? How many have product 2 and 3?

 

[PaddyD]

I stick with my initial suggestion - do the flagging long-hand with a bunch of appropriately constructed and()s, then count those.

 

[ekirk]

So have column headers that read: "Product 1 and 2" "Product 1, 2, and 3"? Is there an easy way to do that? Seems like I'd have to go through all 4000 records.