Filtering a table with matching data in 2 columns.

E

excelhunk

New Member
Joined
Sep 3, 2017
Messages
6
Hey Excel Wizards,

I need some help.

I have some data that has names in multiple columns. These columns share the same list of names. It is a table tracking who was the lead on a job and who worked with him.

Now what I want to do is be able to pull all the data associated with a name no matter what column it is in. That way I could print a report for the one name of all the jobs he worked on.

It looks something like this:
Col.A, Col.B, Col.C, Col.D, Col.E...
Job, Lead, Lead$, Worker1, Worker1$, Worker2, Worker2$

I hope this is enough information that I can get some help. I have tried Pivot Tables, Vlookups, Matches, Filtering and sorting but nothing seemed correct. I feel I am on the right track just not getting it.

Thank you.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
I think I got it right, if not please let me know how your data looks like and how it should look like after the code ran. But try this:

If your data looks like this


Excel 2012
ABCDEFG
Select Worker
JobLeadLead$Worker1Worker1$Worker2Worker2$
Sell ApplesJohn DoeDude BroDude BroDude BroDude BroDude Bro
Sell ApplesJane DoeDude BroDude BroDude BroDude BroDude Bro
Clean CarpetDude BroDude BroDude BroDude BroDude BroDude Bro
Clean CarpetDude BroJohn DoeJane DoeDude BroDude BroDude Bro
Buy Cleaning ProductsDude BroDude BroDude BroDude BroDude BroDude Bro
Sell WatermelonJane DoeDude BroDude BroDude BroDude BroDude Bro
Load TrucksDude BroDude BroDude BroDude BroDude BroDude Bro
Clean WindowsDude BroDude BroJane DoeJohn DoeDude BroDude Bro
Write reportsDude BroDude BroDude BroDude BroDude BroDude Bro
Clean WindowsJane DoeDude BroDude BroDude BroDude BroDude Bro
Sell ApplesDude BroDude BroDude BroDude BroDude BroDude Bro
Sell WatermelonDude BroDude BroJohn DoeDude BroDude BroDude Bro
Sell WatermelonDude BroDude BroDude BroDude BroDude BroDude Bro
Load TrucksDude BroDude BroDude BroDude BroDude BroDude Bro
Clean CarpetDude BroDude BroDude BroDude BroDude BroJane Doe
Sell ApplesDude BroDude BroDude BroDude BroDude BroDude Bro
Buy Cleaning ProductsDude BroDude BroDude BroDude BroJohn DoeDude Bro
Unload TrucksDude BroDude BroDude BroJane DoeDude BroDude Bro
Sell SodaDude BroDude BroJane DoeDude BroDude BroDude Bro
Sell SodaDude BroJohn DoeDude BroDude BroDude BroDude Bro
Clean WindowsJane DoeDude BroDude BroDude BroDude BroDude Bro
Unload TrucksDude BroDude BroDude BroDude BroDude BroJohn Doe
Load TrucksDude BroDude BroJane DoeDude BroDude BroDude Bro
Sell SodaDude BroDude BroDude BroJane DoeDude BroDude Bro
Clean CarpetCool GuyCool GuyCool GuyCool GuyJane DoeCool Guy
Buy Cleaning ProductsCool GuyJohn DoeCool GuyJane DoeCool GuyCool Guy
Write reportsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Clean CarpetCool GuyCool GuyCool GuyJane DoeCool GuyCool Guy
Sell ApplesCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Unload TrucksJane DoeCool GuyCool GuyJane DoeCool GuyJohn Doe
Sell WatermelonCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Buy Cleaning ProductsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Sell ApplesCool GuyJane DoeJohn DoeCool GuyCool GuyCool Guy
Clean WindowsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Clean CarpetJane DoeCool GuyCool GuyCool GuyCool GuyCool Guy
Load TrucksCool GuyJohn DoeCool GuyCool GuyCool GuyCool Guy
Write reportsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Sell SodaCool GuyCool GuyJane DoeCool GuyCool GuyCool Guy
Sell WatermelonCool GuyCool GuyCool GuyJohn DoeCool GuyCool Guy
Unload TrucksCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Clean WindowsCool GuyJohn DoeCool GuyCool GuyCool GuyCool Guy
Write reportsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Sell WatermelonCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Sell SodaCool GuyCool GuyJane DoeCool GuyCool GuyCool Guy
Unload TrucksCool GuyCool GuyCool GuyCool GuyCool GuyJohn Doe
Buy Cleaning ProductsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Clean WindowsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Load TrucksCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
Sell SodaCool GuyCool GuyCool GuyCool GuyCool GuyJane Doe
Write reportsJohn DoeCool GuyCool GuyCool GuyCool GuyCool Guy

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]36[/TD]

