Need help summing, assigning a role and bringing that back into the original sheet.

Vanessa_21460

​Hello Team,

Need everyone's help again. I have a process that currently uses a macro in the beginning and then formatting at the end this is a two part process and is done by two separate people I am hoping to eliminate the split work by putting everything in DPS.

The Macro takes a report does some basic formatting and then creates a pivot based on assignment number to assign a responsibility to it. Then brings the assignment back into the original table and deletes the pivot.

My data  starts out looking like this

I sum it like this and assign a value based on different calculations like line is C=C and D=0 assign it to No Receipt, ect...

1736100005	12	12	0	-12
1736100011	12	12	0	-12
1736100012	3	3	0	-3
4126100549	27	27	0	-27
4126100546	17	17	0	-17

Is there anyway that I can create an assignment for these items and bring that value back into the original sheet? Does not look like grouping or summaries will work is there any other way?

High level the macro makes a pivot, assigns a role, and then does a vlookup to insert the role back into the original sheet. Not sure how to do this in DPS.

​​​

------------------------------
Vanessa Tanner
Manager Optimization Services
Gap Inc Store Labs
San Francisco NM
(505) 462-0455
------------------------------

Baba_Majekodunmi_703

Hi @Vanessa Tanner !

Good to hear from you here in the community. Short answer is that I believe DPS can do it all.

In summary these are the parts of the process you've highlighted that we can address:

• Macro Does Basic Formatting: As long as it's strictly data formatting (ie no color formatting in excel, etc) then Data Prep Studio can do it.
• Pivot Table: Possibly can be accomplished with a Transform>Group function.
• Adding Assignment Number: This can be done with a calculated field using your logic.
• Adding it back to the Original Table: You can simply do a join to the Transform>Group table back to the Original table, DPS allows that functionality.

Happy to chat or walk through this if you like.



------------------------------
Baba Majekodunmi
Solutions Consultant
Altair Engineering Inc.
Manassas VA
978-275-9325
------------------------------
-------------------------------------------
Original Message:
Sent: 12-12-2019 10:33 AM
From: Vanessa Tanner
Subject: Need help summing, assigning a role and bringing that back into the original sheet.

Hello Team,

Need everyone's help again. I have a process that currently uses a macro in the beginning and then formatting at the end this is a two part process and is done by two separate people I am hoping to eliminate the split work by putting everything in DPS.

The Macro takes a report does some basic formatting and then creates a pivot based on assignment number to assign a responsibility to it. Then brings the assignment back into the original table and deletes the pivot.

My data  starts out looking like this

I sum it like this and assign a value based on different calculations like line is C=C and D=0 assign it to No Receipt, ect...

1736100005	12	12	0	-12
1736100011	12	12	0	-12
1736100012	3	3	0	-3
4126100549	27	27	0	-27
4126100546	17	17	0	-17

Is there anyway that I can create an assignment for these items and bring that value back into the original sheet? Does not look like grouping or summaries will work is there any other way?

High level the macro makes a pivot, assigns a role, and then does a vlookup to insert the role back into the original sheet. Not sure how to do this in DPS.



------------------------------
Vanessa Tanner
Manager Optimization Services
Gap Inc Store Labs
San Francisco NM
(505) 462-0455
------------------------------"