Hi All,
If I could pick you brain for a bit. I have a spreadsheet attached with 2 sheets:
Sheet 1 -> Base_214
Sheet 2 --> Base_120_Ajustada
Sheet 1 is the main and Sheet 2 is a support that has only 1 info needed do the MAIN SHEET 1 ( Base 214)
My problem:
Sheet 2 has the PO payment date in column A
I need the payment date to be shown in Sheet 1
I created a key using customer ID + PO Number in Sheet 1
did the same in Sheet 2
BUT: PO number in Sheet 2 has more caracters then the PO number in sheet 1 ( main sheet)
ex:
PO Sheet 1: ABCD122
PO Sheet 2: ABCD122#1 ( there´s no pattern to how many caracters are added AFTER the PO #)
So what I did is a search to see if PO in Sheet 1 is contained in PO Sheet 2 to return the Payment for that PO found in Sheet 2.
Since this PO # can be repeated a few times in the list - I used the MIN function to obtain the oldest payment date.
point is at the end it just returns 1/1/1900
There is like 35000 rows,
For clarification
Key_I - lead key that needs to be found is in column AS of BASE214 ( Main sheet)
Key_II -the one the above key will see if it´s contained is is in column U of Base_120_Ajustada ( Support Sheet)
PO Payment Date - Is in column A of Base_120_Ajustada ( Support Sheet)
If found PO Payment is to be filled in column AT of BASE214 ( Main sheet)
any help would be great.
If I could pick you brain for a bit. I have a spreadsheet attached with 2 sheets:
Sheet 1 -> Base_214
Sheet 2 --> Base_120_Ajustada
Sheet 1 is the main and Sheet 2 is a support that has only 1 info needed do the MAIN SHEET 1 ( Base 214)
My problem:
Sheet 2 has the PO payment date in column A
I need the payment date to be shown in Sheet 1
I created a key using customer ID + PO Number in Sheet 1
did the same in Sheet 2
BUT: PO number in Sheet 2 has more caracters then the PO number in sheet 1 ( main sheet)
ex:
PO Sheet 1: ABCD122
PO Sheet 2: ABCD122#1 ( there´s no pattern to how many caracters are added AFTER the PO #)
So what I did is a search to see if PO in Sheet 1 is contained in PO Sheet 2 to return the Payment for that PO found in Sheet 2.
Since this PO # can be repeated a few times in the list - I used the MIN function to obtain the oldest payment date.
point is at the end it just returns 1/1/1900
There is like 35000 rows,
For clarification
Key_I - lead key that needs to be found is in column AS of BASE214 ( Main sheet)
Key_II -the one the above key will see if it´s contained is is in column U of Base_120_Ajustada ( Support Sheet)
PO Payment Date - Is in column A of Base_120_Ajustada ( Support Sheet)
If found PO Payment is to be filled in column AT of BASE214 ( Main sheet)
any help would be great.
VBA Code:
Sub Search_Key_Returns_Date()
Dim UL As Long
UWht L = Sheets("Base_120_Ajustada").Cells(Rows.Count, "U").End(xlUp).Row
With Sheets("Base214")
.Columns("AT") = ""
.Range("AT2:AT" & .Cells(Rows.Count, "AS").End(xlUp).Row).Formula = _
"=MIN(IF(ISNUMBER(SEARCH(AS2,Base_120_Ajustada!U$2:U$" & UL & ")),Base_120_Ajustada!A$2:A$" & UL & "))"
.Columns("AT").Value = .Columns("AT").Value
.Columns("AT").NumberFormat = "dd/mm/yyyy": .Columns("AT").AutoFit
End With
End Sub