Compare multiple columns and return data from additional cell.

tidak

New Member
Joined
Jan 7, 2014
Messages
13
Hi everyone,

I have just started a new job today and have been asked to analyze some data in Excel. My Excel skills are pretty much limited to autosum so I'm hoping someone can help please so I don't look stupid on my first week at work! I'd like to point out my new boss knows my excel skills are zero, so I didn't get the job through deception :)

I have a spreadsheet with 2 worksheets, they have a different number of entries and no definitive reference data so I'm being forced to match on firstname and lastname. Here is an example:


Worksheet 1:
firstnamelastnametitleemail address
willywonkaceowilly@wonka.com
walterwhitechefwalter@white.com
phildunphysales managerphil@modern.com
cosmokramerjestercosmo@seinfeld.com
waltercronkiteannouncerwalter@radio.com

<tbody>
</tbody>

Worksheet 2:
firstnamelastnamesubscriptionpaid up
amandahugnkissyesyes
willywonkayesno
cosmokramernono
seymorebuttsyesno
amandaseyfriednoyes

<tbody>
</tbody>


As you can see, the only option I have to match on is a combination firstname and lastname. Whilst this certainly isn't perfect, it should get me most of the way there. So here is what I'm trying to do:

Compare the firstname and lastname in worksheet A to the firstname and lastname in sheet B.
If there is a match, copy the lastname and email address from sheet A, and the subscription status from worksheet B to a new worksheet.

So given the data above, this is the output I'm looking for:

lastnameemail addresssubscription
wonkawilly@wonka.comyes
cosmocosmo@seinfeld.comno

<tbody>
</tbody>

At present I'm working through this manually as I just don't know how to automate the process, but there are HUNDREDS of entries in both worksheets and doing it manually just isn't practical, not to mention the likelihood of errors.

I have tried searching for a solution before posting here, but I'm not even sure what to search for, and I have no idea the formulas and function I should be using.

If anyone can assist I would be forever grateful!!!

Many thanks,

Matt.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
hi, Matt

Save the file. ALT-D-D-N, chose Excel files as the data source, select your workbook, OK. If you get a message about no visible tables just OK to acknowledge then from options select system tables. Pick something - anything - and continue to the end then choose the option to edit in MS Query. Via the SQL button edit the text you have. Change it to,
Rich (BB code):
SELECT A.lastname, A.[email address], B.subscription
FROM [your worksheet1 name$] A, [your worksheet2 name$] B
WHERE A.lastname = B.lastname AND A.firstname = B.firstname
OK to enter this and see the required dataset. Hit the 'open door' icon and complete the taks of having the data on a (new) worksheet.

Tidak apa apa. OK?
 

Pimagine

New Member
Joined
Dec 11, 2013
Messages
25
Hi Fazza,

Interesting solution! I tried it, but faced two problems. First I got an error message on A.[email address] so I skipped the [ and ]. Then I got the message "There are too few parameters. The expected number is: 1". I enterd the following code:
Code:
SELECT A.lastname, A.email address, B.subscription
FROM Tab1$ A, Tab2$ B
WHERE A.lastname = B.lastname AND A.firstname = B.firstname
 

Pimagine

New Member
Joined
Dec 11, 2013
Messages
25
Ok, got it! It was not allowed to enter a space between "email" and "address". Maybe that is the reason you used [ ] around it, but that also doesn't work for me. It does work when I rename the column to "emailaddress".
 

tidak

New Member
Joined
Jan 7, 2014
Messages
13

ADVERTISEMENT

hi, Matt

Save the file. ALT-D-D-N, chose Excel files as the data source, select your workbook, OK. If you get a message about no visible tables just OK to acknowledge then from options select system tables. Pick something - anything - and continue to the end then choose the option to edit in MS Query. Via the SQL button edit the text you have. Change it to,
Rich (BB code):
SELECT A.lastname, A.[email address], B.subscription
FROM [your worksheet1 name$] A, [your worksheet2 name$] B
WHERE A.lastname = B.lastname AND A.firstname = B.firstname
OK to enter this and see the required dataset. Hit the 'open door' icon and complete the taks of having the data on a (new) worksheet.

Tidak apa apa. OK?

