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.

[TABLE="class: grid, width: 193, align: left"]
<tbody>[TR]
[TD="align: center"]Card Number[/TD]
[TD="align: center"]Merchant[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Wal-Mart[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Target[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Lowes[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Home Depot[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Wal-Mart[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Panera Bread[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]TGI Fridays[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]CVS[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Wal-Mart[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Target[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Lowes[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Nordstrom[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Panera Bread[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Lowes[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Wal-Mart[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Home Depot[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Lowes[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Target[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Panera Bread[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Wal-Mart[/TD]
[/TR]
</tbody>[/TABLE]











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
Wal-Mart

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Merchant[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Wal-Mart[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Target[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Lowes[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Home Depot[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Wal-Mart[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Panera Bread[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]TGI Fridays[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]CVS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Wal-Mart[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Target[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Lowes[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Nordstrom[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Panera Bread[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Lowes[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Wal-Mart[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Home Depot[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Lowes[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Target[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Panera Bread[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Wal-Mart[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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