If person has both a Roth and 401K display earnings amount once in gross earnings row.

[Cyndi2210]

If the person has a 401K & Roth, we only need to report the Gross Earnings once. I can't get the formula right.


[TABLE="width: 767"]
<colgroup><col><col><col span="2"><col span="5"></colgroup><tbody>[TR]
[TD="align: left"]Name[/TD]
[TD]Date[/TD]
[TD="align: left"]

Earnings​

​

[/TD]
[TD="align: right"]Gross Earnings[/TD]
[TD="align: left"]Type[/TD]
[TD="align: right"]401K[/TD]
[TD="align: right"]401L[/TD]
[TD="align: right"]Roth[/TD]
[TD="align: right"]Total[/TD]
[/TR]
[TR]
[TD="align: left"]Name 1[/TD]
[TD="align: right"]6/18/2012[/TD]
[TD="align: right"]574.75[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]28.74[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28.74[/TD]
[/TR]
[TR]
[TD="align: left"]Name 1[/TD]
[TD="align: right"]6/18/2012[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: left"]401L[/TD]
[TD][/TD]
[TD="align: right"]38.28[/TD]
[TD][/TD]
[TD="align: right"]38.28[/TD]
[/TR]
[TR]
[TD="align: left"]Name 2[/TD]
[TD="align: right"]4/1/2004[/TD]
[TD="align: right"]1206[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]241.20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]241.20[/TD]
[/TR]
[TR]
[TD="align: left"]Name 3[/TD]
[TD="align: right"]9/20/1980[/TD]
[TD="align: right"]1750[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]400.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]400.00[/TD]
[/TR]
[TR]
[TD="align: left"]Name 4[/TD]
[TD="align: right"]6/27/2000[/TD]
[TD="align: right"]1769.23[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]88.46[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88.46[/TD]
[/TR]
[TR]
[TD="align: left"]Name 4[/TD]
[TD="align: right"]6/27/2000[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: left"]401L[/TD]
[TD][/TD]
[TD="align: right"]43.72[/TD]
[TD][/TD]
[TD="align: right"]43.72[/TD]
[/TR]
[TR]
[TD="align: left"]Name 4[/TD]
[TD="align: right"]6/27/2000[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: left"]401L2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]106.51[/TD]
[TD="align: right"]106.51[/TD]
[/TR]
[TR]
[TD="align: left"]Name 5[/TD]
[TD="align: right"]9/30/1980[/TD]
[TD="align: right"]2700[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]50.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50.00[/TD]
[/TR]
[TR]
[TD="align: left"]Name 6[/TD]
[TD="align: right"]12/2/2014[/TD]
[TD="align: right"]1136.61[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]56.83[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]56.83[/TD]
[/TR]
[TR]
[TD="align: left"]Name 7[/TD]
[TD="align: right"]1/17/2018[/TD]
[TD="align: right"]551.25[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]27.56[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27.56[/TD]
[/TR]
[TR]
[TD="align: left"]Name 8[/TD]
[TD="align: right"]10/7/1992[/TD]
[TD="align: right"]1102.82[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]110.28[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]110.28[/TD]
[/TR]
[TR]
[TD="align: left"]Name 9[/TD]
[TD="align: right"]10/7/1992[/TD]
[TD="align: right"]1830.4[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]183.04[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]183.04[/TD]
[/TR]
[TR]
[TD="align: left"]Name 10[/TD]
[TD="align: right"]5/9/2005[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: left"]401L[/TD]
[TD][/TD]
[TD="align: right"]20.99[/TD]
[TD][/TD]
[TD="align: right"]20.99[/TD]
[/TR]
[TR]
[TD="align: left"]Name 11[/TD]
[TD="align: right"]8/13/2018[/TD]
[TD="align: right"]476.25[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]9.53[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9.53[/TD]
[/TR]
[TR]
[TD="align: left"]Name 11[/TD]
[TD="align: right"]8/13/2018[/TD]
[TD="align: right"]476.25[/TD]
[TD][/TD]
[TD="align: left"]ROTH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13.18[/TD]
[/TR]
[TR]
[TD="align: left"]Name 12[/TD]
[TD="align: right"]7/5/2017[/TD]
[TD="align: right"]955.5[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]28.67[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28.67[/TD]
[/TR]
[TR]
[TD="align: left"]Name 12[/TD]
[TD="align: right"]7/5/2017[/TD]
[TD="align: right"]955.5[/TD]
[TD][/TD]
[TD="align: left"]ROTH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18.75[/TD]
[/TR]
[TR]
[TD="align: left"]Name 13[/TD]
[TD="align: right"]8/9/2004[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: left"]401L[/TD]
[TD][/TD]
[TD="align: right"]75.21[/TD]
[TD][/TD]
[TD="align: right"]75.21[/TD]
[/TR]
[TR]
[TD="align: left"]Name 13[/TD]
[TD="align: right"]8/9/2004[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: left"]401L2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]33.21[/TD]
[TD="align: right"]33.21[/TD]
[/TR]
[TR]
[TD="align: left"]Name 14[/TD]
[TD="align: right"]4/23/2001[/TD]
[TD="align: right"]1097.2[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]32.92[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32.92[/TD]
[/TR]
[TR]
[TD="align: left"]Name 15[/TD]
[TD="align: right"]9/7/2006[/TD]
[TD="align: right"]518.61[/TD]
[TD][/TD]
[TD="align: left"] 401[/TD]
[TD="align: right"]15.56[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15.56[/TD]
[/TR]
[TR]
[TD="align: left"]Name 15[/TD]
[TD="align: right"]9/7/2006[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: left"]401L[/TD]
[TD][/TD]
[TD="align: right"]13.06[/TD]
[TD][/TD]
[TD="align: right"]13.06[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi Cyndi,
can you give a bit more context and explain it like I don't have the spreadsheet in front of me (I don't)? Is this a pivot table? Are there formulas in that column that refer to another sheet? Is the ordering of Name, Date & Type always the same? Etc.
Thanks for helping me help you,
Koen

 

[Cyndi2210]

Hi Cyndi,
can you give a bit more context and explain it like I don't have the spreadsheet in front of me (I don't)? Is this a pivot table? Are there formulas in that column that refer to another sheet? Is the ordering of Name, Date & Type always the same? Etc.
Thanks for helping me help you,
Koen

This data is being pulled from payroll records via Microsoft Query. All the columns except the bolded amounts in Name 11 & 12 Earnings columns are correct. Name 11 & 12 Earnings records are duplicating, since it is displaying the full earnings record for 401K and Roth. I only need the formula to display one Earnings amount in the Gross Earnings column for each person on the report. If the earnings amount is listed as 0 for the persons with 401L amounts that is correct, since these are loan repayment amounts. This report is uploaded to the 401K provider so it must stay in this format. I included the additional persons and columns for context only.

 

[Rijnsent]

Hi Cyndi,
if the data is pulled in by MS Query, you probably want to change the data there. What comes to mind: filter out the "Type = ROTH" or for Earnings: put in a formula like "IF Type = ROTH THEN Earnings = 0 ELSE Earnings = Earnings".
Hope that helps,
Koen

 

[Cyndi2210]

I wasn't able to get it to work on the query, because I'm not that good. However, I was able to use it to create the IF statement I needed once the information was returned to Excel. =IF([@Type]="ROTH",0,[@[Gross Earnings]]). Thanks so much.