1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Pulling data from sheets

Discussion in 'Business Applications' started by joe.perry, Nov 6, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. joe.perry

    joe.perry Thread Starter

    Joined:
    Nov 6, 2008
    Messages:
    9
    If i have 2 excel sheets
    Sheet a
    computer15
    computer2
    computer37
    computer40
    computer9

    Sheet b
    computer16
    computer2
    computer39
    computer48
    computer5
    computer40

    is there a way to compare sheet b with sheet a to make a sheet of all computers that occur in both documents?
    such as

    sheet c
    computer2
    computer40
    it would be really cool if i could figure out a way to pull sheet c computers that are in sheet a out of sheet a.

    such as
    sheet d (sheet a's list - sheet c)
    computer15
    computer37
    computer9

    If anyone could figure out how to do this it would be extrmley helpful.
     
  2. joe.perry

    joe.perry Thread Starter

    Joined:
    Nov 6, 2008
    Messages:
    9
    Any help would be great. if its in vbscript or anyhing thats fine as well. id like a file i could save or have emailed to me.
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hi,

    It's not perfect, but should do you well enough.
    One thing you need to do is to sort the two columns on Sheet1 and Sheet2 - otherwise you will get incorrect non-matches.
     

    Attached Files:

  4. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    You are my hero. I still would dump it into Access and link them together and run a query instead hehe. ;) But my mind is not into nesting atm.
     
  5. joe.perry

    joe.perry Thread Starter

    Joined:
    Nov 6, 2008
    Messages:
    9
    the only problem im seeing is im trying to compare txt with diferent naming conventions. i just need to put one sheet of txt up to another and pull all same names out of the 1st sheet of txt. like a compare and delete in sheet 1. is there anyways of doing that?


    Have it look at another excell sheet and anything that is the same name delete from the original excel sheet?
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Joe, you are changing the criteria here. If you gave the problem in full, I could have worked with that. Saying you are "trying to compare txt with different naming conventions" does not help. How are they different? You are too new a member to post a file (need 10 posts first), so if you could just give us some actual examples of the data you want to compare, I could work with that.
    The other request you just now made, "Have it look at another excel sheet and anything that is the same name delete from the original excel sheet?" - that is also something that can be done, but it will require VBA, which isn't my forte.

    And Jim, yes, Access would do it neatly, but not if the names "have different naming conventions." :(
     
  7. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Aye diferent naming conventions might be "fun."
     
  8. joe.perry

    joe.perry Thread Starter

    Joined:
    Nov 6, 2008
    Messages:
    9
    Sorrey for the confusion, im not very good with words.



    i have a list of computers im trying to pull all the servers out of automaticly. So that the one list breaks down into 2 lists. one with servers and one with workstations.


    for example.


    gsb-cs-wso1 (workstation)
    gsb-cs-wso2 (server)
    gsb-cs-wso9 (workstation)



    i have one big list with all computer names and servers in it mixed up.

    im trying to compare that list to a list of all my servers (some of which arent in the list) to see if i can pull the server's out and have them pop up with a list of servers that are in that original jumbled list. and a list of all the regular computers in the list.


    Since my original list is computer names theres no real pattern to determin which is a server and which is a workstation, other then to go thru and look at my list of servers.


    im having to do this by hand currently with over 4k computers. I look at my list of computers and then look at my list of servers to see which is in that oringal list of computers.


    I really appreciate all the help and time your taking to help me with this issue. Im not much of an excel guy.
     
  9. joe.perry

    joe.perry Thread Starter

    Joined:
    Nov 6, 2008
    Messages:
    9
    some of the computernames could be


    gsb-fgh-4567
    bobcomputer
    room324
    computer345

    im just trying to compare that block of 500 computers/servers with a list of 100 or so servers that i have. so that it pulls the servers out makes a list of them and makes a list of workstations (aka computers that havent pulled out of the original block that arent servers.)
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Can you post a sample of your data? You can do this with formulas or VBA. What would also really help is to have you post a sample of what you would like to return, or how you would like it to look. Also, will you be doing this as a one time deal? Or will you be doing this on many lists?
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I was thinking you were saying that a computer might be called PC-112 in one list and something like ex-r113-PC-112-42 in another - something that is possible, but not fun.
    I f you have a list of the server names and want to pull it, my formula should work, and anything not listed (which is essentially what the other formula is) should be the regular PCs and workstations.
    As Zack says, this is doable, but seeing your actual data will help a lot. (Actually, if Zack says he can do it, your problem is almost solved).
     
  12. joe.perry

    joe.perry Thread Starter

    Joined:
    Nov 6, 2008
    Messages:
    9
    It looks Good, However what if my server info dosent line up in columns all the way across? for example if i have a big list of data my server name may be in sheet 1 column 45, and in my server list it may be in sheet 2 column 13
     
  13. joe.perry

    joe.perry Thread Starter

    Joined:
    Nov 6, 2008
    Messages:
    9
    The Servers Have the same name in the lmain list and the list of servers, but i may be comparring a list of 3k computers with a list of 100 computers. so the columns wont line up for server names in my server list and in my main block of names.
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    So, just to make this clear, your computers are spread across multiple columns?
    The would make a formula potentially more complicated (and probably slow), but if Zack is to write code for it, he probably would like to know the exact layout of your data as it is and into what columns you want your final data to go.
     
  15. joe.perry

    joe.perry Thread Starter

    Joined:
    Nov 6, 2008
    Messages:
    9
    My Computers Go straight down a. but what i meant was in the spreedsheet he made i believe the info has to line up based on its numbered row.


    If i have a list of 1k computers and compare it to a list of 100 servers its not going to match up numbers on the rows. in my main list the server may be in row 29 column a, in my server list the server may be in row 5 column a.


    im just using column a.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/766532

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice