Compare multiple excel sheets or files for duplicates row

Help StudioX
,
1

Do you know how we can compare multiple excel sheets or excel files (like 7 sheets or files at a time) for duplicate row based on a specific column in studio x? I have been search for it but most of the tutorial is to compare 2 sheets/files only. Any tutorial for comparing more than 2? Thanks so much

2

Hi @ndtran85

Try this-

  1. Use the “Excel Application Scope” activity to open the first Excel file.
  2. Use the “Read Range” activity to read the data from the first sheet or file into a DataTable variable.
  3. Repeat the above two steps for each additional Excel file or sheet that you want to compare. Make sure to use separate DataTable variables to store the data from each file or sheet.
  4. Use the “Join Data Tables” activity to combine all the DataTable variables into a single DataTable. This will allow you to compare all the data together.
  5. Use the “Filter Data Table” activity to filter the combined DataTable based on the specific column that you want to compare for duplicate rows. Set the filter condition to identify duplicate values in that column.
  6. Use the “Write Range” activity to write the filtered DataTable into a new Excel file or sheet to view the duplicate rows.

Thanks!!

3

Let me try this. Thank you so much!!!

4

@Nitya1
when I used the “join data tables”, it kept giving me the alert “join data tables must have at least 1 valid argument to join on”. Do you know why i kept having the message?

image

5

Never mind! Sorry. I think I figure it out

6

@Nitya1

I tried the filter activity but it could not person the action that we are looking for. For example, we have the data of all the patients who refused medications for the last 7days. We want to compare the sheets to see who refused the medication for more than 4 days (out of that 7 days).