Hi
I need to create a condition that will Find the column position from two different tables.
In Fixed column datatable, this is where the Columns from Excel needs to match and write all the data position new excel file
Fixed Column in Datatable:
Request ID,
Subject,
Total Amount of Transaction Involved,
Account Number,
Status,
Dependency,
Item,
Action Taken,
Created Date,
Notes,
SLA,
Urgency,
Impact,
Project ID,
Requester,
Assigned To,
Category,
Department,
Technician timer
here is my excel data table column
Request ID Subject Account Number Mode Total Amount of Transaction Involved ARL Request Item Access Request Item Group Requester Created Date Item Level On behalf of Approval Status Service Category Impact Is SLA Violated Priority Resolved Date Created By Scheduled End Time Response DueBy Time
Here is the logic i follwed but its not working 
- Read Excel Range: Read the first row of the Excel sheet to get the existing column headers.
- Assign: Create a list to store the order of columns based on the fixed database header.
- For Each: Loop through each fixed database column header and find the corresponding index in the existing Excel column headers.
- Add Data Column: Create a new DataTable with the fixed database headers.
- For Each: Loop through the rows in the Excel DataTable and copy data from the existing DataTable to the new DataTable based on the re-arranged column order.
- Write Range: Write the re-arranged DataTable back to the Excel sheet.
Here is the sample code:
1. Read Excel Range:
- Excel Application Scope
- Read Range activity to read the first row of the Excel sheet into a DataTable (let's call it excelDataTable).
2. Assign:
- Create a new list of strings to store the order of columns based on the fixed database header.
```vb
Assign: fixedDatabaseHeadersOrder = {"Request ID", "Subject", "Account Number", "Mode", "Total Amount of Transaction Involved", "ARL Request Item", "Access Request Item", "Group", "Requester", "Created Date", "Item", "Level", "On behalf of", "Approval Status", "Service Category", "Impact", "Is SLA Violated", "Priority", "Resolved Date", "Created By", "Scheduled End Time", "Response DueBy Time", "Actual Date of Transaction or Incident", "Last Update Time", "Subcategory", "Status", "Scheduled Start Time", "Template", "Action Taken", "Is Service Request", "Assets Item Maintenance Summary", "Urgency", "Asset Item Request", "Department", "Has Linked Requests", "Technician", "DueBy Date", "Total Amount of Transaction Involved:", "Site", "Completed Date", "Category", "Is Maintenance"}
```
3. For Each: ---1st Loop Excel Data table
- For each fixed database header, find the index in the existing Excel column headers.
```vb -- 2nd Loop DatabaseHeader
For Each: fixedDatabaseHeader in fixedDatabaseHeadersOrder
Assign: columnIndex = excelDataTable.Columns.IndexOf(fixedDatabaseHeader)
Add to Collection: indexList (TypeArgument: Int32) = columnIndex
```
4. Add Data Column:
- Create a new DataTable with the fixed database headers.
```vb
Assign: rearrangedDataTable = New DataTable()
For Each: columnIndex in indexList
Add Data Column: columnName = excelDataTable.Columns(columnIndex).ColumnName, Type = excelDataTable.Columns(columnIndex).DataType
Add Data Column to: rearrangedDataTable
```
5. For Each:
- Loop through each row in the Excel DataTable and copy data to the new DataTable based on the re-arranged column order.
```vb
For Each: row in excelDataTable.Rows
Assign: newRow = rearrangedDataTable.NewRow()
For Each: columnIndex in indexList
Assign: columnName = excelDataTable.Columns(columnIndex).ColumnName
Assign: newRow(columnName) = row(columnIndex)
Add Data Row: newRow to rearrangedDataTable
```
6. Write Range:
- Write the re-arranged DataTable back to the Excel sheet.
```vb
Write Range: Write the rearrangedDataTable to the Excel sheet
```
This code assumes that the Excel headers and the fixed database headers match exactly. If there are any variations, you may need to handle those accordingly.