Index row if value in range matches cell

H

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
626
Office Version
  1. 2016
Platform
  1. Windows
Hello All

I need pull all dates from (sheet 1 column A) if range B1:O160 from (sheet 1) value equal to value in A1 (form sheet 2) EX. Below

SHEET 1

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD]11/1[/TD]
[TD]13376[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]98564[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/3[/TD]
[TD]98564[/TD]
[TD][/TD]
[TD][/TD]
[TD]13376[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/5[/TD]
[TD]15426[/TD]
[TD][/TD]
[TD][/TD]
[TD]98564[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13376[/TD]
[/TR]
[TR]
[TD]11/10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15426[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15426[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13376[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]........[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


SHEET 2 RESULTS

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]13376[/TD]
[TD]11/1[/TD]
[TD]11/3[/TD]
[TD]11/9[/TD]
[TD]11/14[/TD]
[/TR]
[TR]
[TD]55463[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15426
[/TD]
[TD]11/5[/TD]
[TD]11/11[/TD]
[TD]11/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]81325[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19652[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22354[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]98564[/TD]
[TD]11/1[/TD]
[TD]11/3[/TD]
[TD]11/5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]415568[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]135548[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]895264[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Can a value on sheet2 exist more than once on sheet1 but on the same date?

Start by trying the following

Code: 
Sub Pull_Dates()
  Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
  Dim c As Range, r As Range, f As Range, cell As String
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  sh2.Range("B1", sh2.Cells(Rows.Count, Columns.Count)).ClearContents
  lr = sh1.UsedRange.Rows(sh1.UsedRange.Rows.Count).Row
  Set r = sh1.Range("A1:O" & lr)
  For Each c In sh2.Range("A1", sh2.Range("A" & Rows.Count).End(xlUp))
    Set f = r.Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then
        cell = f.Address
        Do
          c.Cells(1, Columns.Count).End(xlToLeft)(1, 2).Value = sh1.Range("A" & f.Row).Value
          Set f = r.FindNext(f)
        Loop While Not f Is Nothing And f.Address <> cell
    End If
  Next
  MsgBox "End"
End Sub
 
Upvote 0
DanteAmor said:
Can a value on sheet2 exist more than once on sheet1 but on the same date?
Click to expand...
.. and if it can, do you want the date returned once only or as many times as the value appears next to that date.

If you are interested in a formula approach, you could try this formula copied across and down. This formula does the blue action from above if values can occur multiple times on the same date.

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:65px;" /><col style="width:65px;" /><col style="width:65px;" /><col style="width:65px;" /><col style="width:56px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">13376</td><td style="font-size:10pt; text-align:right; ">1-Nov</td><td style="font-size:10pt; text-align:right; ">3-Nov</td><td style="font-size:10pt; text-align:right; ">9-Nov</td><td style="font-size:10pt; text-align:right; ">14-Nov</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">55463</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">15426</td><td style="font-size:10pt; text-align:right; ">5-Nov</td><td style="font-size:10pt; text-align:right; ">11-Nov</td><td style="font-size:10pt; text-align:right; ">13-Nov</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">81325</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">19652</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">22354</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">98564</td><td style="font-size:10pt; text-align:right; ">1-Nov</td><td style="font-size:10pt; text-align:right; ">3-Nov</td><td style="font-size:10pt; text-align:right; ">5-Nov</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">415568</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">135548</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">895264</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=IFERROR(AGGREGATE<span style=' color:008000; '>(15,6,Sheet1!$A$1:$A$160/<span style=' color:#0000ff; '>(Sheet1!$B$1:$O$160=$A1)</span>,COLUMNS<span style=' color:#0000ff; '>($B1:B1)</span>)</span>,"")</td></tr></table></td></tr></table>
 
Upvote 0
Peter_SSs said:
.. and if it can, do you want the date returned once only or as many times as the value appears next to that date. Yes I do want the date to appear more than once if same value appears more than once for the date
Click to expand...
 
Upvote 0
Peter_SSs said:
.. and if it can, do you want the date returned once only or as many times as the value appears next to that date.

If you are interested in a formula approach, you could try this formula copied across and down. This formula does the blue action from above if values can occur multiple times on the same date.

Sheet2

ABCDEF

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]13376[/TD]
[TD="align: right"]1-Nov[/TD]
[TD="align: right"]3-Nov[/TD]
[TD="align: right"]9-Nov[/TD]
[TD="align: right"]14-Nov[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]55463[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]15426[/TD]
[TD="align: right"]5-Nov[/TD]
[TD="align: right"]11-Nov[/TD]
[TD="align: right"]13-Nov[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]81325[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]19652[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]22354[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]98564[/TD]
[TD="align: right"]1-Nov[/TD]
[TD="align: right"]3-Nov[/TD]
[TD="align: right"]5-Nov[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]415568[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]135548[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]895264[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B1=IFERROR(AGGREGATE(15,6,Sheet1!$A$1:$A$160/(Sheet1!$B$1:$O$160=$A1),COLUMNS($B1:B1)),"")

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

Hey Peter, something that I just realize that I will need to add to this is I need add a column to the right of each column in sheet 1 after column B and for each number type it will specify if to the right of that "DAT" "FTO" "VAC" ex:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD]11/1[/TD]
[TD]13376[/TD]
[TD]DAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]98564[/TD]
[TD]DAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/3[/TD]
[TD]98564[/TD]
[TD]FTO[/TD]
[TD][/TD]
[TD]13376[/TD]
[TD]FTO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/5[/TD]
[TD]15426[/TD]
[TD]DAT[/TD]
[TD][/TD]
[TD]98564[/TD]
[TD]DAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]98564[/TD]
[TD]FTO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]98564[/TD]
[TD]DAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13376[/TD]
[TD]FTO[/TD]
[/TR]
[TR]
[TD]11/10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13376[/TD]
[TD]DAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15426[/TD]
[TD]FTO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15426[/TD]
[TD]DAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13376[/TD]
[TD]DAT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and for sheet 2 I want to only pull the dates with DAT next to the Number and than on another sheet I will just have it pull all dates for "FTO"

SHEET 2 RESULTS

[TABLE="width: 500"]
<tbody>[TR]
[TD]13376[/TD]
[TD]11/1[/TD]
[TD]11/10[/TD]
[TD]11/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55463[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15426[/TD]
[TD]11/5[/TD]
[TD]11/13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]81325[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19652[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22354[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]98564[/TD]
[TD]11/1[/TD]
[TD]11/5[/TD]
[TD]11/9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]415568[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
hajiali said:
.. I just realize that I will need to add to this is I need add a column to the right of each column in sheet 1 after column B and for each number type it will specify if to the right of that "DAT" "FTO" "VAC" ex:

and for sheet 2 I want to only pull the dates with DAT next to the Number and than on another sheet I will just have it pull all dates for "FTO"
Click to expand...

Change "DAT" for "FTO" etc

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:65px;" /><col style="width:65px;" /><col style="width:65px;" /><col style="width:32px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">13376</td><td style="font-size:10pt; text-align:right; ">1-Nov</td><td style="font-size:10pt; text-align:right; ">10-Nov</td><td style="font-size:10pt; text-align:right; ">14-Nov</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">55463</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">15426</td><td style="font-size:10pt; text-align:right; ">5-Nov</td><td style="font-size:10pt; text-align:right; ">13-Nov</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">81325</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">19652</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">22354</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">98564</td><td style="font-size:10pt; text-align:right; ">1-Nov</td><td style="font-size:10pt; text-align:right; ">5-Nov</td><td style="font-size:10pt; text-align:right; ">9-Nov</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">415568</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">135548</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">895264</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=IFERROR(AGGREGATE<span style=' color:008000; '>(15,6,Sheet1!$A$1:$A$160/<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(Sheet1!$B$1:$N$160=$A1)</span>*<span style=' color:#ff0000; '>(Sheet1!$C$1:$O$160="DAT")</span>)</span>,COLUMNS<span style=' color:#0000ff; '>($B1:B1)</span>)</span>,"")</td></tr></table></td></tr></table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Are these macros the best way to make custom sorts easily accessible?
Replies
2
Views
89
JenniferMurphy
JenniferMurphy
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
566
Alex Blakenburg
A
A
  • Solved
Compare two sheets based on lastrow with highlighted cells
2 3
Replies
26
Views
254
mumps
mumps
O
  • Solved
Macro to extract repeating invoice numbers and details
Replies
4
Views
234
Omar M
O
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
65
kivikatz
K

Forum statistics

Threads
1,223,886
Messages
6,175,189
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top