Remove error rows from the dt?

[sangasangasanga (sanga)]

Hi, I have 2 input files. If a value is in input1 file and input2 file then it passes this condition.

However when the value in input1 file, does not match in input2 file = the error inputs are written somewhere. But in the output, the error rows are shown with the previous values duplicated.

E.g. of input1. Those highlighted in yellow are not in input2 file

In the output the error rows uses the previous values.

But I would like the rows to not duplicate the previous row if it is an error row

I tried using clear data table but it doesn’t work

Append Range duplicating data when using in for each

Good idea dinesh. Hey, I also wanted to mention that If you delete rows from your existing table, then use Append Range, it will keep all the blank rows where you deleted rows. So, if that ever happens, you need to perform a “Clear All” or “Clear Contents” (found next to the Format in Menu Ribbon) of entire range, followed by Writing the Existing table back on before Appending another table. Discovered this recently and thought I’d share since it was related.

Pls help.

Thank you xoxo

[MAHESH1 (MAHESHKUMAR JV)]

@sangasangasanga

Do you want the the values which are there in both Inputs as Output.

Regards,
Mahesh

[ClaytonM]

Hello.

I don’t think we can give you an answer without seeing some of the logic that was used to output the error rows.

It’s possible that you are using Read Range to get the existing error rows, then using Append Range to output the table. This would cause you to essentially Append existing rows. Normally, instead, you would want to either A) use an empty table to add the errors then use Append Range or B) use the Read Range to get the existing error rows then use Add Data Row with the new error line, followed by Write Range… but there are probably a few other approaches as well.

Regards.

[sangasangasanga (sanga)]

@MAHESH1 yes

[sangasangasanga (sanga)]

@ClaytonM I am doing everything under for each row for input(input file1 name). There is a if to compare the values between 2 files (dt is input file2 name). I cant write another table in, I tried that got abit messy

[ClaytonM]

Hi.

This is what your code is saying, which will help identify the issue:

Build an empty NoServer table
Read existing Server Names from Sheet1 to dt variable
For each item, If input Server is not in dt variable, then Add it to the NoServer table
Write Range the NoServer table to Sheet2

So I see 2 problems:
—You are reading from Sheet1 but then writing to Sheet2, so the Server file doesn’t get updated from Sheet1.
—After you add a Server to the noServer table, you are still checking the Server list from Sheet1 that is stored in the dt variable.

To solve this, I would recommend that you think about the noServer list as the existing Servers that you want to update to from the Excel file, and that is the list that you check if the Server is already found. (you only need to build an empty noServer table if the Excel file does not exist.

To clarify, let me provide you some sample snips of the edits.

The above shows that you can check if File exists, if it does not then Build the table. As shown, I put the noServer variable in the Read Range. —Then, you can use that variable in the .Select instead

Adjusted .Select to look in noServer which is the updated Server table.

And shown there, that you can use Sheet1 to overwrite the Server table with the updated one.

I hope this helps you.

Regards.