How to identify multiple product users

ekirk

New Member
Joined
Apr 24, 2017
Messages
7
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,647
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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​
Col1Col2Col3Col4
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​
Col1Col2Col3Col4
2​
aaaa
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"...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

New Member
Joined
Apr 24, 2017
Messages
7
Here's the data sample. It's like 4,000 lines total.

Internal ID ID Product 1Product 2Product 3Product 4Product 5Product 6Product 7Product 8Product 9Product 10Product 11Product 12Product 13Product 14Product 15
17594YesNoNoNoNoNoNoNoNoNoNoNoNoNoNo
17627YesNoNoNoNoNoYesNoNoNoNoNoNoNoNo
2271511YesNoNoNoNoNoNoNoNoNoNoNoNoNoNo
69482549YesNoNoNoNoNoNoNoNoNoNoNoNoNoNo
69552556YesNoNoNoNoNoNoNoNoNoNoNoNoNoNo
69572558YesNoNoNoNoNoYesNoNoNoNoNoNoNoNo
69612562YesNoNoNoNoNoYesNoNoNoNoNoNoNoNo
17638YesNoNoYesNoNoYesNoNoNoNoNoNoNoNo
17649YesNoNoNoNoNoNoNoNoNoNoNoNoNoNo
176510YesYesNoNoNoYesYesNoNoNoNoNoNoNoNo
176712YesNoNoNoNoYesNoNoNoNoNoNoNoNoNo

<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

New Member
Joined
Apr 24, 2017
Messages
7
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

New Member
Joined
Apr 24, 2017
Messages
7
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
I stick with my initial suggestion - do the flagging long-hand with a bunch of appropriately constructed and()s, then count those.
 

ekirk

New Member
Joined
Apr 24, 2017
Messages
7
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.
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top