How do you Order an Excel file by column when the file has multiple tables in the same sheet?

Hi guys,

How do you order columns in excel?, i tried using the sort data table activity but the result are less than optimal because the file doesn’t have headers, it starts to mix things and there are two tables in the same sheet. here are some examples

and this is what i want to achive:

excel2

You can use Read Range on the whole sheet to get the original data, let’s call this dt_original. Have it grab the headers on the very first row so that your columns are named. Then you can use a For Each Row to loop through all of the data rows. If the row contains good data (not empty, first and second columns are numbers, etc.) then add the DataRow to a new table, let’s call it dt_new.

Then dt_new should contain just the data and not the empty spaces or the additional headers. From there you can sort the DataTable and then write it to a file.

2 Likes

Thanks,

your answer was very helpful, instead of using a “for each” statement, i did it with this expression

dt_original.AsEnumerable.Where(function(a) IsNumeric(a.Item(0).ToString)).CopyToDataTable

3 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.