I have a Column In Excel Sheet where there are already two values separated by comma . I want to add the third value to the same column based on conditions:
I want to check if
CurrentRow(“QuoteNumber”).ToString of Output Sheet is equal to CurrentRow(“QuoteNumber”).To String of Unmatched Sheet AND CurrentRow(“GeneralClause_Field”).ToString =“General Clause Is not Matched”
Insert, General Clause is Not Matched in Sheet Name=Unmatched in Column(Mismatched Data)
My Output should be like. It should not replace previous values but add just by comma to the already present value
First you need to get existing value, the you use add the new value and write into the same place
// this need to be get from file
Var_existing_value = "A,B,C"
Var_new_value = "D"
// Add new value
Var_WriteVlaue = Var_existing_value +", "+Var_new_value
Write un same location
@dutta.marina I just provided to you logic and you can build according to your logic.
These are in two different sheets,. If the currentRow(“GeneralClauseFields”) contains value “General Clause Is n0t matched”, then it should update in other sheet column(“MismatchedData”) as (keeping previous values plus appending new value with comma)
Something like for Q30564 MismatchedData =PaymentTerms, TotalContractValue,PrimaryContact , GeneralClause Is not Mismatched.
Here the primary Key is QuoteNumber between two sheets.
No compilation errors but it didnot update the GeneralClause_Field
field is not mismatched under column(“Mismatched Data”) for Q30564. Do I need to write to excel after this code?
Looping through DT1
dt1.AsEnumerable.ToList.ForEach
Updating Mismatcheddata with mismatcheddata and new data
(Sub(r) r(“MisMatchedData”) = r(“MisMatchedData”).ToString + “,” +
Validation if dt1 current row r is matching with any rows
If(dt2.AsEnumerable.Any(function(x) x(“QuoteNumber”).ToString.Equals(r(“QuoteNumber”).ToString)),
If matched get the dt2 matched row and get the generalclausefield
dt2.AsEnumerable.Where(function(x) x(“QuoteNumber”).ToString.Equals(r(“QuoteNumber”).ToString))(0)(“GeneralClause_Field”).ToString,
Else send empty
“”).
At last trim if there is any comma at start and end
Trim(","c))
Can one more condition be added . Get the General Clause field only when the value of that field is "General Clause Is not matched " else do not updated in MisMatchedData fields.
x(“GeneralClause_Field”).ToString=“General Clause Is not Matched”
Here its updating GeneralClause is Matched and General Clause Is not Matched. If I just want to update “GeneralClause Is not Matched "
dt1.AsEnumerable.ToList.ForEach(Sub(r) r(“MisMatchedData”) = r(“MisMatchedData”).ToString + “,” + If(dt2.AsEnumerable.Any(function(x) x(“QuoteNumber”).ToString.Equals(r(“QuoteNumber”).ToString) AND Any (function(x)x(“GeneralClause_Field”).ToString=“General Clause Is not Matched”)),dt2.AsEnumerable.Where(function(x) x(“QuoteNumber”).ToString.Equals(r(“QuoteNumber”).ToString))(0)(“GeneralClause_Field”).ToString,”“).Trim(”,"c))
