Convert column to row based on one column

[arina]

I have this table:

And I want to convert it to this:

The key is column W*:
If its value is E or S, it will be grouped into one row for the same Cust. Meanwhile, if the value is N, each N will be a new row.

Attached is the input file.
Status.xlsx (12.0 KB)

[Eric_Wong_Test (Eric Wong)]

Convert.xaml (51.6 KB)
Please change the input excel path and the sheet name

[arina]

Thank you !! your code works great but for W * where the value is E or S, the location was split by comma (when tested with actual data) and the whole value move forward. And column Total should be after W 15 (yes the data until 15 sorry for not mentioning earlier) and before Remarks, Status and Success.

Status.xlsx (10.4 KB)

[Eric_Wong_Test (Eric Wong)]

Convert.xaml (51.6 KB)
Status.xlsx (13.0 KB)
I use this input excel in running. Is it right?
If not. Can you provide the input excel?
The source I have changed. It can handle w 15.

[arina]

Here’s attached the input file. W column is Warehouse column.
Status (New).xlsx (11.4 KB)

[Eric_Wong_Test (Eric Wong)]

Convert.xaml (54.6 KB)
Please Try.

The output result:

[arina]

thanks !! it works wonderful !! let’s say if i have comma in description, then i just need to add this only right?

and if want to add another repetitive column, then i need to add this here right?

do i need to change this to 5 too?

may i know what this part do?

and may i know in which part do you remove column with all empty values?

[Eric_Wong_Test (Eric Wong)]

1. Yes
   Also, you need to replace it again.
   if don’t do this, the excel result will not change to “,” for location column
   go there
   

   
   
   
   

2. Yes
   Also, you need to add the new value into an “add data row”
   

   

3. No. This one should be for these four columns (Code, Cust, Location, Date)
   As sometime it will only split the row for these four columns

4. Checking which column data is not blank