基于现有单元格值显示单元格中的FILTER值 [英] Displaying FILTER Value In Single Cell Based On Existing Cell Value

问题描述

我一直在为特定工作表制定解决方案，我必须以特定方式显示数据，但过去一天一直在挣扎中挣扎。

我基本上想检查几列，并找到该范围内的第一个非空白值，条件是特定单元格具有特定值。

让我更多地进入-depth：

我有一张从表单中提取信息的表单，并且示例单元格使用FILTER公式为整个列提供单个公式。

  = FILTER（'Form Responses'！$ D：D，'Form Responses'！$ B：B =Outgoing） 
  

表单响应表单中的D列包含数字值。

表单响应表格中的B列包含文本传入或传出。

对于此特定列， 传出，如果找到了，我们列出并行D列的值。所以如果我们在B3中有输出，那么我们将得到D3中的任何值。 B17和D17，B143和D143等。

所有这些都很好，但我特别关注的问题是如何找到可以在任何地方的价值在多列（G：T）中。

从G列到T列，我有在表格级别选择的子类别并放入表格回复表格中。

我需要做的是找到传出或传入值的特定子类别，这就是我挣扎的地方。

我设法将几个公式放在一起，几乎可以实现我所需要的：

  = FILTER '表单回复'！$ G：T，'表单回复'！$ B：B =传出）
  

然而，它根据原始子类别显示不同单元格中的内容。

这是什么意思？

如果我在表单响应表中有可以在G到T的列中的子类别，那么我想找到具体的值（每行只能有一个子类别）和所以我们的子类别可以是Sub 1（G1），Sub 2（E1），Sub 3（E1） ）等，但我们需要找到该值，并将其显示在F：F中。

遗憾的是，FILTER公式似乎无法格式化值它发现在一个单一的公式中，但将它们放在一起。

我对文本的墙壁感到非常抱歉，但我想尽可能地彻底。随时根据需要进行编辑！

以下是电子表格的外观图，以更好地理解我的意思。 Sub 1，Sub 2和Sub 3都应该属于子类别列。

p>

以下是Form Responses工作表的一个例子。

我首先使用FILTER的原因是因为它可以通过单个公式一次从表单回复表单中提取所有信息，而且我拥有图像中显示的单元格的精确副本，但对于传入，每当我尝试使用INDEX和MATCH时，我都会留下空单元格（由于引用静态单元格，如B1，B2，E4等）。 ）。

在此先感谢您的任何帮助！

解决方案

说明

1. 添加一个辅助列，比方说它将是AA


2. 向AA1添加一个公式以连接子类别值。示例

  = ArrayFormula（G：G& H： H& I：I）
  

3. 像

  = FILTER（'Form Responses'！$ AA：$ AA，'表格回复'！$ B：B =传出）
  

说明

• ArrayFormula 返回一个值数组
  
• & 将每列的值逐行连接在一起
  

I have been working on a solution for a specific sheet I have to display data in a specific way, but have been struggling without gain for the past day.

I basically want to check through several columns and find the first non-blank value in that range on condition that a specific cell has a certain value.

Let me go more in-depth:

I have a sheet that pulls information from a form, and an example cell has a single formula for that entire column, as it uses the FILTER formula.

=FILTER('Form Responses'!$D:D, 'Form Responses'!$B:B = "Outgoing" )

The D column in the Form Responses sheet contains numerical values.

The B column in the Form Responses sheet contains either the text "Incoming" or "Outgoing".

For this specific column, I find references of "Outgoing" and if it is found then we list the value of the parallel D column. So if we had "Outgoing" in B3 then we would get the value of whatever is in D3. B17 and D17, B143 and D143, etc.

All of this works well, but the issue I am having specifically is when it comes to finding the value that can be anywhere among multiple columns (G:T).

From column G to column T I have sub-categories that are selected at the form level and put into the form response sheet.

What I need to do is find the "Outgoing" or "Incoming" value's specific sub-category, and that is where I am struggling.

I managed to put together several formulas that ALMOST achieve what I need:

 =FILTER('Form Responses'!$G:T, 'Form Responses'!$B:B = "Outgoing" ) 

HOWEVER, it displays the contents in different cells, as per the original sub-categories.

What do I mean by this?

If I have sub-categories that can be in columns from G to T in the Form Responses sheet then I want to find that specific value (there can only ever be ONE sub-category per row) and place it in our information sheet, let's say column F.

So our sub-categories could be Sub 1 (G1), Sub 2 (E1), Sub 3 (E1) etc. but we need to find that value and have it display in F:F.

Sadly, the FILTER formula doesn't seem to be able to format the values it finds in a single formula, but places them alongside each other.

I am terribly sorry for the wall of text, but I am trying to be as thorough as possible. Feel free to edit as necessary!

Below is an image of what the spreadsheet looks like, to get a better understanding of what I mean. The Sub 1, Sub 2 and Sub 3 are all supposed to be under the Sub-Category Column.

And here is an example of what the Form Responses sheet looks like.

The reason I used FILTER in the first place was because it can pull ALL information from the form responses sheet at once with a single formula, and I have an exact copy of the cells as shown in the image but for "Incoming" and as such whenever I tried to work with INDEX and MATCH I would be left with empty cells (due to referencing static cells such as B1, B2, E4 etc.).

Thanks in advance for any and all assistance!

解决方案

Instructions

1. Add an auxiliary column, let say it will be AA
2. Add a formula to AA1 to concatenate the subcategories values. Example

=ArrayFormula(G:G&H:H&I:I)

3. Use a formula like

=FILTER('Form Responses'!$AA:$AA,'Form Responses'!$B:B = "Outgoing")

Explanation

• ArrayFormula returns an array of values
• & concatenates the values of each column, row by row

这篇关于基于现有单元格值显示单元格中的FILTER值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！