Merging DataTables and outputting to different sheet

Help Studio
1

Hi guys, really hoping someone can point me in the right direction on this one.

Goal: To input a Product from Sheet 1, get various part numbers from each one and populate it into Excel (see attached excel file)

My Flow is as follows

Excel - Copies Sheet 1 (dtSheet1) and copies Sheet 2 (ctSheet2

I then Merge the two DataTables together.

Using a series of Get Text and Assigns within a For Each of the merged Data Table, it then copies the required information to variables and Appends them to Excel.

However! The problem I’m facing is that if it finds multiple part numbers (i.e. 4 part numbers for the memory, which is probable, then it then duplicates the rows into the final output.

Galaxy S8 memory_number1
Galaxy S8 memory_number2
Galaxy S8 memory_number3
Galaxy S8 memory_number4
Galaxy S9 memory_number256
Galaxy S9 memory_number256
Galaxy S9 memory_number256
Galaxy S9 memory_number256
iPhone 9 memory_number359
iPhone 9 memory_number359
iPhone 9 memory_number359
iPhone 9 memory_number359
iPhone 10 memory_number344
iPhone 10 memory_number344
iPhone 10 memory_number344
iPhone 10 memory_number344

But it needs to simply show:

Galaxy S8 memory_number1
Galaxy S8 memory_number2
Galaxy S8 memory_number3
Galaxy S8 memory_number4
Galaxy S9 memory_number256
iPhone 9 memory_number359
iPhone 10 memory_number344

reg.xlsx (14.1 KB)

I’ve attached an Excel file which shows source data.

Basically I then I need it to go through one product at a time…find however much data is in there and put into Excel.
Then it goes to the next product and finds however much data is there and lets say it finds 1 set of data, it puts it in one row, irrespective of how much it found for the previous product

2

your case looks like removing duplicated rows

Assign Activity
LHS:dtDistinctData | datatype: DataTable
RHS
YourDataTableVar.DefaultView.toTable(True, {"Col1Name","Col2Name"})

3

Thank you for the reply :slight_smile: It fixes one issue I didnt realise I had, where it wasn’t outputting to the correct Column, but it doesen’t remove the duplicates unfortunately. It seems to remove duplicate columns(?) but not rows

4

DefaultView… Method will decide on

  • the first argument (Boolean) the data is to deplicate
  • will extract and do it on the columns assigned on the second argument, the string array

As your sample data did only show 2 columns so it was assumed that this will the data

In case of we have to deplucate data with more columns, but the duplicate identification is done only on a subset of columns we can do it with a LINQ

  • GroupBy Approach
    OR
  • sequenceEqual approach
5

Apologies, in the attachment from the first post it does show the other columns and there may well be data in there.

I’m unaware of LinQ, are you aware of any training on this?

6

have a look here:

7

Thank you.

Another issue I’m facing is that when I merge the DataTables, its adding 2 columns from the first sheet to the second sheet.
But what’s then happening is that when it’s outputting, despite specifying the column header, it’s outputting the data to the wrong column - shifting it 2 spaces to the right.

8

please visualize the issue. Thanks

9

Currently Sheet1 contains:
VIN Description
image

The second sheet contains:
VIN2 COMPONENT1 COMPONENT2 COMPONENT3 COMPONENT5

image
image945×25 990 Bytes

I use Merge DataTable to combine the two

Then scrape the info from the web and in particular, specify for it to scrape to column ‘COMPONENT1’ -row.item(“COMPONENT1”)

But it’s putting the data in COMPONENT3