Say that I have a data table that I get from Workbook A. Then, I want to filter data table based on a value that I have in Workbook B. The problem is, there are a lot of values that I want to filter in Workbook A.
E.g. 1st value is Jane, then I want to filter data table that has Jane value. Next row we have John, then I want to FILTER THE DATA that has John value and ADD it into the DATA TABLE. How can I achieve this?
Attached is the screenshot of my loop for what I have got so far. The problem with this is that it will change the filtered data to the new one as the loop continues, instead of adding new data.
Instead of using Write Range try with Append Range activity
Write Range - Will always remove the existing data and write the new data
Append Range - As the name suggests this will keep and previous data and just add the current data to the sheet
Hope this may help you
When I changed it to Append Range, the sheet become blank.
I have created a sample workflow based on your use case
Refer to it below!
Main.xaml (9.2 KB)
I couldn’t really understand how am I supposed to change the Assign activity. I assumed it’s to replace the Filter Data Table activity that I have, right? I have changed the columns from each excel according to their names but the error I got is that for “Column from excel2” cannot be found in table Data Table.
readDt.Select("[Columnfromexcel1]’"+CurrentRow(“Column from excel2”).ToString+"’").CopyToDataTable
You need to Change the Column name as per excel data!
Suppose you re having the in one excel column name like A and the values are equals with of Column B of another excel values then you have to write the select function like below!
Inside For each row you need to assign like below
DtFilter = readDt1.Select("[ColumnA]=’"+Currentrow(“ColumnB”).Tostring+"’).CopyToDatatable
The assign activity works okay now. But unfortunately, it gives me a blank data when I open the result excel. Then, I tried to change the Append Range to Write Range, it works, it’s just that the data gets overwritten.
Regards and thank you
Just wanted to update. It works now. No more blank excel. So thank you!
But before I close this topic, I’d like to ask you a few things if you don’t mind:
Does my data needed to be in Table format instead of just a normal range? I was just wondering because later on in the Assign activity I am going to refer the columns right, is it gonna affect anything if my data in both excel files are not in table format?
In the result file the data table is not going to become tables (even though I can add headers using Write Range activity), but is there anyway to automate it to become a table?
- Instead of refer column name in for each we can also use index!
2)Some how the output will be datatable we can convert to array or List!
This is what i have got from your question looping @ppr @yoichi @Srini84 @Palaniyappan @lakshman @rahulsharma @postwick @sonaliaggarwal47
for Further more suggestions!
Can you provide the format you are expecting? Is it in CSV or Text etc., based upon only we can provide the solution
This is also answered when the same format of the file is uploaded
sample.zip (28.2 KB)
Here I have attached the sample project for you.
For my first question, as you can see in both “Source File.xlsx” and “Reference File.xlsx” I made a TABLE for the data as I thought I must do it like that as it will be needed for the columns names in the assign activity. But as @pravin_calvin has answered I can refer to it using the column index so I guess I don’t need to…
For my second question, if you look at the output excel file, which I named it as “New Excel.xlsx” you can see that it’s just a normal range data and NOT a table… my question is, is there any way to make it to become a table? (Hopefully you get what I mean for this question)
Thanks and regards
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.