Display only value that have things in common?

[osscie3]

This one is a hard one to articulate so I'll do my best. Also, please tell me if this is easier in Access.

I have a set of transactional data that has card number and merchant. I want to only display merchants (ideally in a pivot table) that all of those card numbers have been to and exclude the rest. For instance let's say I have 5 different card numbers and they've all made transactions at different merchants. I only want to see the merchants that ALL of those 5 cards have been to and exclude the rest. How can I do this?

Thanks!

 

[Aladin Akyurek]

Try to post a small sample along with the desired result(s) for that sample.

 

[osscie3]

Try to post a small sample along with the desired result(s) for that sample.

Card Number	Merchant
1	Wal-Mart
1	Target
1	Lowes
1	Home Depot
2	Wal-Mart
2	Panera Bread
2	TGI Fridays
2	CVS
3	Wal-Mart
3	Target
3	Lowes
3	Nordstrom
4	Panera Bread
4	Lowes
4	Wal-Mart
4	Home Depot
5	Lowes
5	Target
5	Panera Bread
5	Wal-Mart

<tbody>
</tbody>

I'd want it to return Wal-Mart since that's only merchant that all card numbers have in common.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	Number	Merchant
2	1	Wal-Mart			Wal-Mart
3	1	Target
4	1	Lowes
5	1	Home Depot
6	2	Wal-Mart
7	2	Panera Bread
8	2	TGI Fridays
9	2	CVS
10	3	Wal-Mart
11	3	Target
12	3	Lowes
13	3	Nordstrom
14	4	Panera Bread
15	4	Lowes
16	4	Wal-Mart
17	4	Home Depot
18	5	Lowes
19	5	Target
20	5	Panera Bread
21	5	Wal-Mart
Sheet1

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$21,SMALL(IF(FREQUENCY(IF($B$2:$B$21<>"",MATCH($B$2:$B$21,$B$2:$B$21,0)),ROW($B$2:$B$21)-ROW($B$2)+1)=SUM(IF(FREQUENCY(A2:A21,A2:A21),1)),ROW($B$2:$B$21)-ROW($B$2)+1),ROWS($E$2:E2))),"")

 

[osscie3]

	A	B	C	D	E	F
1	Number	Merchant
2	1	Wal-Mart			Wal-Mart
3	1	Target
4	1	Lowes
5	1	Home Depot
6	2	Wal-Mart
7	2	Panera Bread
8	2	TGI Fridays
9	2	CVS
10	3	Wal-Mart
11	3	Target
12	3	Lowes
13	3	Nordstrom
14	4	Panera Bread
15	4	Lowes
16	4	Wal-Mart
17	4	Home Depot
18	5	Lowes
19	5	Target
20	5	Panera Bread
21	5	Wal-Mart

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$21,SMALL(IF(FREQUENCY(IF($B$2:$B$21<>"",MATCH($B$2:$B$21,$B$2:$B$21,0)),ROW($B$2:$B$21)-ROW($B$2)+1)=SUM(IF(FREQUENCY(A2:A21,A2:A21),1)),ROW($B$2:$B$21)-ROW($B$2)+1),ROWS($E$2:E2))),"")

Awesome. Will this work if there are multiple merchants in common?

 

[Aladin Akyurek]

Awesome. Will this work if there are multiple merchants in common?

Yes.