FacebookTwitter
Hatrack River Forum   
my profile login | search | faq | forum home

  next oldest topic   next newest topic
» Hatrack River Forum » Active Forums » Books, Films, Food and Culture » Excel-fu help needed.

   
Author Topic: Excel-fu help needed.
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
I have an excel sheet with a column of 20,000 names on it and other columns with important information.

I have another sheet that has a column of 10,000 names (all part of the 20,000 names in the other document) with one other column of important information on it.

Is there a way I can match the two documents together so that I can copy just the 10,000 names in the 20,000 name document in order to paste them in the second document?

I hope that makes sense. If I sort by alphabet I have to read down 20,000 names and copy each match one at a time. If I can match the names of the two documents then I can only copy the names I need.

Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
scifibum
Member
Member # 7625

 - posted      Profile for scifibum   Email scifibum         Edit/Delete Post 
You can use "Advanced filter" to do what you need.

I started typing out instructions but I think I would do a bad job compared to the Excel help feature on Advanced filter. Look there first.

Or use "VLOOKUP" - that would probably work well here too. (Either one is a very useful tool. VLOOKUP might be useful more often now that I think about it.)

Sorry my edit crossed your post.

Posts: 4287 | Registered: Mar 2005  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
Thanks. I just got done watching a video on vlookup, but I don't think that is what I need. I will watch advanced filter now.
Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
swbarnes2
Member
Member # 10225

 - posted      Profile for swbarnes2           Edit/Delete Post 
I'd import both as tables to Microsoft Access, you should be able to do some kind of join there.

Excel is a spreadsheet program, Access is a database program, it's going to have more tools to deal with your data.

Or find someone who knows a bit of programming. You could do this in about 10 lines of Perl.

Posts: 575 | Registered: Feb 2007  |  IP: Logged | Report this post to a Moderator
scifibum
Member
Member # 7625

 - posted      Profile for scifibum   Email scifibum         Edit/Delete Post 
Yes - you can certainly use Access or VBA or some other script to do this.

