Not sure what function to use - INDEX/MATCH, VLOOKUP?

[brasqo]

Just looking for some guidance...

I'm confused on what I should be looking for as far as what formula would accomplish what I'm looking to do.

I'd like to be able to look at column H, and if it has text rather than a date, I'd like to change it to the date... You can see in Column A, these are the same case number (78972), but I have Labor and Parts are on separate lines. For some reason, when I'm pulling parts from my proprietary program, it doesn't pull a date as needed.

So in this case, I'm looking to create a formula that will look at column A, and if the value in column H is not equal to a date, then copy the date in the corresponding (same case number) in it's place...

So in this example, I'd want to copy "12/7/2021 21:34" from H1, and copy it to H2 - replacing the "Village- HVAC..." text....The sheet im working on has alot of instances like this, so I'll be applying this formula/macro to the entire sheet.

I looked at some methods using VLOOKUP, but that would require me to create all of this data into a table (it's pretty substantial amount of data - over 300 rows).

Would this be a case for INDEX/MATCH?

If anyone can point me in the right direction, it would be extremely appreciated.

Thank you as always!

 

[jtakw]

Hi,

If the Labor row is Always ahead of the Parts row, this can do the job.
The formula will have to be in a separate Column, Not column H, if you want the values changed within column H, you'll need VBA.

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	78972							12/7/2021 21:34	12/7/21 21:34
2	78972							Village -HVAC	12/7/21 21:34
Sheet996

Cell Formulas
Range	Formula
I1:I2	I1	=IF(N(H1),H1,VLOOKUP(A1,A$1:H1,8,0))

 

[brasqo]

Thank you for looking jtakw!

I ended up using the following in VBA, and it worked as needed!!!.

Sub Change_Completed_Date_Parts()

'S = column I want the result posted.
Range("S1").Formula = "=IF(N(H1),H1,VLOOKUP(A1,A$1:H1,8,0))"
Range("S1", "S" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

End Sub

Thank you again!

 

[jtakw]

You're welcome, thanks for the feedback, glad you worked it out.