Show Only One Unique Value in Pivot Table?

goob90

New Member
Joined
Nov 12, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
I am not sure if I am asking the right question, but I will try.

I am creating a pivot table with data similar to what is shown below:
Book1
ABCD
1SystemIDNameAmount
2Payroll5067John Smith III25
3Vendor5067Smith John25
4Payroll8855Debbie Brown30
5Vendor8855Brown R Debbie30
6Payroll3465Ned Flanders45
7Vendor3465Flanders Ned45
Sheet1


When I create the pivot table, I structure it so the "ID" is in the Rows, "System" in Columns, and "Amount" in Values. I set up my pivot table in tabular format with no subtotals or grand totals.
Book1
ABC
3Sum of AmountSystem
4IDPayrollVendor
534654545
650672525
788553030
Sheet5


Everything looks great so far, but I would like to include the employee's name. The issue is, I only want the name to show once, or else it looks like this:
Book1
ABCD
3Sum of AmountSystem
4IDNamePayrollVendor
53465Flanders Ned45
6Ned Flanders45
75067John Smith III25
8Smith John25
98855Brown R Debbie30
10Debbie Brown30
Sheet5


Is there a way I can show only one name per "ID" and keep everything in one row versus two? The vendor's employee name is always in a different order than the payroll name, and it sometimes includes an initial in between the first and last name.

I would like it to look like this:
Book1
HIJK
3Sum of AmountSystem
4NameIDPayrollVendor
5Ned Flanders34654545
6John Smith III50672525
7Debbie Brown88553030
Sheet1
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I would think the simplest approach would be to use an Employee list with XLOOKUP...

Book1
ABCDE
1SystemIDNameAmount
2Payroll5067John Smith III25
3Vendor5067John Smith III25
4Payroll8855Debbie Brown30
5Vendor8855Debbie Brown30
6Payroll3465Ned Flanders45
7Vendor3465Ned Flanders45
8
9
Sheet1
Cell Formulas
RangeFormula
C2:C7C2=XLOOKUP(B2,Employees!$A$1:$A$3,Employees!$B$1:$B$3)


Book1
ABCD
15067John Smith III
28855Debbie Brown
33465Ned Flanders
4
5
Employees
 
Upvote 0

Forum statistics

Threads
1,223,956
Messages
6,175,619
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top