Display only value that have things in common?

O

osscie3

Board Regular
Joined
Apr 30, 2014
Messages
70
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!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Aladin Akyurek said:
Try to post a small sample along with the desired result(s) for that sample.
Click to expand...

[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.
 
Last edited:
Upvote 0

Book1
ABCDEF
1NumberMerchant
21Wal-MartWal-Mart
31Target
41Lowes
51Home Depot
62Wal-Mart
72Panera Bread
82TGI Fridays
92CVS
103Wal-Mart
113Target
123Lowes
133Nordstrom
144Panera Bread
154Lowes
164Wal-Mart
174Home Depot
185Lowes
195Target
205Panera Bread
215Wal-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))),"")
 
Upvote 0
Aladin Akyurek said:
ABCDEF
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))),"")
Click to expand...

Awesome. Will this work if there are multiple merchants in common?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Automate data export from multiple word tables into excel
Replies
8
Views
199
NdNoviceHlp
NdNoviceHlp
N
  • Question Question
Need help with organizing processes to proceed. Project / Challenge.
Replies
3
Views
468
noveske
N
U
  • Question Question
How to save only the rows that contain a value and ignore blank rows
Replies
1
Views
193
sijpie
sijpie
M
  • Question Question
Index Match formula pulling from a longer list with blank row spaces or unwanted data into a shorter list without row spaces
Replies
5
Views
380
Eric W
Eric W
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
146
BenW71
B

Forum statistics

Threads
1,224,746
Messages
6,180,705
Members
452,994
Latest member
Janick

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top