retrieving multiple cells correlated with another cell

[mike4777]

I have a list of coins that are bought at different exchanges in Column A. The exchanges are listed in column B. Some coins are bought from 2 or more exchanges so there may be entries that look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Coin A[/TD]
[TD]Exchange 1[/TD]
[/TR]
[TR]
[TD]Coin B[/TD]
[TD]Exchange 2[/TD]
[/TR]
[TR]
[TD]Coin A[/TD]
[TD]Exchange 3[/TD]
[/TR]
</tbody>[/TABLE]

I've created a summary table that lists balances by coins and I'm trying to include the exchange a coin was purchased at. I don't think index/match will work. This is what I'm looking for:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Coin A[/TD]
[TD]Exchange 1, Exchange 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Coin B[/TD]
[TD]Exchange 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

There may be several exchanges to be retrieved. Any ideas?

Thanks in advance

 

[Aladin Akyurek]

No, I am getting results like:

Exchange A, Exchange B, Exchange A
Exchange A, Exchange A

I only want each unique exchange displayed once. Also, how do I suppress the commas?

Remember, I may have the same coin put in the data column twice and it may have the same exchange displayed by each coin.

So we have duplicates. Also, it is not Exchange 1, Exchange 2, etc. but Exchange A, Exchange B...

The goal is no duplicates in the output, using ACONCAT, right?

 

[mike4777]

Thus you have

coin a, exchange 1
coin b, exchange 1
coin a, exchange 2
coin a, exchange 1

Otherwise, yo won't get duplicates...

Could you please modify the code to get rid of duplicates? I only want unique exchanges in the cell. I figured out the commas.

 

[Aladin Akyurek]

Could you please modify the code to get rid of duplicates? I only want unique exchanges in the cell. I figured out the commas.

In B2 of Summary control+shift+enter, not just enter:

=TRIM(aconcat(IF(FREQUENCY(IF(1-(Trades!$B$2:$B$5=""),IF(Trades!$B$2:$B$5=$A2,MATCH(Trades!$G$2:$G$5,Trades!$G$2:$G$5,0))),ROW(Trades!$G$2:$G$5)-ROW(Trades!$G$2)+1)," "&Trades!$G$2:$G$5,"")))

 

[mike4777]

In B2 of Summary control+shift+enter, not just enter:

=TRIM(aconcat(IF(FREQUENCY(IF(1-(Trades!$B$2:$B$5=""),IF(Trades!$B$2:$B$5=$A2,MATCH(Trades!$G$2:$G$5,Trades!$G$2:$G$5,0))),ROW(Trades!$G$2:$G$5)-ROW(Trades!$G$2)+1)," "&Trades!$G$2:$G$5,"")))

That is odd... your second to last post did not show up for me until just a few minutes ago but has a much earlier time stamp. I thought you had forgot about me. Thanks for your response.

That ended up changing the results so that only one value is displayed in the cell now as opposed to multiple values, just non-repeating ones.

 

[Aladin Akyurek]

That is odd... your second to last post did not show up for me until just a few minutes ago but has a much earlier time stamp. I thought you had forgot about me. Thanks for your response.

That ended up changing the results so that only one value is displayed in the cell now as opposed to multiple values, just non-repeating ones.

You are welcome. ACONCAT with FREQUENCY delivers a unique set of items, with a space as delimiter, corresponding to a coin of seletion, as you requested.