Trying to use VLOOKUP to compare two tables

[JP2R]

Greetings All:

[Note: I have also posted this @ MS Excel General Q & A List <EXCEL-G@PEACH.EASE.LSOFT.COM>]


I'm trying to match two tables ...[maybe 'match' maybe the wrong word - or I'm just flat out using the wrong function]


Table 1 -

Has the following columns

A2= Interface

B2= Switch Nr

C2= Switch Port

D2= Floor

E2= Room

F2= Jack


Table 2 -

Has the following columns

P2= Room

Q2= Phone Extension

R2= Employee Name


The problem I'm having is the in Table 1 - column "E" (Room) there can be more than one "Room" listed because there is more than one "Jack" in each room

Example: Room 2F101 has Jack 2F-091, 2F-092, 2F-093


Table 2 - column "P"(Room) there can be more than one "Room" listed because there is more than one person occupying the room

Example: Room 2F101 - Extension 1234 - Mr. Smith

Room 2F101 - Extension 5647 - Mr. Jones


They are asking me to match up the rooms on both lists - but when I try - it only will show me one of the extensions [repeated twice] or the name repeated twice.

Some rooms can only hold one person , some can hold up to three How can I confirm that room 2F101 [for example] in Table 2 - shows up in Table 1?

-- Jae

 

[dan_l]

You must work with a lot of guys named Jack.


Can you share some of your data? Anonymized of course. I think whatever the solution is, it's going to be dependant upon how your data is organized - or how you'll have to reorganize to make this work.


On the other hand, and people hate it when I say this: sometimes Excel problems like these are as easy as opening up Access

 

[JP2R]

Dan -


The 'Jack' comment was great! I need to be more careful in how I arrange my words [smile]

I believe you are correct - the solution may very easily be on Access ... [I will check that out as well - that you for that insight]

-

As for the data:


Table 1

- - - - -


B2= Switch Nr C2= Switch Port D2= Floor E2= Room F2= Jack


4 37 2 2F101 2F-091

4 39 2 2F101 2F-092

4 33 2 2F105 2F-089

4 35 2 2F105 2F-090

5 36 2 2F105 2F-090

4 29 2 2F107 2F-087

4 31 2 2F107 2F-088

5 30 2 2F107 2F-087

4 25 2 2F109 2F-085

4 27 2 2F109 2F-086

4 21 2 2F111 2F-083

4 23 2 2F111 2F-084

4 17 2 2F113 2F-081

4 19 2 2F113 2F-082

5 18 2 2F113 2F-081

5 11 2 2F121 2F-030

5 13 2 2F121 2F-031


Table 2

- - - - -

P2= Room Q2= Phone Extension R2= Employee Name

2F022 44627 Security Closet

2F101 42629 Piescik-Conf

2F101 32936 Piescik,John

2F105 36371 Mahlum, Phil

2F107 32326 Noble, Gene

2F109 33144 Visitor Office

2F109 33802 Creps,Linda J.

2F111 32665 Wickes III,Harry E.

2F113 33127 Zerambo,Carl W.

2F121 37094 Ward-Conf 2F121

2F121 32359 Ward,Ellen P.

2F123 34827 Thelen,Richard P.

2F127 33794 Wilson, Jim

2F127 39951 Gross,Jason D

 

[dan_l]

Yeah I'm leaning access. Thing is, I'm sure you could spend time getting really cute with some formulas or going all vba, but after the 3 minutes I spent getting your data in to access, it took me all of 30 seconds to do this:


----

SELECT DISTINCT Table1.Room, Table2.Extension, Table2.Name

FROM Table1 INNER JOIN Table2 ON Table1.Room = Table2.Room;

----


Results in this:

Room Extension Name

2F101 32936 Piescik,John

2F101 42629 Piescik-Conf

2F105 36371 Mahlum, Phil

2F107 32326 Noble, Gene

2F109 33144 Visitor Office

2F109 33802 Creps,Linda J.

2F111 32665 Wickes III,Harry E.

2F113 33127 Zerambo,Carl W.

2F121 32359 Ward,Ellen P.

2F121 37094 Ward-Conf 2F121


Added benefit is:


------

SELECT DISTINCT Table1.Room, Table2.Extension, Table2.Name

