Bilateral lookup

[rayoflight]

First time poster here. Nice to meet you all :-)

I hope someone can help me with the following. I have a spreadsheet with around 10k rows of data. Each row contains a sender name, a recipient name and an amount. There are a around 100 unique senders/receivers, with almost all senders receiving and vv.

What I am trying to find out is which two individuals send/receive the most $ between them. I have added one column to concatenate the sender and receiver name which tells me how much went from each sender to each receiver. But that doesnt help me as I need to know how much went from A-->B and from B-->A in total.

Any suggestions?

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Spencer101]

With no sight of example data it's hard to tell, but would INDEX(MATCH,MATCH)+INDEX(MATCH,MATCH) work?

It would look up transaction going one way between two people and add to that the transaction between the same two people going the other way.

If you can provide example data I will show you what I mean. Not easy to explain.




EDIT: I had a completely different data layout in mind when writing this. I think KevinUK might be on to the right solution.

[Kevin UK]

Hi rayoflight

It is unclear without a spread sheet, but may be!
In column A: Senders
In column B: Receivers
In column c: Amounts

=MAX(IF((A:A="Name")*(B:B="Name"),C:C))
Array formula, to commit. CTRL + SHIFT + ENTER

[rayoflight]

Transfers.xlsxpic transfer.jpg

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Sorry about not including the workbook; attached a simplified example.

I have the section in rows 3-8. What I am trying to get to is a summary as in rows 11-14. I tried Kevin's solution but I cannot make it work.

Thanks again!

[rayoflight]

Any ideas anyone?

[popipipo]

Maybe this will do it

[shg]

You could add a column and then use a pivot table:

Please Login or Register  to view this content.

The formula in D2 and copied down is

=IF(A2<=B2, A2 & "|" & B2, B2 & "|" & A2)

[rayoflight]

Thanks popipipo and shg!

shg - like the simplicity of your approach. Never thought of using < and > to sort text rather than numbers. Good one.

Thanks!