However, you can also handle this easily within Excel without coding or using another program. This is one of the things Excel is well suited for. (If you're working alone or don't have Access in your version of MS Office, don't be discouraged.)

lem, i can put together a simple example worksheet to email to you if you want. Just email through Hatrack if you're interested.

Posts: 4287 | Registered: Mar 2005  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
Ok...I tried the advanced filter, but it looks like you can only do filtering from within the same excel sheet.

I then created my very first database in Access 2007. I have two sheets now from each excel document.

Now there is some type of join available? I need a process that can see if both sheets match the same data in column 1 then then it will take the data in column 6 in sheet 2 and insert it into column 3 in sheet 1.

I will send you an appreciative email scifibum!

Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
How do I email through Hatrack? I see your profile but there is no place to send you a message. I can only post a comment on "contact us" for OSC or click a link to go back to home page.
Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
scifibum
Member
Member # 7625

 - posted      Profile for scifibum   Email scifibum         Edit/Delete Post 
Sorry had email disabled. Should show up now if you click my profile.

Yep, in Excel you'd have to copy/paste the data into the same sheet to use Advanced filter.

Don't want to derail the Access thing if you'd like to proceed with that. You just need to create a query, add both tables to the designer view, click and drag the name field(s) in one table to the corresponding field(s) in the other table to create the join, then drag the fields you want in your result set from the tables to the grid below.

Posts: 4287 | Registered: Mar 2005  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
Ok. I am going with the Excel Option because scifibum sent me a really helpful walkthrough. However, I am stuck with the data not matching with the names.

So here is how it is set up.

Sheet1 = Large List
Sheet2 = Small List
Sheet3 = Work List

The Work list is the Small List names copied for cell A and B. Cell C is just the Header of the Large lists value1.

I am wanting to copy the value1 from the Large list and copy over to match only the names on the small list. I am doing this on the Work List I created.

Steps I took:
  • I highlighted the Work List and choose filter--advanced (under the data ribbon).
  • I chose copy to another location.
  • I clicked the empty fields in the dialog box (like List Range)and went to the different sheets and clicked on the part of the sheet that I wanted to put in the field for "list range," "criteria range," and "copy to."
  • The formulas came out like this: List range: 'Large List'!$A$1:$C$15480 Criteria range: 'Large List'!$C:$C Copy to: $C:$C
  • I clicked "unique records only" and clicked ok.

I think because I started the Advanced filter on the work list it didn't put in the sheets name for the "copy to" field.

The problem is when the data copies over it doesn't line up with the names. It just copies down all 15480 values in the C column. It doesn't match it to the name and it doesn't only put in the data of the small list names. It should only copy a couple thousand values over, not ALL of them. And it should, to my understanding, match the value to the right name.

I am confused. [Wall Bash]

Any help on how I'm doing this wrong? Thanks!

I started the Advanced Filter on the "Work List" because when I started it on the Large List I got the error, "you can only copy filtered data to the active sheet."

Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
bump
Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
TomDavidson
Member
Member # 124

 - posted      Profile for TomDavidson   Email TomDavidson         Edit/Delete Post 
I'll be honest with you. I wouldn't take this approach. What you're describing sounds to me like a perfect job for VLOOKUP.

Assuming Sheet1 has the names in column C and the destination column of the important information in column H, and Sheet2 has the names in column B and the information to be copied into column H in column F, you would put the following into column H on Sheet1:

=VLOOKUP(C2,Sheet2!B:F,5,FALSE)

This will, everywhere that the names match, put the information in Column F on Sheet2 into Column H on Sheet1. If there is no exact match, the code "#N/A" will appear instead.

Posts: 37449 | Registered: May 1999  |  IP: Logged | Report this post to a Moderator
just_me
Member
Member # 3302

 - posted      Profile for just_me           Edit/Delete Post 
Are you still stuck?
Posts: 409 | Registered: Apr 2002  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
Ok, I readjusted how my sheets were set up and I readjusted the columns. It worked!!

Here is the code I used: =VLOOKUP(A1,Sheet2!A:C,3,FALSE)

The names in both Sheet1 and Sheet2 are in column A. The data destination is Sheet1 E (where this formula went). The data to be extracted is in sheet2 C.

It appears to work. However, the Last Name is in column A and the First Name is in Column B in both sheets. How it stands now, it looks up the first person with the last name Cox (or any other names shared with more then one person) and puts it in all the cox fields in Sheet1. I want to be able to make sure the last AND first name are compared.

I don't want to just look at column A in both sheets, I want to look at Column A & B in both sheets. Any ideas how to expand that?

EDIT: I don't know what the "3" is in the equation. It used to be five. I changed it to three on a whim... Also I changed it from A2 to A1 within the start of the parenthesis because that is what worked after a few tweaks.

Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
quote:
Are you still stuck?
Yes. I am stuck on both processes. If I use scifibum's excel sheet instructions for advanced filter, I only end up copying the value from one field to another---it doesn't filter it by name.

When I do TomD way, I am not able to have VLookup look at 2 (A&B) columns instead of 1(A).

I am going through help files and tweaking the sheet to experiment right now. I just spent 40 minutes with a programmer from a larger city visiting manipulating the data, but we had no such luck.

Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
swbarnes2
Member
Member # 10225

 - posted      Profile for swbarnes2           Edit/Delete Post 
This shouldn't take 40 minutes. Excel isn't the right tool for the job. Excel is a hammer, you need a screwdriver.

A database program, or a small script could do this in 5 minutes.

(Why can't you concatenate the contents of A and B together?)

Posts: 575 | Registered: Feb 2007  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
OK! Worked!! I tried concatenating the names in column K on both sheets (before swbarnes2's post), but I never got my formula right. However, I inserted a new column A in both sheets and concatenated B and C and re-worked the formula and got the right info!

It is done! Thank you all! I just signed up for an online Excel and Access class. It is becoming apparent that I will need much better skills then I currently have to be the type of employee I want to be.

Until now I never really had to manipulate data an any meaningful way outside of simple imports and exports.

Phhs...stupid psych degree!

Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
TomDavidson
Member
Member # 124

 - posted      Profile for TomDavidson   Email TomDavidson         Edit/Delete Post 
quote:
When I do TomD way, I am not able to have VLookup look at 2 (A&B) columns instead of 1(A).
And this is why God made the concatenate function. [Smile]

Insert a column into the sheet. Into that column, put =CONCATENATE(TRIM(A2), ", ", TRIM(B2)). Fill down the column for all 20000 rows.

I would strongly recommend that you now insert another column, and Paste Special the values of the resulting column above into the new column. Then delete the column with the =CONCATENATE function.

Do this for both sheets. You will wind up with one column in each sheet formatted as "LastName, FirstName".

----------

quote:
I don't know what the "3" is in the equation. It used to be five. I changed it to three on a whim... Also I changed it from A2 to A1 within the start of the parenthesis because that is what worked after a few tweaks.
*laugh* Some explanations of how that works:

=VLOOKUP(A1,Sheet2!A:C,3,FALSE)

In this scenario, A1 is the reference address for the cell that contains the data you want to look for in the array. (In other words, it has the name you want to look up.) This doesn't have to be a cell reference; if you just wanted to look for the name "Jones" in the array, you could just type the word "Jones" (in quotes) instead of the cell reference.

Sheet2!A:C is the cell reference of the array (or group) of cells you want to search. (The Sheet2! bit just tells it to look at the cells on a different worksheet, in this case named Sheet2.) VLOOKUP always searches the leftmost column of the array for the value, but you also need to define which other continguous columns it's going to search across to return information. In this case, you're searching across columns A, B, and C, but only matching values in column A.

The 3 indicates which column in the array contains the data you want to return, and means that you want to return the third column of the array. If you were searching across columns A,B,C,D, and E (for some reason), and wanted to return the value in column D, you'd put a 4 there. If you were searching across columns B,C,D,E, and F, and wanted to return column C, you'd put a 2. (Note, by the way, that you generally want to define your array so that you aren't searching across extraneous columns.)

Finally, the FALSE means that you will only return data if there is an EXACT match between the value in cell A1 and an item in column A on Sheet2. If you were to put TRUE there instead, it would return the data for what it considers the closest match. In all cases, it will return the first match that meets its criteria.

[ April 30, 2009, 04:17 PM: Message edited by: TomDavidson ]

Posts: 37449 | Registered: May 1999  |  IP: Logged | Report this post to a Moderator
TomDavidson
Member
Member # 124

 - posted      Profile for TomDavidson   Email TomDavidson         Edit/Delete Post 
Gah! Beat me to it! (That'll teach me to go to meetings.)
Posts: 37449 | Registered: May 1999  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
I feel cool. I had a follow up question that seemed simple enough. After I typed it and was doing a little more research before I dared submit it, I figured out the answer!

Excel is really cool. I am starting to wonder how/why I never looked at it closer!

Here is the original question. The answer is =IF(E1=F1,"yes","no") pasted in column G. Simple but new to me.

quote:
Follow up question. I am now comparing Column E with Column F. They SHOULD both have the same value/data. Is there a quick way to compare the two columns and either put in TRUE or False if the Data matches or doesn't match?

Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
just_me
Member
Member # 3302

 - posted      Profile for just_me           Edit/Delete Post 
As a handy trick, concatenate is great, but there's an easier way than using the function.. use &.

So, instead of doing
=CONCATENATE(TRIM(A2), ", ", TRIM(B2))
you can just do:
=TRIM(A2) & ", " & TRIM(B2)

I find it makes it much easier to deal with large quantities of concatenations...

Posts: 409 | Registered: Apr 2002  |  IP: Logged | Report this post to a Moderator
lem
Member
Member # 6914

 - posted      Profile for lem           Edit/Delete Post 
quote:
So, instead of doing
=CONCATENATE(TRIM(A2), ", ", TRIM(B2))
you can just do:
=TRIM(A2) & ", " & TRIM(B2)

I am not getting what TRIM does. I just did =CONCATENATE(B1, " ", C1) and it worked just fine. Why do I need to put in "TRIM"?
Posts: 2445 | Registered: Oct 2004  |  IP: Logged | Report this post to a Moderator
TomDavidson
Member
Member # 124

 - posted      Profile for TomDavidson   Email TomDavidson         Edit/Delete Post 
TRIM removes extra white space. I don't know about you, but I'm constantly getting data from questionable sources that might have entered something with a gazillion empty spaces after the actual text. In general, then, when I compare strings, I'll TRIM() them first to eliminate obvious white space issues.

When matching is REALLY important, and I'm working in a case-sensitive environment, I'll trim out white spaces and all punctuation, then cast everything to upper-case before comparing.

(BTW, now that you've got Column G doing a TRUE or FALSE, check out Conditional Formatting for a fun way to highlight the results.)

Posts: 37449 | Registered: May 1999  |  IP: Logged | Report this post to a Moderator
   

   Close Topic   Feature Topic   Move Topic   Delete Topic next oldest topic   next newest topic
 - Printer-friendly view of this topic
Hop To:


Contact Us | Hatrack River Home Page

Copyright © 2008 Hatrack River Enterprises Inc. All rights reserved.
Reproduction in whole or in part without permission is prohibited.


Powered by Infopop Corporation
UBB.classic™ 6.7.2