retrieving multiple cells correlated with another cell

M

mike4777

Board Regular
Joined
Apr 12, 2011
Messages
124
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 :-)
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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.
 
Upvote 0
Aladin Akyurek said:
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.
Click to expand...

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"
 
Last edited:
Upvote 0
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]
 
Upvote 0
mike4777 said:
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]
Click to expand...

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.​
 
Upvote 0
Aladin Akyurek said:
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.​
Click to expand...


Did you mean
Code: 
=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.
 
Upvote 0
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:
Rich (BB code): 
=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:
Rich (BB code): 
=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>
 
Upvote 0
Aladin Akyurek said:
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:
Rich (BB code): 
=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:
Rich (BB code): 
=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>
Click to expand...



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?
 
Last edited:
Upvote 0
mike4777 said:
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?
Click to expand...

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...
 
Upvote 0
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Help with coin-collecting sheet
Replies
3
Views
151
felixstraube
F
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
355
Jagadeshrao
J
Jeffrey Mahoney
  • Solved
Get Items From Descriptions Based on List
Replies
5
Views
401
Fluff
Fluff
F
  • Question Question
Merge multiple tables into one
Replies
3
Views
917
FotS
F
S
  • Question Question
Find the maximum of a cell on multiple sheets and print the name associated with that sheet even if one of the sheets has a "blank" maximum.
Replies
0
Views
321
slim6y
S

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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