Thank you so much for the prompt reply! I have followed your instruction but I must be doing something wrong with the SQL syntax. I've changed the input a little to reflect my real life data and this is what I'm trying:


SELECTA.firstname, A.lastname, A.email
FROM cert A, live B
WHERE A.lastname = B.lastname AND A.firstname = B.firstname

Worksheet 1 is called "cert" and worksheet 2 is called "live". When I enter this I get the error "SQL Query cannot be represented graphically" then when I say OK I get the error "Could not add the table 'cert' "

Do I need the square brackets and dollar sign? Where should I put them to ensure correct syntax?

Thanks again!

Terima kasih!
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Selamat pagi, Bpk

You are close

FROM cert A, live B
works if you have a range with defined name cert
it doesn't work for a worksheet name

for a worksheet name cert use
FROM [cert$] A, [live$] B

where the A and B are aliases - short names - that are used elsewhere

Your SELECT line - which defines the returned fields, has all three from table A - A.firstname, A.lastname, A.email

if you want a field from table B, use its prefix & name - A.firstname, A.lastname, B.subscription

if the field name contains a space, syntax is - B.[field name with space in it]

If it doesn't work out again, it is very helpful to post the full SQL as you did just now. Also, compliments on your initial question which was well explained.

Selamat
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

Ok, got it! It was not allowed to enter a space between "email" and "address". Maybe that is the reason you used [ ] around it, but that also doesn't work for me. It does work when I rename the column to "emailaddress".

Only just saw your message. glad it worked for you :)

[should work for name containing space]
`and be more robust than this alternative`

regards
 

tidak

New Member
Joined
Jan 7, 2014
Messages
13
Selamat pagi, Bpk

You are close

FROM cert A, live B
works if you have a range with defined name cert
it doesn't work for a worksheet name

for a worksheet name cert use
FROM [cert$] A, [live$] B

where the A and B are aliases - short names - that are used elsewhere

Your SELECT line - which defines the returned fields, has all three from table A - A.firstname, A.lastname, A.email

if you want a field from table B, use its prefix & name - A.firstname, A.lastname, B.subscription

if the field name contains a space, syntax is - B.[field name with space in it]

If it doesn't work out again, it is very helpful to post the full SQL as you did just now. Also, compliments on your initial question which was well explained.

Selamat

Again, thank you for taking the time to help, I really appreciate it!

I have tried changing the query as suggested, and I have further simplified it to only select from one worksheet for now just to make things easy for me. I now have:

SELECT A.firstname, A.lastname, A.email
FROM [cert$] A
WHERE A.lastname = B.lastname AND A.firstname = B.firstname

But I still get the error "Could not add the table '[cert$]'."

I have a single workbook, and in that workbook I have 3 tabs "cert" "live" and "output". I've double checked and there are definitely no spaces so I'm doing something else wrong :)

firstname, lastname and email are all just column headings in the worksheets, I haven't defined any ranges at all, they are just simple headings at the top of the column.

Sorry for not understanding properly, I'm not sure what else to try.

Many thanks,

Matt.

Semoga hari anda menyenangkan!
 

tidak

New Member
Joined
Jan 7, 2014
Messages
13
I got it to work!

The query I ended up using was:

SELECT `cert$`.firstname, `cert$`.lastname, `cert$`.email
FROM `C:\Temp\excel\combined.xlsx`.`cert$` `cert$`, `C:\Temp\excel\combined.xlsx`.`live$` `live$`
WHERE `cert$`.firstname = `live$`.firstname AND `cert$`.lastname = `live$`.lastname

Very different syntax I know, I just used the wizard to generate and example then edited it.

Thank you Fazza for pointing me in the right direction, I would NEVER have found this otherwise.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
I was away for a while.

FYI, the SQL that didn't work was like this

SELECT some fields
FROM tableA
WHERE something about tableB

That won't work. All tables used in the SQL need to be in the FROM clause. As tableB isn't in the FROM clause, there will be an error when referring to it in the WHERE clause.

Also, FY information/learning, you can edit your successful SQL to

SELECT C.firstname, C.lastname, C.email
FROM [cert$] C, [live$] L
WHERE C.firstname = L.firstname AND C.lastname = L.lastname

sampai jumpa lagi thread depan :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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
Top