Create a new excel after manipulation in existing excel with multiple lines

[Sonam_N]

Hello All,

I have one excel in which i have multiple states with different account. Sharing details for one state with different account.

State	Property	Account	Qb exem	Cs exem
AZ	33714	B2053001	10000	-5000
AZ	33714	B2053003	10000	-5000
AZ	33714	B2255003	10000	-5000
AZ	33714	B2053004	10000	-5000

I want a new excel in which for every account those have value in QB exem or Cs exem for that 2 lines need to created like below

State	Property	Account	Amount
AZ	33714	B2053001	10000
AZ	33714	B2053001	-5000
AZ	33714	B2053003	10000
AZ	33714	B2053003	-5000
AZ	33714	B2255003	10000
AZ	33714	B2255003	-5000
AZ	33714	B2053004	10000
AZ	33714	B2053004	-5000

Thanks

[Anil_G (Anil Gorthi)]

@Sonam_N

Follow steps

1. Read data into datatable dt
2. Create datatable dt1 with 4 columns and all string type
3. for each row in datatable dt
4. For each with {3,4}
5. Add data row with dt1 and itemarray as {currentrow(0).ToString,currentrow(1).ToString,currentrow(2).ToString,currentrow(currentitem).ToString}
6. Outside both loops write dt1 to excel as needed
   cheers

[rlgandu (Rajyalakshmi Gandu)]

@Sonam_N
Sequence7.zip (2.2 KB)

Input:

Output:

[Parvathy (PS Parvathy)]

Hi @Sonam_N

=> Read Range Workbook

Output → dtOriginal

=> Use below code in Invoke Code:

' Create a new DataTable to store the transformed data
dtTransformed = New DataTable
dtTransformed.Columns.Add("State", GetType(String))
dtTransformed.Columns.Add("Property", GetType(Double))
dtTransformed.Columns.Add("Account", GetType(String))
dtTransformed.Columns.Add("Amount", GetType(Double))

' LINQ query to transform the data
Dim query = From row In dtOriginal.AsEnumerable()
            Select New With {
                .State = row.Field(Of String)("State"),
                .Property = row.Field(Of Double)("Property"),
                .Account = row.Field(Of String)("Account"),
                .QbExem = row.Field(Of Double)("Qb exem"),
                .CsExem = row.Field(Of Double)("Cs exem")
            }

For Each item In query
    dtTransformed.Rows.Add(item.State, item.Property, item.Account, item.QbExem)
    dtTransformed.Rows.Add(item.State, item.Property, item.Account, item.CsExem)
Next

Invoked Code Arguments:

=> Write Range Workbook dtTransformed back to excel

FLOW:

XAML:
Sequence2.xaml (8.8 KB)

Hope it helps!!