Excel-单元格匹配后,标头之间的返回值-V2 [英] Excel - Return value, after cell has been matched, between headers - V2
问题描述
此问题是我之前在以下位置发布的问题的延续: Excel-单元格匹配后的返回值,标头之间
This Question is a continuation to the one I posted before at: Excel - Return value, after cell has been matched, between headers
与以前一样(以及更多):
•如果数字为负:
•使用MATCH检查范围(在标头之间)中是否存在对应的(正)编号并且'IDX'是相同的;
• Use MATCH to check if there is a correspondent (positive) number in the range (between headers) AND 'IDX' is the same;
•如果存在MATCH,则输出已匹配的F的ID(第一列)的值,然后输出已匹配的负值的ID;
• If there is a MATCH output the value of the ID (1st column) of the F that has been matched, and then, output the ID of the negative value that has been matched ;
•如果没有,则不执行任何操作.
• If not, do nothing.
问题是:
•它需要循环,因为负值可以在正值之后;
• It needs to loop, because the negative value can come after the positive one;
•搜索必须包含在标头之间,并且idx必须相同.我认为我上传的图片清晰,充满希望.
• The search must be contained between headers AND the idx must be the same. I think the image I uploaded is clear, hopefuly.
我尝试修改lori_m的先前答案,添加一个新变量和条件,但是一切都变成了caput:
I've tried modifying the previous answer from lori_m, adding a new variable and condition but everything just went caput:
Names.Add "i", "=$A$1:$A$12": _
Names.Add "v", "=$B$1:$B$12": _
Names.Add "m", "=$D$1:$D$12": _
Names.Add "h", [IF({1},LOOKUP(ROW(i),ROW(i)/(v=INDEX(v,1))))]: _
Names.Add "l", [IF({1},LOOKUP(ROW(i),ROW(i)/(v=INDEX(v,1)),FREQUENCY(-ROW(i),IF(v=INDEX(v,1),-ROW(i),0))-1))]: _
[m]=[IF(COUNTIF(OFFSET(v,h,,l),"=-"&v),CONCATENATE("=",ADDRESS(ROW(i),COLUMN(i),4),"&"" AND ""&INDEX(",ADDRESS(h+1,COLUMN(i)),":",ADDRESS(h+l,COLUMN(i)),",MATCH(-",ADDRESS(ROW(v),COLUMN(v),4),",",ADDRESS(h+1,COLUMN(v)),":",ADDRESS(h+l,COLUMN(v)),",0))"),"")]: _
Names("h").Delete: _
Names("l").Delete
我非常感谢您提供的所有帮助.
I am very appreciated for all the help.
推荐答案
这是一些VBA代码,在40,000行上大约需要0.4s:
Here's some VBA code for this, it takes around 0.4s on 40,000 rows:
Public Sub MatchVals()
Dim Arr As Variant, dic As Object, v As Variant, Match() As String
Dim i As Long, j As Long, k As Long, n As Long
Arr = ActiveSheet.UsedRange.Value2
Set dic = CreateObject("scripting.dictionary")
n = UBound(Arr)
ReDim Match(1 To n, 1 To 1) As String
k = n
For i = n To 1 Step -1
If Arr(i, 3) <> Arr(1, 3) Then
dic.Add Arr(i, 3) & "|" & Arr(i, 2), i
Else
For j = i + 1 To k
If Arr(j, 3) < 0 Then
v = dic(-Arr(j, 3) & "|" & Arr(j, 2))
If Not IsEmpty(v) Then
Match(v, 1) = Arr(v, 1) & " AND " & Arr(j, 1)
End If
End If
k = i - 1
Next j
dic.RemoveAll
End If
Next i
With ActiveSheet.UsedRange
.Offset(0, .Columns.Count).Resize(, 1).Value2 = Match
End With
End Sub
这篇关于Excel-单元格匹配后,标头之间的返回值-V2的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!