Hi,
I have 2 files and I am doing a vlookup. I was succesfull in getting 1 column of data, however I need to get multiple columns of data. I am attaching the sample files and xml file. The sample file is right now limited but in my business use case the columns keeps varying.
Marks.xlsx (10.3 KB) MasterData.xlsx (8.5 KB) Main.xaml (3.2 KB)
Hi @manjesh_kumar,
One possible solution could be to copy & apply the solution created by you on a different column, and afterwards check the result.
It could be a series of vlookups, or you can use some kind of flowchart for efficiency, for instance:
VLOOKUP_Check_Column1 —> Result 1
If Result 1 is Nothing, then
VLOOKUP_Check_Column2 —> Result 2
If Result 2 is Nothing, then … and so on
Hope this helps.
Best regards,
Marius
Hello @Marius_Puscasu,
Thank you for the response
I have mentioned that in my use case there are more than 25 columns and it can vary, is there a way where I can loop until I find the last column data.
Hi @manjesh_kumar,
It does not matter how may columns do you have.
You can use a For Each loop to iterate thru all of your columns, then you can modify your lookup activity in order to receive as parameter a column at the time.
When your lookup returns a value, then you have found the match and, afterwards you can go to next row …
Best regards,
Marius
Hello @Marius_Puscasu,
How do i get the column address like ‘D’, ‘E’ for me to put this in a loop dynamically. Currently the cell value where i write is static which I want to change dynamically.
“C”+(DT1.Rows.IndexOf(row)+2).ToString
Regards
Manjesh
Hi @manjesh_kumar,
You can use an array to get all the columns from your data table:
arrColumnnames = (From dc In yourDT.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray()
Then you can use a For Each loop to iterate thru your columns arrColumnnames
In order to correctly identify a position you could use this assignment
yourDT.Rows(yourDT.Rows.IndexOf(row))(item.ToString) = your value
where item.ToString is referring actually to your column name
Therefore in your specific case,
“C” +(DT1.Rows.IndexOf(row)+2).ToString
can be replaced with
DT1.Rows(DT1.Rows.IndexOf(row)+2)(item.ToString)
Hope this helps
Best regards,
Marius
Hi @manjesh_kumar,
But you have the column address. In the solution mentioned above, the identified column names are included within the column address you are looking for
For instance,
“B” +(DT1.Rows.IndexOf(row)+2).ToString has the same value as
DT1.Rows(DT1.Rows.IndexOf(row)+2)(item.ToString) (when item.ToString is “Name” ← item from the For Each loop). It’s just a different way to reference a cell, and it is dynamic…
Best regards,
Marius
Dear @Marius_Puscasu,
In this current example there is a column name but in the real use case there is no column name and I have to rely on the column id .
After I do a vlookup and write in the column “C” then I need do a vlookup for the same column “ID” from master.xlsx column and then write start writing the data in “D” Column of master.xlsx.
Regards,
Manjesh
Hi @manjesh_kumar,
I’m afraid that that I don’t know what are you looking for.
You have already created a lookup activity for one column, which is running well.
Now just surround it with a For Each activity & some result checks and you are applying the lookup to a whole range…
Best regards,
Marius
Dear @Marius_Puscasu,
Thank you for being patient and trying to help me, appreciate that. 
Regards,
Manjesh
Anil_G
ashokkarale
Ajay_Mishra
Gautham_Pattabiraman
BHUSHAN_NAGAONKAR1
vrdabberu
ABHIMANYU_THITE1
lrtetala
samantha_shah
shyamala_shyamu