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.

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

<colgroup><col><col><col span="2"><col span="5"></colgroup><tbody>
</tbody>

 

[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.