Check data 2 sheet?

[fairymemay (noona)]

I want to map data 2 sheet (Sheet Data , Sheet Extra)

sheet Data as below.

Sheet Extra as below.

I want to read value in column APAPNO with column App no. in sheet Extra.
It found in sheet Extra —> write text Extra in that row in sheet Data. (in highlight yellow)

Please guide me about it.
mapData1.xlsx (12.2 KB)

[k.entwistle (Katie Entwistle)]

@fairymemay this is an excel formula rather than an automation requirement.

In cell D2 on Data sheet, write:

=if(iserror(vlookup(F2,'Extra'!A:A,1,0)),"","Extra")

and fill the formula down the rest of the table.

This will return the word “Extra” in the status column if the APAPNO exists on the Extra sheet in the App no. column.

[fairymemay (noona)]

@k.entwistle It I want map with column name how to edit it?

[k.entwistle (Katie Entwistle)]

Convert your data to a table instead, it offers more flexibility in that way.

[fairymemay (noona)]

Any solution about this case ?

LINQ ?

[ashwin.ashok ((TheCodingTheory))]

Hi @fairymemay ,

Is this what you were looking for?

CompareSheets.xaml (7.1 KB)

Kind Regards,
Ashwin A.K

[fairymemay (noona)]

@ashwin.ashok Why I run It write column as below.

And If I have column 20 column but I don’t want write in InArgument row(0) to row(20)
Because In future maybe add column.

Please guide me about it.

[ashwin.ashok ((TheCodingTheory))]

Hi @fairymemay ,

Could you try this instead and let me know if that works out for you?

(From row In dt_data.AsEnumerable()
Let status = If(lst_appNumbers.Contains(row("APAPNO").ToString.Trim),"Extra","")
Let ra = row.ItemArray.Take(2).Append(status).Concat(row.ItemArray.Skip(3)).ToArray()
Select dt_updatedData.Rows.Add(ra)).CopyToDataTable()

Kind Regards,
Ashwin A.K

[fairymemay (noona)]

@ashwin.ashok I​ have​ a​ question​ if​ in​ future​ column​ Status​ have​ change​ position​ column
But​ I​ don’t​ want​ edit​ code.
It can use​ column​ name​ for​ set​?

[ashwin.ashok ((TheCodingTheory))]

Hi @fairymemay ,

You can simply append the Status column to the end and use an Invoke Method Activity to set its Ordinal to wherever you see fit.

Kind Regards,
Ashwin A.K

[fairymemay (noona)]

@ashwin.ashok How​ to​ apply​ invoke​ code​ in​ this​ case?

[ashwin.ashok ((TheCodingTheory))]

Hi @fairymemay ,

Here is a sample workflow for that aswell:

(From row In dt_data.AsEnumerable()
Let status = If(lst_appNumbers.Contains(row("APAPNO").ToString.Trim),"Extra","")
Let ra = row.ItemArray.Append(status).ToArray()
Select dt_updatedData.Rows.Add(ra)).CopyToDataTable()

CompareSheets_v1.xaml (9.2 KB)

Note: Index starts from 0, so if you want the column to be present on the third column, you have to pass in 2 as an argument to the Invoke Activity.

Kind Regards,
Ashwin A.K