Hi, i tried a lot but i am not getting output for VLookup.
Please find the below excel samples and sample code.
Please someone help. its very urgent
ReceiveSKUOutput.xlsx (10.5 KB)
sample.xaml (14.0 KB)
ECC To Investigate_16_3_2022.xlsx (76.8 KB)
Hi @Sirisha_Siri ,
The bot is unable to detect it
I’ll look into it some more from my side.
Kind Regards,
Ashwin A.K
Hi @ashwin.ashok .
yes, pls check it…that would be so much helpful
Also can u pls help me with LINQ query for performing VLOOKUP in ui path, because its being very difficult when we are having large amount of data
Hi @Sirisha_Siri ,
This is the issue:
The formatting was provided as null, so it took just the visible values as shown in the screenshot below:
After changing settings →
If there are no matches in the Vlookup, it won’t throw an error, instead it will return an empty string.
You can verify that by declaring a Row Index in the Lookup Table Activity and check if its equal to -1 and handle it if necessary.
Kind Regards,
Ashwin A.K
Thankyou so much @ashwin.ashok
Now should i change the “read formatting” to “RawValue” ?
Hi @Sirisha_Siri ,
Yes, could you please give that a try and let me know if it works as expected?
Kind Regards,
Ashwin A.K
After changing the settings, i am able to get output in the message box, but its not writing back to excel . once can u pls check whats the issue…
output has to be written to ecc to investigate excel
Hi @Sirisha_Siri ,
I bet the values are being writing into the Hidden Rows.
Could you check and confirm?
Kind Regards,
Ashwin A.K
Yes, u r right but i need to right the values corresponding to that serial number only…how can i do that.hiden rows will be there according to the filters that were applied earlier…vlookup has to be performed on filtered data and it has to write values corresponding to that row only
Could you try this to retrieve the Index?
dt.AsEnumerable().Where(Function(w) w.ItemArray.Contains(skuoutput)).Select(Function(s) dt.Rows.IndexOf(s))+2
Kind Regards,
Ashwin A.K
It is showing error @ashwin.ashok
Slight error →
dt.AsEnumerable().Where(Function(w) w.ItemArray.Contains(skuoutput)).Select(Function(s) dt.Rows.IndexOf(s)).First()+2
Kind Regards,
Ashwin A.K
Ok give me a second, caught up with a lot of work right now, will get back to you in a moment.
Kind Regards,
Ashwin A.K
There appears to be a mismatch between the Serial Number in sheet1 and sheet2
Mat docs contains the lookup value.
Please validate the documents.
I am not entirely sure about the values you are trying to lookup, please be a bit more descriptive so that we can reach a conclusion.
Kind Regards,
Ashwin A.K
Matdocs column contains the output, i kept it manually for reference. Actually I am trying to get the values of “Documents” from “Receiveskuoutput” document which has same serial numbers in both the excels. the output should be written in the “ecc to investigate” excel
now what’s happening with my code is…it is writing the output value to the starting cell. i tried to get the row index, but its throwing a runtime error at assign activity. it is not retrieving row index. we are getting the output but its not getting placed in proper row
Ok got it, thank you for the extra information.
I’ve attached the sequence for you.
Also, we have to read sheet2 twice - once to retrieve the visible cells, and second, to read all the cells so as to understand which row has to get filled.
Convert.ToString(dt_forRowCount.AsEnumerable().Where(Function(w) w.ItemArray.Contains(row("Serial Number").ToString)).Select(Function(s) dt_forRowCount.Rows.IndexOf(s)).First()+2)
sampleModified.xaml (15.0 KB)
Kind Regards,
Ashwin A.K
Thankyou so much @ashwin.ashok