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

	A​	B​	C​	D​
1​	Col1	Col2	Col3	Col4
2​	aa	10​	1​	100​
3​	bb	20​	2​	200​
4​	cc	30​	3​	300​
5​	aa	40​	4​	400​
6​	bb	10​	5​	500​
7​	cc	20​	6​	600​
8​	aa	10​	7​	700​
9​	bb	40​	8​	800​
10​	cc	10​	9​	900​
11​	aa	20​	10​	1000​
12​	bb	30​	11​	1100​
13​	cc	40​	12​	1200​

For the extract...

	G​	H​	I​	J​	K​
1​		Col1	Col2	Col3	Col4
2​	aa	aa	10​	1​	100​
3​	10​	aa	10​	7​	700​

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.

Internal ID

ID

Product 1

Product 2

Product 3

Product 4

Product 5

Product 6

Product 7

Product 8

Product 9

Product 10

Product 11

Product 12

Product 13

Product 14

Product 15

1759

4

Yes

No

No

No

No

No

No

No

No

No

No

No

No

No

No

1762

7

Yes

No

No

No

No

No

Yes

No

No

No

No

No

No

No

No

2271

511

Yes

No

No

No

No

No

No

No

No

No

No

No

No

No

No

6948

2549

Yes

No

No

No

No

No

No

No

No

No

No

No

No

No

No

6955

2556

Yes

No

No

No

No

No

No

No

No

No

No

No

No

No

No

6957

2558

Yes

No

No

No

No

No

Yes

No

No

No

No

No

No

No

No

6961

2562

Yes

No

No

No

No

No

Yes

No

No

No

No

No

No

No

No

1763

8

Yes

No

No

Yes

No

No

Yes

No

No

No

No

No

No

No

No

1764

9

Yes

No

No

No

No

No

No

No

No

No

No

No

No

No

No

1765

10

Yes

Yes

No

No

No

Yes

Yes

No

No

No

No

No

No

No

No

1767

12

Yes

No

No

No

No

Yes

No

No

No

No

No

No

No

No

No

<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>
</tbody>

​

 

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