[TD="align: center"]37[/TD]

[TD="align: center"]38[/TD]

[TD="align: center"]39[/TD]

[TD="align: center"]40[/TD]

[TD="align: center"]41[/TD]

[TD="align: center"]42[/TD]

[TD="align: center"]43[/TD]

[TD="align: center"]44[/TD]

[TD="align: center"]45[/TD]

[TD="align: center"]46[/TD]

[TD="align: center"]47[/TD]

[TD="align: center"]48[/TD]

[TD="align: center"]49[/TD]

[TD="align: center"]50[/TD]

[TD="align: center"]51[/TD]

[TD="align: center"]52[/TD]

</tbody>
Email Campaign Stats​

and via a Data validation list you select a worker name here like this:

Excel 2012
ABCDEFG
Select Worker

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]John Doe[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Email Campaign Stats​
You will get this:

Excel 2012
ABCDEFG
Select Worker
JobLeadLead$Worker1Worker1$Worker2Worker2$
Sell ApplesJohn DoeDude BroDude BroDude BroDude BroDude Bro
Clean CarpetDude BroJohn DoeJane DoeDude BroDude BroDude Bro
Clean WindowsDude BroDude BroJane DoeJohn DoeDude BroDude Bro
Sell WatermelonDude BroDude BroJohn DoeDude BroDude BroDude Bro
Buy Cleaning ProductsDude BroDude BroDude BroDude BroJohn DoeDude Bro
Sell SodaDude BroJohn DoeDude BroDude BroDude BroDude Bro
Unload TrucksDude BroDude BroDude BroDude BroDude BroJohn Doe
Buy Cleaning ProductsCool GuyJohn DoeCool GuyJane DoeCool GuyCool Guy
Unload TrucksJane DoeCool GuyCool GuyJane DoeCool GuyJohn Doe
Sell ApplesCool GuyJane DoeJohn DoeCool GuyCool GuyCool Guy
Load TrucksCool GuyJohn DoeCool GuyCool GuyCool GuyCool Guy
Sell WatermelonCool GuyCool GuyCool GuyJohn DoeCool GuyCool Guy
Clean WindowsCool GuyJohn DoeCool GuyCool GuyCool GuyCool Guy
Unload TrucksCool GuyCool GuyCool GuyCool GuyCool GuyJohn Doe
Write reportsJohn DoeCool GuyCool GuyCool GuyCool GuyCool Guy

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]John Doe[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]38[/TD]

[TD="align: center"]41[/TD]

[TD="align: center"]43[/TD]

[TD="align: center"]47[/TD]

[TD="align: center"]52[/TD]

</tbody>
Email Campaign Stats​

Using this code (You could assign the code to a button or to your Excel add ins)

Code: 
Sub easybreezybeautifulcovergirl()

Dim i, lrow As Long
Dim j As String


ActiveSheet.Cells.EntireRow.Hidden = False
lrow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
j = Cells(1, 2).Value


For i = lrow To 3 Step -1
    If (Cells(i, 2) <> j) And (Cells(i, 3) <> j) And (Cells(i, 4) <> j) And (Cells(i, 5) <> j) And (Cells(i, 6) <> j) And (Cells(i, 7) <> j) Then
    Rows(i).EntireRow.Hidden = True
    End If
Next


End Sub
 
Upvote 0
Hi Truiz.

Thank you for your assistance. Unfortunately I am not able to use this approach. I am sharing this file and others are adding their own edits to it through Excels online platform. It is very basic in its abilities it appears and it doesn't seem to play nice with coding.

I do believe I did find another work around though with using a pivot table. I have yet to see if this transfer through the online version.

Another issue I am running into is that I have designed this spreadsheet on my computer which has 2016 and the company I work is being cheap and doesn't to upgrade from their 2010 version. Not exactly sure though but this seems to be causing communication problems.

Thank you again though.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Can a macro help create a user hierarchy?
Replies
7
Views
688
Akuini
Akuini
M
  • Solved
Using Filter formula to return a data table
Replies
5
Views
177
dreid1011
dreid1011
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
390
ChrisMac1
ChrisMac1
R
  • Question Question
Help auto filtering dynamic data table
Replies
1
Views
160
russelldt
R
T
  • Question Question
help with data shared between tables
Replies
0
Views
208
The_Flapjack
T

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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