Use a pivot table or formula?

D

designerken

New Member
Joined
Nov 8, 2022
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
I am very new to excel so please forgive me. Plus I am using O365 on a Mac so some excel functions dont work.

Basically I have a data table that repeats every 16 rows, each row consists of a user (id:name:email) and a course title and if they have completed that course (YES:NO). (data attached as image)

excel-table-worksheet.jpg


I would like to create a dashboard type setup on another worksheet. That lists each user once in a row and has yes/no if they have completed the course (see attached). Can/should this be done in a pivot table? Should I right out as a formula? I was hoping to re-use this as much as possible with other worksheets I have with similar data.

sample-layout.jpg

Any help in the right direction would be welcomed. TIA
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
broadmoor_reports_user_courses_2022-11.xlsx
ABCDEFGHI
1user_idnameemailcourse_idcourse_titlesteps_completedsteps_totalcourse_completedcourse_completed_on
2534John Doejohnd@gmail.com4113Course 0122YES5/11/21
3534John Doejohnd@gmail.com1774Course 0222YES5/12/21
4534John Doejohnd@gmail.com3571Course 0388YES5/11/21
5534John Doejohnd@gmail.com1668Course 0477YES5/12/21
6534John Doejohnd@gmail.com1776Course 051414YES5/12/21
7534John Doejohnd@gmail.com1765Course 0677YES5/11/21
8534John Doejohnd@gmail.com1757Course 0766YES5/11/21
9534John Doejohnd@gmail.com1763Course 0877YES5/11/21
10534John Doejohnd@gmail.com1772Course 0988YES5/12/21
11534John Doejohnd@gmail.com1767Course 1077YES5/12/21
12534John Doejohnd@gmail.com1761Course 1166YES5/11/21
13534John Doejohnd@gmail.com1576Course 121010YES5/12/21
14534John Doejohnd@gmail.com4424Course 13010NO
15534John Doejohnd@gmail.com4368Course 1403NO
16534John Doejohnd@gmail.com1759Course 1599YES5/11/21
17534John Doejohnd@gmail.com2952Course 1633YES5/11/21
18540Jane Doejanedoe@gmail.com4113Course 0122YES4/7/21
19540Jane Doejanedoe@gmail.com1774Course 0222YES5/31/21
20540Jane Doejanedoe@gmail.com3571Course 0388YES4/7/21
21540Jane Doejanedoe@gmail.com1668Course 0477YES5/31/21
22540Jane Doejanedoe@gmail.com1776Course 051414YES5/31/21
23540Jane Doejanedoe@gmail.com1765Course 0677YES5/31/21
24540Jane Doejanedoe@gmail.com1757Course 0766YES5/31/21
25540Jane Doejanedoe@gmail.com1763Course 0877YES5/31/21
26540Jane Doejanedoe@gmail.com1772Course 0988YES5/31/21
27540Jane Doejanedoe@gmail.com1767Course 1077YES5/31/21
28540Jane Doejanedoe@gmail.com1761Course 1166YES3/30/21
29540Jane Doejanedoe@gmail.com1576Course 121010YES5/31/21
30540Jane Doejanedoe@gmail.com4424Course 13010NO
31540Jane Doejanedoe@gmail.com4368Course 1403NO
32540Jane Doejanedoe@gmail.com1759Course 1599YES5/31/21
33540Jane Doejanedoe@gmail.com2952Course 1633YES4/7/21
34775Jim Doejimdoe@gmail.com4113Course 0122YES7/22/21
35775Jim Doejimdoe@gmail.com1774Course 0222YES7/22/21
36775Jim Doejimdoe@gmail.com3571Course 0388YES7/22/21
37775Jim Doejimdoe@gmail.com1668Course 0477YES7/22/21
38775Jim Doejimdoe@gmail.com1776Course 051414YES7/22/21
39775Jim Doejimdoe@gmail.com1765Course 0677YES7/22/21
40775Jim Doejimdoe@gmail.com1757Course 0766YES7/22/21
41775Jim Doejimdoe@gmail.com1763Course 0877YES7/22/21
42775Jim Doejimdoe@gmail.com1772Course 0988YES7/22/21
43775Jim Doejimdoe@gmail.com1767Course 1077YES7/22/21
44775Jim Doejimdoe@gmail.com1761Course 1166YES7/22/21
45775Jim Doejimdoe@gmail.com1576Course 121010YES7/22/21
46775Jim Doejimdoe@gmail.com4424Course 13010NO
47775Jim Doejimdoe@gmail.com4368Course 1403NO
48775Jim Doejimdoe@gmail.com1759Course 1599YES7/22/21
49775Jim Doejimdoe@gmail.com2952Course 1633YES7/22/21
50548Bruce Willisbrucew@gmail.com4113Course 0122YES5/12/21
51548Bruce Willisbrucew@gmail.com1774Course 0222YES5/12/21
52548Bruce Willisbrucew@gmail.com3571Course 0388YES5/12/21
53548Bruce Willisbrucew@gmail.com1668Course 0477YES5/12/21
54548Bruce Willisbrucew@gmail.com1776Course 051414YES5/12/21
55548Bruce Willisbrucew@gmail.com1765Course 0677YES5/12/21
56548Bruce Willisbrucew@gmail.com1757Course 0766YES5/12/21
57548Bruce Willisbrucew@gmail.com1763Course 0877YES5/12/21
58548Bruce Willisbrucew@gmail.com1772Course 0988YES5/12/21
59548Bruce Willisbrucew@gmail.com1767Course 1077YES5/12/21
60548Bruce Willisbrucew@gmail.com1761Course 1166YES5/12/21
61548Bruce Willisbrucew@gmail.com1576Course 121010YES5/12/21
62548Bruce Willisbrucew@gmail.com4424Course 13010NO
63548Bruce Willisbrucew@gmail.com4368Course 1403NO
64548Bruce Willisbrucew@gmail.com1759Course 1599YES5/12/21
65548Bruce Willisbrucew@gmail.com2952Course 1633YES5/12/21
broadmoor_reports_user_courses
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Repeating a pivot table many times and changing only one column -- are there more efficient approaches?
Replies
0
Views
148
sschaefer
S
E
  • Question
Create different Excel files / sheets based on a Table slicer
Replies
0
Views
192
elisadovio
E
S
  • Question
Multiple Use Radio or Checkmark Buttons
Replies
2
Views
97
ScottTemple
S
B
  • Question
Excel 2021 - Building a Pivot Table on a Filter Function Array
Replies
9
Views
180
BrerRabbit
B
M
  • Question
VLOOKUP help - return value if two partial matches are met (I think??)
Replies
5
Views
282
MountainFog
M

Forum statistics

Threads
1,214,653
Messages
6,120,751
Members
448,989
Latest member
mariah3

Share this page

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