检查列中是否存在单元格值，返回同一行但不同列中的值 [英] Check if a Cell Value exists in column, return a value in the same row but different column
问题描述
我在Excel中有一大堆数据来自供应商，但我只需要这个工作表中的某些内容。我不想删除任何我不需要的数据，因为他们使用的数据，我不想过滤数据的其他原因。我已经尝试过其他用户提出的其他Excel公式问题，他们不太需要他们做的。
供应商通过文本文件，通过我没有设置的连接，我们使用功能区的数据选项卡上的全部刷新按钮，将文本文件中的新数据每天导入Sheet1。 p>
我已经创建了Sheet2，我打算使用数据进行操作。我想要做的工作Sheet2是在Sheet1的B列中寻找值A（我们称之为帐户标签）。如果找到值A，则输出位于值A所在的同一行的列A（帐号）中的单元格的值。我想让它跳过列B中没有A的所有行，因为列B中有几个对我没有用的其他标记。
我需要重复相同的过程来拉取AA栏的帐户余额，但一旦我弄清楚如何做到上述，我相信我可以使用相同的方法来拉平衡信息。
我试图使用VLOOKUP，MATCH和其他一些方法，但我似乎无法弄清楚如何做到这一点。我担心的另一件事情是，如果我得到公式，它将给Sheet2上的空行，没有A值的Sheet1上的所有行，我不想要去做。我只希望Sheet2包含我需要的信息。我有一种感觉，我可能需要做一个宏，但我不知道从哪里开始。
谢谢，
编辑截至05/31/2016
好的，所以我会尝试澄清一下。
表格1：
运行这个我可以得到
团队点< space>团队< space>团队积分
莱斯特81<空格>阿森纳<空间>阿森纳71
阿森纳71< space>
马刺70<空格>
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,
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>
这篇关于检查列中是否存在单元格值，返回同一行但不同列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
