Hi
There are a couple of options here to do this, if you want to use a collection to just return the other 2 column values if the post code value exists then you can do this using a loop. Continue using the action Utility - Collection Manipulation -> Collection Contains Value to determine the post code already exists if it doesn't then mark it as an exception or whatever you need to do there. If the value does exist the do a loop through the collection using a decision to determine when you have found the value in the collection InStr([Collection.Postcode], [Postcode])>0 when the decision is true then you just need to get the values from the current row and you can do that with a calculation stage to 2 data items for the calulcation you just need to set the collection and column and set the data item to be saved to e.g. [Collection.SecondColumn] or [Collection.ThirdColumn].
The other option is to use excel to find the value then find the corresponding cell values but for this you will need to create a code stage to find the address of the postcode value and then a calculation stage to modify the cell address to include the column e.g. "B"&Mid([CellAddress],2,10000). I've included the code for this type of search below if you wanted to use it. Hope this helps 🙂
Dim wb, ws As Object
Dim excel, sheet, range As Object
Dim startcell as Object
Dim newCell as object
Dim xlformulas as integer = -4123
Dim xlRows as Integer = 1
Dim xlNext as Integer = 1
Dim xlPart as Integer = 2
Try
wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)
wb.Activate()
ws.Activate()
Excel = WS. Application
Sheet = Excel. ActiveSheet.Range(StartingCell). Activate()
startcell = Excel. ActiveCell
Ws. Cells.Find(What:=SearchText, After:=startcell, LookIn:=xlformulas, _
LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False). Activate()
newCell = excel. ActiveCell
Success = True
Result = newCell.Address(False,False)
Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
End Try
------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------
