+ Reply to Thread
Results 1 to 12 of 12

Need help pulling information from Sheet2 if it matches criteria from Sheet3.

  1. #1
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    266

    Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    Hi All,

    In a worksheet I created for work, I have 20 columns, and 5,000 rows on sheet2 which I have renamed "Open Order". A few key columns are Account name which is in the B column, Order number which is in the D column, Item number which is in G column, and quantity which is in the K column. The rest are not of importance for this question. Throughout the day, we receive an email that has a list of changes in it. Unfortunately the email is just written, and is not done in any organized formation. Below I will include an example of these changes. What I would like to do is paste these changes into Sheet3, which has been renamed "Changes", and if any orders have this item on it, have them listed. I wold like it to include the Account name, Order number, Item number and quantity from "Open Order". I have no idea where to even start with this. Any info that could get me started would help a lot. Even just a confirmation that this is possible would help.

    Example of the changes...

    Nx1 alloc all the orders

    Hc221gbw is nla its rgb

    Ww204w is wn

    Ww43d cancel the orders

    Sp1k move all and allocate the orders
    END EXAMPLE





    Thank you

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    Can you upload a sample file?

  3. #3
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    266

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    Sample For EXCEL HELP FORUM.xlsx

    Here is a small sample. I created a formula to take the first word in column A on Changes!. Now I just need Column B on the changes sheet to search column C on Open order sheet and if there is a match, pull the info from that line and place it in column D through G on the Changes sheet.

  4. #4
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    For example, Item G33 appears 3 times on the Open Order tab. Do you want all 3 results? Or just the first one?

  5. #5
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    266

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    I want all matches to be shown.

  6. #6
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    Hi,

    Is this what you are trying to achieve if not update the expected results manually .
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    )
    Punnam
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    266

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    That is very close. I need it to display more info though. Attached is the results I would like, if possible. If it is not possible to display all ofthe information, I at least need the order number.

    Sample For EXCEL HELP FORUM.xlsx

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,686

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    =INDEX('Open Order'!$A$2:$D$17,MATCH(1,(COUNTIFS($F$1:$F1,'Open Order'!$C$2:$C$17,$E$1:$E1,'Open Order'!$B$2:$B$17,$D$1:$D1,'Open Order'!$A$2:$A$17)=0)*(COUNTIF($B$2:$B$6,'Open Order'!$C$2:$C$17)>0),0),1)
    Try this array formula in D2 and copy across
    for more details see attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  9. #9
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    266

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    How do I make it so that formula can be used in another workbook? Every time I try to use it in my actual workbook, it takes me back to the sample.

  10. #10
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    Hi Teblol ,

    1)Make sure the sheet names in the original workbook are same as the same work book , i think is is better to rebuild the formula in your workbook instead of copying the same .
    2) If your trying to use formula with respect to post 6 , it is necessary for you to use the Ctrl+Shift+Enter combination of keys instead of just enter

    Punnam

  11. #11
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    266

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    Thanks everyone for your help. Siva allowed me to email a copy of the actual report I was using, and came up with what I wanted. I was shocked with how well it works. He is amazing and I appreciate the time he took to come up with a solution to what I was looking for. He definitely lived up to his reputation. +++++ I am very glad I found this site. You are all so helpful, and very quick with responses. I have a lot to learn, and I feel that I have come to the right place.

  12. #12
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,686

    Re: Need help pulling information from Sheet2 if it matches criteria from Sheet3.

    Glad to help you Teblol, and thanks for your feedback and adding reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. compare Sheet2 against Sheet3 with highlighting results in Sheet2
    By mariposa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2011, 01:17 PM
  2. Replies: 2
    Last Post: 12-01-2010, 07:51 AM
  3. Delete Sheet2 & Sheet3
    By arunkw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-22-2010, 10:04 AM
  4. Macro to copy values from Sheet1 to Sheet3, based on criteria in Sheet2
    By John74 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2010, 04:55 PM
  5. A1 in sheet1 = =SUM('sheet2:sheet3'!A1)
    By minrufeng in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-22-2006, 03:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1