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]

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

=TEXTJOIN(", ",1,IF(Data!$A$2:$A$4=$A2,$B$2:$B$4,""))

If TEXTJOIN is not available on your system, we can either a UDF (ACONCAT) to the same effect or place the results of each coin in cells of their own.

 

[mike4777]

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

=TEXTJOIN(", ",1,IF(Data!$A$2:$A$4=$A2,$B$2:$B$4,""))

If TEXTJOIN is not available on your system, we can either a UDF (ACONCAT) to the same effect or place the results of each coin in cells of their own.

I don't understand how the formula plugs in. The B column above has nothing in it yet... this is where the formula I'm asking for will go to retrieve the exchanges. I have another sheet with all the coins and the exchanges they were bought at (coins in column B and exchanges in column G). The tab the data is on is "Trades"

 

[mike4777]

Summary Tab:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Coin[/TD]
[TD]formula to retrieve exchanges[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Trades Tab (where the raw data is):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column B[/TD]
[TD]Column G[/TD]
[/TR]
[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]

 

[Aladin Akyurek]

Summary Tab:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Coin[/TD]
[TD]formula to retrieve exchanges[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Trades Tab (where the raw data is):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column B[/TD]
[TD]Column G[/TD]
[/TR]
[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]

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

=TEXTJOIN(", ",1,IF(Trades!$A$2:$A$4=$A2,Trades$B$2:$B$4,""))

If TEXTJOIN is not available on your system, we can either a UDF (ACONCAT) to the same effect or place the results of each coin in cells of their own.​

 

[mike4777]

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

=TEXTJOIN(", ",1,IF(Trades!$A$2:$A$4=$A2,Trades$B$2:$B$4,""))

If TEXTJOIN is not available on your system, we can either a UDF (ACONCAT) to the same effect or place the results of each coin in cells of their own.​

Did you mean

=TEXTJOIN(", ",1,IF(Trades!$B$2:$B$1003=$A2,Trades!$G$2:$G$1003,""))

I'm getting a name error. I am entering it as an array like you said.

 

[Aladin Akyurek]

That means the native TEXTJOIN function is not available on your Excel.

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

=REPLACE(aconcat(IF(Trades!$A$2:$A$4=$A2,", "&Trades!$B$2:$B$4,"")),1,2,"")
​

where A2 = Coin A.

For this formula to work, add the following code for ACONCAT as a Module to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function


If you don't want a udf (ACONCAT is a udf), then:

In B2 of Summary, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$2:$B$4,SMALL(IF($A$2:$A$4=$A2,ROW(
$B$2:$B$4​
)-ROW(
$B$2)+1),ROWS($B2:B2))),"")
​

​

​

<strike></strike><strike></strike>

 

[mike4777]

That means the native TEXTJOIN function is not available on your Excel.

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

=REPLACE(aconcat(IF(Trades!$A$2:$A$4=$A2,", "&Trades!$B$2:$B$4,"")),1,2,"")
​

where A2 = Coin A.

For this formula to work, add the following code for ACONCAT as a Module to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function


If you don't want a udf (ACONCAT is a udf), then:

In B2 of Summary, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$2:$B$4,SMALL(IF($A$2:$A$4=$A2,ROW(
$B$2:$B$4​
)-ROW(
$B$2)+1),ROWS($B2:B2))),"")
​

​

​

<strike></strike><strike></strike>

The code appears to be working but it is pulling duplicates the same exchange. Is there a way to get rid of this? Also, on cells where there isn't a coin listed yet it is displaying ",,,,,,,,,,,,,,,,,,,,,,,,,," How can I get rid of this?

 

[Aladin Akyurek]

The code appears to be working but it is pulling duplicates the same exchange. Is there a way to get rid of this? Also, on cells where there isn't a coin listed yet it is displaying ",,,,,,,,,,,,,,,,,,,,,,,,,," How can I get rid of this?

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

 

[mike4777]

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.