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

[Teblol]

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

[hoyasaxa215]

Can you upload a sample file?

[Teblol]

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.

[hoyasaxa215]

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

[Teblol]

I want all matches to be shown.

[Punnam]

Hi,

Is this what you are trying to achieve if not update the expected results manually .

Please Login or Register  to view this content.

)
Punnam

[Teblol]

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

[samba_ravi]

=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

[Teblol]

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.

[Punnam]

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

[Teblol]

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.

[samba_ravi]

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