Compare multiple columns and return data from additional cell.

[tidak]

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:

firstname	lastname	title	email address
willy	wonka	ceo	willy@wonka.com
walter	white	chef	walter@white.com
phil	dunphy	sales manager	phil@modern.com
cosmo	kramer	jester	cosmo@seinfeld.com
walter	cronkite	announcer	walter@radio.com

<tbody>
</tbody>

Worksheet 2:

firstname	lastname	subscription	paid up
amanda	hugnkiss	yes	yes
willy	wonka	yes	no
cosmo	kramer	no	no
seymore	butts	yes	no
amanda	seyfried	no	yes

<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:

lastname	email address	subscription
wonka	willy@wonka.com	yes
cosmo	cosmo@seinfeld.com	no

<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.

 

[Fazza]

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,

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]

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:

SELECT A.lastname, A.email address, B.subscription
FROM Tab1$ A, Tab2$ B
WHERE A.lastname = B.lastname AND A.firstname = B.firstname

 

[Pimagine]

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]

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,

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]

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]

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]

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]

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]

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