FROM Table1 left JOIN Table2 ON Table1.Room = Table2.Room WHERE table2.extension IS NULL

------


would show you that the magic record I just made up in Table 1 is totally unoccupied, which if I'm looking at what I think I'm looking at, would probably be useful at some point.


Room Extension Name

Joe's Office

 

[dan_l]

I should add, I'm not saying that it's impossible to do with Excel. Really, you'd just have to make sure that you have one fixed list of rooms that you'll use to look up against a second list that's sorted by rooms and then just use the vlookup/count trick to merge the two. The only thing is, if thing thing were to turn into a....ahem....living document it may quickly become a resident of spreadsheet hell.


If you don't have access available, you can use excel query:

---------

SELECT DISTINCT table1.Room, table2.Extension, table2.Name

FROM

C:Documents and Settingsdan lDesktoptable1.xlsx.table1 table1, C:Documents and Settingsdan lDesktoptable2.xlsx

.table2 table2

WHERE table1.Room = table2.Room

---------

 

[JP2R]

Wow! Dan!

So Access is the answer ...


I have Access 2010 [now - thanks to your suggestion requested it from my boss] and will put the tables in like you suggest ... think of all the hair pullin' you just saved me ...


-- Jae

 

[JP2R]

Dan -


I'm sorry this has taken so long for me to reply, but I like to seriously try the examples provided ...


I did as you suggested and loaded Access [2010] and did the queries and set them up the way you mentioned ...


What was frustrating is that I cannot seem to get the query to allow for me to merge the two tables together ...


I either only get the info for all the Unique/Distinct matches from one table or the other ...


I would like to be able to list all the items ...


The Switch Nr / Switch Port / Floor / Room / Jack / Extension / Name

and if there is no match on to the Table 1 then I need to see that too - so that I can research and fill that data in ...


There are some rooms - that are conference rooms, for example, they have one phone in them ... but several jacks ... so it's important to get ...


Switch Nr / Switch Port / Floor / Room / Jack / Extension / Name

1 / 24 / 2 / 2F215 / 2F-001 / 33000 / Conference Room #2

1 / 25 / 2 / 2F215 / 2F-002 / - /

1 / 26 / 2 / 2F215 / 2F-003 / - /


So I tried it in Excel using helper columns and I tried in Access and I've just about pulled my hair out ...


I'm sure that it's something super simple that I'm overlooking ...

Any suggestions?


-- Jae

 

[GN0001]

This might help you:


If you are going to do a VLookup and your data is repeated in lookup column, you can use this formula to create a helper column by using this function.


C5&Countif($C$5:C5,C5)


Then you can do a vLookup.

Thanks

Guity

 

[JP2R]

Guity :


Thank you for your suggestion - I am going to give this a try and see how this formula works and how to use it as you are suggesting ...


Muchly appreciated

-- Jae

 

[JP2R]

Guity:


I took the formula you suggested - it wasn't clear to me where to use this, whether in Table 1 or in Table 2 - here is what I tried


---

Table 1


A2 =Switch nr/ B2 =Switch Port / C2 =Flr / D2 =Room / E2 =Jack / F2 =Match/no Match /G2 = Helper Clmm


Table 2

O2 = Helpr Clmn / P2 = Match/no Match / Q2 = Phone Ext / R2 = Employee Name


---> Where am I to and how am I to use the "Helper Column"?


I'm trying take Table 1 and have it look at Table 2

If there is a match say so

Then if it matches please provide me with the phone extension and the name of the person


It works well for a single room with a single jack,phone extension, and name

where it breaks is in a room where there is more than one jack ... for example on a conference room

all I would need in that room is one phone, but happens is that that phone extension populates all the way down through the empty jacks ...


Or if there are two people in a room - I only get the one person and extension for both jacks ...


Your solution has helped to eliminate that frustration/result, but it still wants to fill in a conference room with one name and number all the way down all the empty jacks ...

not sure how to fix that ...

 

[JP2R]

All -


I think I have it - it sure isn't pretty - or very quick - but it will get the job done ...

if interested I will share the sheet - otherwise take my word for it - all you Gurus out there will just cry ... but it works ...


Thank you for your help

-- Jae