display subarray from the name of another tab

C

Cedric51

New Member
Joined
Oct 20, 2016
Messages
3
hi all,

I have two tabs: DATA1 and DATA2 as followed:

DATA1
-------

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]VAL1[/TD]
[TD]VAL2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]



DATA2
-------
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]NAME[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]john[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]eric[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]paul[/TD]
[/TR]
</tbody>[/TABLE]

Using the unique ID column which linked the data in the two tabs, when I select 'john', I want to get the following subarray:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]VAL1[/TD]
[TD]VAL2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

when I select 'eric', I want to get the following subarray:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]VAL1[/TD]
[TD]VAL2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

when I select 'paul', I want to get the following subarray:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]VAL1[/TD]
[TD]VAL2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]


any idea?

thank you
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
If you have excel2013+ you could use the 'datamodal' pivot table to do things like that.
 
Upvote 0
Try this in Tab "DatA2"
"Data1" data starts "A1 in sheet "Data1"
Data2" data start "A1" in sheets "Data2"
ND:- This is a "Selection change Event" and Results start "D1" in sheets "Data2" after Selection from Column 2 sheets "Data2"
To load code Right click tab of sheet "Data2" select "View Code" , vbwindow appears , Paste code Into vbwindow, close Vbwindow.

Code: 
Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Q [COLOR=navy]As[/COLOR] Variant, nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object, K [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]If[/COLOR] Target.Column = 2 And Target.Count = 1 [COLOR=navy]Then[/COLOR]
    nStr = Target.Offset(, -1).Value
    Range("D:E").Clear
[COLOR=navy]With[/COLOR] Sheets("Data1")
    [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
    [COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        ReDim Ray(1 To 2, 1 To Rng.Count)
        Ray(1, 1) = "Val1": Ray(2, 1) = "Val2"
        Ray(1, 2) = Dn.Offset(, 1).Value
        Ray(2, 2) = Dn.Offset(, 2).Value
        Dic.Add Dn.Value, Array(Ray, 2)
    [COLOR=navy]Else[/COLOR]
        Q = Dic(Dn.Value)
            Q(1) = Q(1) + 1
            Q(0)(1, Q(1)) = Dn.Offset(, 1)
            Q(0)(2, Q(1)) = Dn.Offset(, 2)
        Dic(Dn.Value) = Q
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] Dic.keys
    [COLOR=navy]If[/COLOR] K = nStr [COLOR=navy]Then[/COLOR]
        [COLOR=navy]With[/COLOR] Sheets("Data2").Range("D1").Resize(Dic(K)(1), 2)
            .Value = Application.Transpose(Dic(K)(0))
            .Borders.Weight = 2
            [COLOR=navy]Exit[/COLOR] For
        [COLOR=navy]End[/COLOR] With
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Hi All,

using formulas, in A1:C8 first table, in E1:F3 second table, in H1 selected name, in H2 to be copied across (thanks to XOR LX)


Excel 2010 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]
A​
[/TH]
[TH]
B​
[/TH]
[TH]
C​
[/TH]
[TH]
D​
[/TH]
[TH]
E​
[/TH]
[TH]
F​
[/TH]
[TH]
G​
[/TH]
[TH]
H​
[/TH]
[TH]
I​
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
1​
[/TD]
[TD="bgcolor: #FAFAFA"]ID[/TD]
[TD="bgcolor: #FAFAFA"]VAL1[/TD]
[TD="bgcolor: #FAFAFA"]VAL2[/TD]
[TD][/TD]
[TD]
ID​
[/TD]
[TD]NAME[/TD]
[TD][/TD]
[TD="bgcolor: #FFC000"]john[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
2​
[/TD]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD][/TD]
[TD="bgcolor: #EBF1DE"]
1​
[/TD]
[TD="bgcolor: #EBF1DE"]john[/TD]
[TD][/TD]
[TD="bgcolor: #FFC000"]
=IFERROR(INDEX(B$2:B$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A1))),"")​
[/TD]
[TD="bgcolor: #FFC000"]
=IFERROR(INDEX(C$2:C$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A1))),"")​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
3​
[/TD]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD][/TD]
[TD="bgcolor: #EBF1DE"]
2​
[/TD]
[TD="bgcolor: #EBF1DE"]eric[/TD]
[TD][/TD]
[TD="bgcolor: #FFC000"]
=IFERROR(INDEX(B$2:B$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A2))),"")​
[/TD]
[TD="bgcolor: #FFC000"]
=IFERROR(INDEX(C$2:C$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A2))),"")​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
4​
[/TD]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD][/TD]
[TD="bgcolor: #EBF1DE"]
3​
[/TD]
[TD="bgcolor: #EBF1DE"]paul[/TD]
[TD][/TD]
[TD="bgcolor: #FFC000"]
=IFERROR(INDEX(B$2:B$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A3))),"")​
[/TD]
[TD="bgcolor: #FFC000"]
=IFERROR(INDEX(C$2:C$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A3))),"")​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
5​
[/TD]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFC000"]=IFERROR(INDEX(B$2:B$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A4))),"")[/TD]
[TD="bgcolor: #FFC000"]=IFERROR(INDEX(C$2:C$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A4))),"")[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
6​
[/TD]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFC000"]=IFERROR(INDEX(B$2:B$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A5))),"")[/TD]
[TD="bgcolor: #FFC000"]=IFERROR(INDEX(C$2:C$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A5))),"")[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
7​
[/TD]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IFERROR(INDEX(B$2:B$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A6))),"")[/TD]
[TD]=IFERROR(INDEX(C$2:C$8,AGGREGATE(15,6,ROW($2:$8)-1/--(VLOOKUP(N(IF({1},$A$2:$A$8)),$E$2:$F$4,2,0)=$H$1),ROWS($A$1:$A6))),"")[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]
8​
[/TD]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Foglio1[/TD]
[/TR]
</tbody>[/TABLE]


Hope it helps
 
Last edited:
Upvote 0
@Mick
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'why bother collecting all userdata in a dictionary
'if you only want to see data from one user?

    Dim nStr As String, Rng As Range, Dn As Range, r As Long
    
    If Target.Column = 2 And Target.Count = 1 Then
        nStr = Target.Offset(, -1).Value
        Range("D:E").Clear
        With Sheets("Data1")
            Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
        End With
        r = 1
        For Each Dn In Rng
            If Dn.Text = nStr Then
                r = r + 1
                Cells(r, 4) = Dn.Offset(, 1)
                Cells(r, 5) = Dn.Offset(, 2)
            End If
        Next Dn
    End If
End Sub
 
Upvote 0
ask2tsp
You're absolutely right, I was trying to make the code more interesting to do !!!
In my enthusiasm I lost control of the plot !!!!
 
Upvote 0
hi,

Unfortunately, I have Excel 2010 so I have to use the vb solution. Thank you for your solutions!

Regards,

Cedric
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Get IAccessible of UserForm control without reference
2
Replies
12
Views
1K
Jaafar Tribak
Jaafar Tribak
B
  • Solved
Macro to Open new tab and duplicate the data from the template, and rename new tab from cell value of template.
Replies
3
Views
546
mumps
mumps
N
  • Question Question
Excel VBA: Form to be filled in with data from another worksheet
2
Replies
15
Views
481
MikeVol
MikeVol
C
  • Question Question
Tab Selection from Userform based on Date range
Replies
5
Views
111
campbell1093
C
S
  • Solved
Table comparison using formulas
Replies
2
Views
99
sharshra
S

Forum statistics

Threads
1,223,237
Messages
6,170,930
Members
452,367
Latest member
TePunaBloke

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