trying to reorder + combine excel cells while also using column name headers inside the cells.

i have the following excel sheet document as shown below:

And I kind of need to remove the empty cells in rows and then merge the last cells for every row and and the collumn name (date) beside each cell content example the CL or 1 Box or Bag so it would become CL/Wed:Feb-01 for example with 1 cell only but if multy data cells then like this: 1 Box/Fri:Feb-03, 1 Box/Sun:Feb-12 as an example.

below is what final result should be:
im lost with all the filtering attempts I tried from tutorials can you please point me in right direction if I can learn how to do this it will help me to handle many similer types of excel files that I need to edit this way too.


Welcome to the KNIME Community!

Can you please upload that Excel file as shown in the first screenshot? People are usually more inclined to help you if they do not have to type that all manually over again and also allows to properly validate any proposed solution :wink:

One way to solve it is via a column list loop and append each record with the column name and aggregate them afterwards.


PS: Can you verify the expected output for person4 as well. The sheet says CL, CL, Box but your expected output table says CL, Bag, CL


@ArjenEX Wow, you are fast!

@ECHOSTORM Here is another way to do it using the Unpivoting node.
Unpivot_example.knwf (10.8 KB)

1 Like

Very good solution as well! One improvement I would make is to, due to the nature of the file, make the column selection dynamic with [A-Za-z]{3}:[A-Za-z]{3}.*

Im very sorry I didnt upload the file
Test_ rami.xlsx (19.7 KB)
my first post on forum my bad!

thanks to all your suggestions ill try them out today after work, really appreciate the help thanks again.

And I uploaded the excel file for you here.

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