根据其他列在一列中查找值 [英] Find values in One Column based on Other Columns

问题描述

我希望有人可以帮忙。我有一个超过65,000行的excel表。我附上了我想要实现的图片

列J和N是重要的，你可以在单元格J2中看到800的付款，并在在单元格N2中，您可以看到它是在11/16/2015。在单元格J3中，您可以看到我们收回了-800的钱，并且在1/4的单元格N3被收回。

有没有办法或一些逻辑或VBA代码，我可以很容易地确定2015年的付款，并在2016年收回。我可以用这个号码来说明什么还原到零显示我，或者我可以用黄色列D和L来确定这些金额。滚动65,000行将使我无法确定这些数额，任何帮助将不胜感激。

  Sub Macro2（）
 Dim lastrow As Long 
 Dim lastColumn As Long 
 Application.ScreenUpdating = False 
 
 lastColumn = ActiveSheet.UsedRange.Column  -  1 + ActiveSheet.UsedRange.Columns.Count 
 lastrow = ActiveSheet.UsedRange.Row  -  1 + ActiveSheet.UsedRange.Rows.Count 
列（A：A）。选择
 Selection.Insert Shift：= xlToRight，CopyOrigin：= xlFormatFromLeftOrAbove 
范围（A2）选择
 ActiveCell.FormulaR1C1 =1
范围（A3）选择
 ActiveCell.FormulaR1C1 =2
范围（A2： A3）选择
 Selection.AutoFill目的地：=范围（A2：A& lastrow）
范围（A1）Value =行ID
列（选择
 Selection.Insert Shift：= xlToRight，CopyOrigin：= xlFormatFromLeftOrAbove 
 Range（Q1）。选择
 ActiveCell.FormulaR1C1 =positive identifier
列（R：R）。选择
 Selection.Insert Shift：= xlToRight，CopyOrigin：= xlFormatFromLeftOrAbove 
 Range（R1）。选择
 ActiveCell.FormulaR1C1 =匹配行ID 
 Range（Q2）。选择
 ActiveCell.FormulaR1C1 == 1 * AND（RC [-6]> 0，YEAR（RC [-1]）= 2015）
 Range（Q2）。选择
 Selection.Style =逗号
 Selection.AutoFill目的地：=范围（Q2：Q&对于i = 2 To lastrow 
对于j = 3要重新生成
如果Cells（i，5）.Value = Cells（j，5）.Value And Cells（i，11 ）.Value> 0和细胞（i，11）.Value = -1 * Cells（j，11）.Value And Year（Cells（i，16）.Value）= 2015 And Year（Cells（j，16）.Value）= 2016然后
细胞（i，18）.Value = j  -  1 
结束如果
下一个
下一个
范围（R2：R& lastrow）。选择
 Selection.Style =Comma
 Application.ScreenUpdating = True 
 End Sub 
  

得到一些代码，但是在

$ pre> 如果Cells（i，5）.Value = Cells（j ，5）。价值和细胞（i，11）。价值> 0和细胞（i，11）.Value = -1 * Cells（j，11）.Value And Year（Cells（i，16）.Value）= 2015 And Year（Cells（j，16）.Value）= 2016然后


出现的错误是

水平线

请注意，我关闭了网格线。并添加了将客户分开的水平紫线。紫色线条不是手工绘制，而是条件格式： -

只需将此规则应用于整个工作表：

所以，而不是通过65,000行，现在它关于您在列表中有多少客户。

公式

• Q： = N（AND（J2> 0，YEAR =（（J2（0，YEAR（N2）= 2016））


• S： = SUMIFS（Q：Q，D：D，D：D，L：L，L：L）+ SUMIFS ：R，D：D，D：D，L：L，L：L）


• T： = N（AND SUMIFS（j：J-，D：D，D：D，L：L，L：L，S：S，S：S）= 0，S：S> = 2））





这是我所拥有的，希望有人能拿出更好的答案。有趣的是解决大数据问题。




I hope someone can help. I have an excel sheet with over 65,000 rows. I have attached a picture of what I am trying to achieve

Column J and N are the important ones as you can see in cell J2 a payment of 800 was made and in and in cell N2 you can see it was made on 11/16/2015. In cell J3 you can see we took back the money -800 and it was taken back on 1/4/2016 cell N3.

Is there a way or some logic or VBA code where I can easily identify these payments made in 2015 and taken back in 2016. Can I use the number to say what ever reverts back to zero show me, or can I use the columns D and L in yellow to identify these amounts. Scrolling through 65,000 lines will make me blind trying to identify these amounts any help would be greatly appreciated.

Sub Macro2()
Dim lastrow As Long
Dim lastColumn As Long
Application.ScreenUpdating = False

lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"
Range("A2:A3").Select
Selection.AutoFill Destination:=Range("A2:A" & lastrow)
Range("A1").Value = "Row ID"
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Q1").Select
ActiveCell.FormulaR1C1 = "positive identifier"
Columns("R:R").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R1").Select
ActiveCell.FormulaR1C1 = "Matching row ID"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=1*AND(RC[-6]>0,YEAR(RC[-1])=2015)"
Range("Q2").Select
Selection.Style = "Comma"
Selection.AutoFill Destination:=Range("Q2:Q" & lastrow)
For i = 2 To lastrow
For j = 3 To lastrow
If Cells(i, 5).Value = Cells(j, 5).Value And Cells(i, 11).Value > 0 And Cells(i, 11).Value = -1 * Cells(j, 11).Value And Year(Cells(i, 16).Value) = 2015 And Year(Cells(j, 16).Value) = 2016 Then
Cells(i, 18).Value = j - 1
End If
Next
Next
Range("R2:R" & lastrow).Select
Selection.Style = "Comma"
Application.ScreenUpdating = True
End Sub

Got some code but its bugging out on

If Cells(i, 5).Value = Cells(j, 5).Value And Cells(i, 11).Value > 0 And Cells(i, 11).Value = -1 * Cells(j, 11).Value And Year(Cells(i, 16).Value) = 2015 And Year(Cells(j, 16).Value) = 2016 Then

the error that appears is Any help is greatly appreciated

解决方案

I notice you are ok with sorting your data. This makes your problem a bit easier. This answer is not to provide a better formula, but to ease the pain of matching.

I thought for a while, and realize the combination of exceptional cases are too many. Even though it is not impossible to find the perfect formula but you will just waste too much effort in finding it. Lets just continue using the "flawed" formula to auto highlight the rows.

Horizontal lines

Notice that I turned off grid lines. And added the horizontal purple lines that split the customers apart. The purple lines are not manually drawn but a conditional formatting:-

Just apply this rule to the entire worksheet:

So instead of going through 65,000 lines, now its about how many customers you have on the list.

Formulas

• Q: =N(AND(J2>0,YEAR(N2)=2015))
• R: =N(AND(J2<0,YEAR(N2)=2016))
• S: =SUMIFS(Q:Q,D:D,D:D,L:L,L:L) + SUMIFS(R:R,D:D,D:D,L:L,L:L)
• T: =N(AND(SUMIFS(J:J,D:D,D:D,L:L,L:L,S:S,S:S)=0,S:S>=2))

This is what i have, hope someone can come up with better answers. Its interesting to solve big data problem anyway.

这篇关于根据其他列在一列中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！