How to Read Headers which is in left side

Suppose i have an excel in this formate

Area DAM DAM DAM PAN PAN PAN LAN

Axile A1 A1 A2 A1 A2 A2 A1

Here i want to extract a unique Area . And i want the output in table formate like borders and columns we do manually in excel. The output Should be like this

Area| DAM | PAN | LAN|


    | A1  | A2   | A1  |  A2   |     A1   | 
    |       |         |       |         |            |  

Experts please help

Please share a screenshot or example file of input and expected output. Do I understand correctly that you want to recreate the borders formatting from original file?

Not only Borders But I also want to create the columns of distinct values of each row in a column.
Here Area is the Header of that particular row and axile is the header of 2nd row.
For One DOM we have Two Axile.So We need to produce output like this

DOM


A1|A2
|

Like these for others area also we need to do

Ok, I think I understand now, does the image show what you are trying to achieve?
image

First of all, does it matter whether the data is stored horizontally or vertically in excel? It would make the task a lot easier if we could use “Area” and “Axile” columns instead of rows.

  1. Read range
  2. Remove duplicates
  3. Sort by Area and Axile (use Invoke Method: dataTable.DefaultView.Sort = “Area, Axile”)
  4. Write Range to Excel.

If horizontal layout has to be kept, then you need to transpose data before reading it and after writing it to excel.

Borders and cells merging can be executed by “Invoke VBA” and executing VBA macro inside the excel file

Output Step2 is correct but beside DAM “Area” Header should also be there as a separate column.And the formate should be like this only as you showed in Output step2

To keep horizontal layout you need to:

  1. read range, with “Add Headers” Property set to false.
  2. Transpose the datatable (create “transposedDT” using nested for each loops)
  3. Remove duplicates
  4. Sort by Area and Axile (use Invoke Method: dataTable.DefaultView.Sort = “Area, Axile”)
  5. Transpose the table back to original layout
  6. Write to Excel with “Add Headers” set to false, starting in Cell “B1” (leaves place for)
  7. Write Cell “AREA” in A1
  8. Write Cell “AXILE” in A2
  9. Execute Macro, that will merge cells with same value in second Row.
  10. Execute Macro, that will add borders in range

How will i transpose Can you expain in detail