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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
1Select Worker
2JobLeadLead$Worker1Worker1$Worker2Worker2$
3Sell ApplesJohn DoeDude BroDude BroDude BroDude BroDude Bro
4Sell ApplesJane DoeDude BroDude BroDude BroDude BroDude Bro
5Clean CarpetDude BroDude BroDude BroDude BroDude BroDude Bro
6Clean CarpetDude BroJohn DoeJane DoeDude BroDude BroDude Bro
7Buy Cleaning ProductsDude BroDude BroDude BroDude BroDude BroDude Bro
8Sell WatermelonJane DoeDude BroDude BroDude BroDude BroDude Bro
9Load TrucksDude BroDude BroDude BroDude BroDude BroDude Bro
10Clean WindowsDude BroDude BroJane DoeJohn DoeDude BroDude Bro
11Write reportsDude BroDude BroDude BroDude BroDude BroDude Bro
12Clean WindowsJane DoeDude BroDude BroDude BroDude BroDude Bro
13Sell ApplesDude BroDude BroDude BroDude BroDude BroDude Bro
14Sell WatermelonDude BroDude BroJohn DoeDude BroDude BroDude Bro
15Sell WatermelonDude BroDude BroDude BroDude BroDude BroDude Bro
16Load TrucksDude BroDude BroDude BroDude BroDude BroDude Bro
17Clean CarpetDude BroDude BroDude BroDude BroDude BroJane Doe
18Sell ApplesDude BroDude BroDude BroDude BroDude BroDude Bro
19Buy Cleaning ProductsDude BroDude BroDude BroDude BroJohn DoeDude Bro
20Unload TrucksDude BroDude BroDude BroJane DoeDude BroDude Bro
21Sell SodaDude BroDude BroJane DoeDude BroDude BroDude Bro
22Sell SodaDude BroJohn DoeDude BroDude BroDude BroDude Bro
23Clean WindowsJane DoeDude BroDude BroDude BroDude BroDude Bro
24Unload TrucksDude BroDude BroDude BroDude BroDude BroJohn Doe
25Load TrucksDude BroDude BroJane DoeDude BroDude BroDude Bro
26Sell SodaDude BroDude BroDude BroJane DoeDude BroDude Bro
27Clean CarpetCool GuyCool GuyCool GuyCool GuyJane DoeCool Guy
28Buy Cleaning ProductsCool GuyJohn DoeCool GuyJane DoeCool GuyCool Guy
29Write reportsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
30Clean CarpetCool GuyCool GuyCool GuyJane DoeCool GuyCool Guy
31Sell ApplesCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
32Unload TrucksJane DoeCool GuyCool GuyJane DoeCool GuyJohn Doe
33Sell WatermelonCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
34Buy Cleaning ProductsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
35Sell ApplesCool GuyJane DoeJohn DoeCool GuyCool GuyCool Guy
36Clean WindowsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
37Clean CarpetJane DoeCool GuyCool GuyCool GuyCool GuyCool Guy
38Load TrucksCool GuyJohn DoeCool GuyCool GuyCool GuyCool Guy
39Write reportsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
40Sell SodaCool GuyCool GuyJane DoeCool GuyCool GuyCool Guy
41Sell WatermelonCool GuyCool GuyCool GuyJohn DoeCool GuyCool Guy
42Unload TrucksCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
43Clean WindowsCool GuyJohn DoeCool GuyCool GuyCool GuyCool Guy
44Write reportsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
45Sell WatermelonCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
46Sell SodaCool GuyCool GuyJane DoeCool GuyCool GuyCool Guy
47Unload TrucksCool GuyCool GuyCool GuyCool GuyCool GuyJohn Doe
48Buy Cleaning ProductsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
49Clean WindowsCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
50Load TrucksCool GuyCool GuyCool GuyCool GuyCool GuyCool Guy
51Sell SodaCool GuyCool GuyCool GuyCool GuyCool GuyJane Doe
52Write reportsJohn DoeCool GuyCool GuyCool GuyCool GuyCool Guy

<tbody>
</tbody>
Email Campaign Stats​

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

Excel 2012
ABCDEFG
1Select WorkerJohn Doe

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

Excel 2012
ABCDEFG
1Select WorkerJohn Doe
2JobLeadLead$Worker1Worker1$Worker2Worker2$
3Sell ApplesJohn DoeDude BroDude BroDude BroDude BroDude Bro
6Clean CarpetDude BroJohn DoeJane DoeDude BroDude BroDude Bro
10Clean WindowsDude BroDude BroJane DoeJohn DoeDude BroDude Bro
14Sell WatermelonDude BroDude BroJohn DoeDude BroDude BroDude Bro
19Buy Cleaning ProductsDude BroDude BroDude BroDude BroJohn DoeDude Bro
22Sell SodaDude BroJohn DoeDude BroDude BroDude BroDude Bro
24Unload TrucksDude BroDude BroDude BroDude BroDude BroJohn Doe
28Buy Cleaning ProductsCool GuyJohn DoeCool GuyJane DoeCool GuyCool Guy
32Unload TrucksJane DoeCool GuyCool GuyJane DoeCool GuyJohn Doe
35Sell ApplesCool GuyJane DoeJohn DoeCool GuyCool GuyCool Guy
38Load TrucksCool GuyJohn DoeCool GuyCool GuyCool GuyCool Guy
41Sell WatermelonCool GuyCool GuyCool GuyJohn DoeCool GuyCool Guy
43Clean WindowsCool GuyJohn DoeCool GuyCool GuyCool GuyCool Guy
47Unload TrucksCool GuyCool GuyCool GuyCool GuyCool GuyJohn Doe
52Write reportsJohn DoeCool GuyCool GuyCool GuyCool GuyCool Guy

<tbody>
</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
Can a macro help create a user hierarchy?
Replies
7
Views
238
Akuini
Akuini
W
  • Solved
Moving/transferring data between tables with matching column names, not by column order - vba
Replies
5
Views
291
Alex Blakenburg
A
A
  • Question
Refresh a Pivot table with group fields gives errors.
Replies
0
Views
44
axelg
A
N
  • Question
MATCH and INDEX help
Replies
3
Views
105
etaf
etaf
B
  • Question
Button to scroll from previous or next row of data from table
Replies
6
Views
79
JerrExcel
JerrExcel

Forum statistics

Threads
1,215,483
Messages
6,125,063
Members
449,206
Latest member
Healthydogs

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