Calculating number of matching relationships between two columns

K

kaisered

New Member
Joined
Sep 6, 2017
Messages
2
Hi, First of all apologies if I've not titled this correctly (it might be why my days of searching have returned nothing).
I have two columns of data one showing storage locations the second showing invoice numbers. What I'm trying to achieve is a way to find out how many times any location is found with any other location on the same invoice number, so from the sample below SAA11 and SEC41 have sold together once. Hopefully that makes sense, the full list is over 3000 rows and would ideally be a lot larger to give more accurate information.

[TABLE="width: 133"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]
[TABLE="width: 133"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Location
[/TD]
[TD="width: 114, bgcolor: transparent"]Invoice
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SAA11
[/TD]
[TD="bgcolor: transparent, align: right"]85978161
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SEC41
[/TD]
[TD="bgcolor: transparent, align: right"]85978161
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SAD22
[/TD]
[TD="bgcolor: transparent, align: right"]85978162
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SHF53
[/TD]
[TD="bgcolor: transparent, align: right"]85978162
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SKA73
[/TD]
[TD="bgcolor: transparent, align: right"]85978162
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SJB41
[/TD]
[TD="bgcolor: transparent, align: right"]85978163
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SBD71
[/TD]
[TD="bgcolor: transparent, align: right"]85978164
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SAB21
[/TD]
[TD="bgcolor: transparent, align: right"]85978165
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SAC11
[/TD]
[TD="bgcolor: transparent, align: right"]85978165
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SEA52
[/TD]
[TD="bgcolor: transparent, align: right"]85978165
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SFA71
[/TD]
[TD="bgcolor: transparent, align: right"]85978165
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Welcome to the board. A pivot table should be able to show you this relationship
 
Last edited:
Upvote 0
Thanks for the welcome and quick reply.
I'm not too good with pivot tables but I'd thought the same thing and had a bash a couple of days ago but didn't get what I was after. (I'm still not sure I've explained the problem properly to be honest as I can usually muddle through with a bit of searching). Maybe the mockup below will be clearer. From that I can see that on 3 different invoices SAA11 was accessed with SAA12.
[TABLE="width: 480"]
<colgroup><col width="64" style="width: 48pt;" span="10"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SAA11 [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SAA12 [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SAA21 [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SAB11 [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SAB21 [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SAB22 [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SAB23 [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SAC11 [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SAC21 [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SAA11 [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]3[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]5[/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]3[/TD]
[TD="class: xl66, bgcolor: transparent"]7[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SAA12 [/TD]
[TD="class: xl66, bgcolor: transparent"]3[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]6[/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SAA21 [/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"]3[/TD]
[TD="class: xl66, bgcolor: transparent"]7[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SAB11 [/TD]
[TD="class: xl66, bgcolor: transparent"]5[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]5[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SAB21 [/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"]6[/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"]5[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]5[/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SAB22 [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"]3[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]5[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SAB23 [/TD]
[TD="class: xl66, bgcolor: transparent"]3[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]7[/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]5[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SAC11 [/TD]
[TD="class: xl66, bgcolor: transparent"]7[/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]5[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SAC21 [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]9[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
With a pivot table in the rows group you use the field Invoice and under that field you insert Location and in the totals column make sure it's the count of.

You may want to watch a few YouTube videos on Pivot Table if reading about them isn't facilitating, however, whilst above can be achieved with a macro (I do not believe it can with a formula but someone else on this board may know how), you're asking for the wheel to be re-invented when a perfectly usable Excel feature already exists.

Lets see if someone suggests a coded solution, though my recommendation would still be use and therefore learn about pivot tables.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro to compare two sheets with identical column headers and first column, then showing all table differences in third sheet
Replies
1
Views
217
DanteAmor
DanteAmor
S
  • Question Question
Excel Customer List to Word Offer and Invoice template, with unique invoice number list and buttons to create
Replies
0
Views
514
_SGX_
S
M
  • Solved
Converting numbers stored as text to regular numbers query (VBA)
Replies
4
Views
1K
Alex Blakenburg
A
M
  • Question Question
Calculating Percentage between two columns
Replies
0
Views
1K
Mr Steve Cox
M
N
  • Question Question
Stacked Chart to show value and status YTD
Replies
2
Views
802
neilo1969
N

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

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