Pivot Challenge

[AskGenie]

I'm hoping someone can steer me in the right direction.  I have a table with a list of employees and and calculated benefits for their weekly check.  I need to be able to upload this data but I'm not sure how I can perform this transformation.  The original table lists a row for every benefit but I need to pivot this so I only get one line per employee.  To make it more challenging, not every employee has the same number of benefits so one one employee the first benefit might be Annuity but for the next the first may be INS.  The transposition below is the closest I've gotten.  I need the first row to be column 1.  Any help would be GREATLY appreciated.

[AskGenie]

BRILLIANT!  Thank you soooo much.  I don't exactly understand what we did here but it works perfectly.  🙂

[AskGenie]

Thank you for your response, however, forgive me but I don't understand.  should I implement your suggestion starting from from where I left off (employee code at top) or from my original table?   What would a lookup formula for this look like?  Thank you.

[KT_Bsmart2gethe · Accepted solution]

Hi @AskGenie ,

My apology for not paying enough attention.

Starting point: (Based on the file I attached earlier) Step: Changed Type

We need two steps to achieve the outcome:

Step1: GroupBy

Once the GroupBy step generated, go to the formula bar replace the highlighted code below (Image A) with highlighted in Image B.

Image A

Image B

The next step is add a custom column with code below:

The final step is to combine all the tables by adding a custom step (blue circle below) and add the highlighted code:

Regards

KT

[KT_Bsmart2gethe]

Hi @AskGenie ,

I missed out on your first image. I was looking at the second one.

I mocked up the data as per the first image in excel, and please below link for the solution:

Solution 

Regards

KT

[AskGenie]

I may have mis stated.  I need the exact opposit.  I need this

to look like this

Thank you.

[KT_Bsmart2gethe]

Hi @AskGenie ,

There are a few ways to achieve the outcome.

One of the way is:

1. Unpivot all the columns that forming the two column you want (i.e. PEN, $$ COP $$ etc),
2. add index column,
3. perform a lookup function with the help of the index column to get the previous row for the value or the name (i.e. PEN / COP), and this will set the benefits and it value next to each other,
4. filter out the unnecessary rows.

Regards

KT