Check if a Cell Value exists in column, return a v

Question

I have a large sheet of data in Excel that comes from a vendor daily but I only need certain things from this worksheet. I don't want to delete any of the data I don't need as its used by others and I don't want to filter the data for other reasons. I've tried some other Excel formulas I've found from other users questions and they don't quite do what I need them to do.

All of the data from the vendor comes via a text file and through connections that I didn't setup, we use the "Refresh All" button on the Data tab of the Ribbon to import the new data from the text file into Sheet1 on a daily basis.

I have created Sheet2 where I plan to use the data for manipulation. What I am trying to do Sheet2 is look for the value "A" (what we call an account tag) in Column B of Sheet1. If the value "A" is found then output the value of the cell located in Column A (the account number) of the same row that the value "A" was located on. I want it to skip all the rows that don't have an "A" in Column B as there are several other tags in Column B that are not useful to me.

I need to pretty much repeat the same process to pull the accounts balance from Column AA but once I figure out how to do the above I am sure I can use the same method to pull the balance information.

I have attempted to use VLOOKUP, MATCH and a couple other methods but I can't seem to figure out how to do this. Another thing I'm afraid of is if I get the formula right, it is going to give me blank rows on Sheet2 for all of the rows on Sheet1 that don't have the "A" value, which I don't want it to do. I only want Sheet2 to contain the information I need. I have a feeling I might need to do a Macro, but I am not sure where to start.

Thanks,

EDIT as of 05/31/2016

Ok, so I will attempt to clarify this.

Sheet 1: Data that is Input Sheet 2: Data that is Output If you look at the image of Sheet 1 Column B has what I am going to call Tags. I need excel to look in Column B for any rows that have an A tag and copy the Account number to Sheet 2 (the output sheet) and then look in Column AA and copy the Balance so that my results on a second sheet look like the image I posted in the second image(the output sheet). There is other data I'll be dealing with on sheet 2 but for the purposes of explaining my problem I only need this information.

If you look at Sheet 1 you will notice that the account number is the same for many rows until I reach a new account number which is tagged with another A in column B. So I need excel to ignore all the other rows until it sees another A in column B and repeat the process of collecting the account number and the balance.

I hope this clarified the problem.

Thanks,

Answer 1

One can use the Macro recorder to begin code. You need an operation to record.

If I have some data

Team        Points  <space>  Team       
Leicester   81      <space>  Arsenal        
Arsenal     71      <space>     
Spurs       70      <space>     

then I can use from the Data ribbon "Advanced" which throws a popup box, where one can select the List range, and a criteria range. Also one can choose for Action the Copy to another location.

Running this I can get

Team        Points  <space>  Team       <space>   Team    Points
Leicester   81      <space>  Arsenal    <space>   Arsenal 71
Arsenal     71      <space>     
Spurs       70      <space>