Create multiple lists based on criteria from single column

I have a file that I downloaded as a csv that I am not trying to organize for ease of use.*

I want to create multiple column lists based on the criteria from another column.



For example, I want to change this:

Billing Admin	View
Billing Admin	Edit
Billing Admin	Invoice
Receptionist	View
Receptionist	Edit
Receptionist	Schedule
Receptionist	Edit
Receptionist	Create

To this:

Billing Admin	Receptionist
View	View
Edit	Edit
invoice	Add new
	schedule
	create

I have tried the following:

Transpose

Pivot Table

=IFERROR(INDEX(role_permission!B:B, MATCH(0, COUNTIF(A$1:A3, IF(role_permission!A:A=A$1,role_permission!B:B,A$1)), 0)),"")

Filter formula



None of these has been able to do the job at the scale that I need it done. I know I must be missing something.* I have attached the file for further reference. There are dozens of roles, I do not have time to copy and paste the list of roles over to a new sheet or workbook.*

with Power Query

col1	col2		Billing Admin	Receptionist
Billing Admin	View		View	View
Billing Admin	Edit		Edit	Edit
Billing Admin	Invoice		Invoice	Schedule
Receptionist	View			Create
Receptionist	Edit
Receptionist	Schedule
Receptionist	Edit
Receptionist	Create

Please Login or Register  to view this content.

next time post excel file with representative example before and expected result

I'm guessing there's a better formula than this, but this gives your expected results:

=LET(
rnga,A1:A8,
rngb,B1:B8,
Header,TRANSPOSE(UNIQUE(rnga)),
Data1,FILTER(rngb,rnga=INDEX(Header,,1)),
Data2,FILTER(rngb,rnga=INDEX(Header,,2)),
Data,IFERROR(HSTACK(Data1,Data2),""),
VSTACK(Header,Data))

Please Login or Register  to view this content.

Thank you! I will give it a try. Since it was my first post, it wouldn't let me attach anything...

I am not familiar with Power Query, can you elaborate or link me to a tutorial?

Originally Posted by Gregb11

Data1,FILTER(rngb,rnga=INDEX(Header,,1)),
Data2,FILTER(rngb,rnga=INDEX(Header,,2)),

This is working perfectly! Is there a faster way to specify data sets? There are 465 unique sets.

@John

it should be like this?

Billing Admin	View		Billing Admin	Receptionist	Billing Admin
Billing Admin	Edit		View	View	Edit
Billing Admin	Invoice		Edit	Edit	View
Receptionist	View		Invoice	Schedule
Receptionist	Edit			Edit
Receptionist	Schedule			View
Receptionist	Edit			Edit
Receptionist	View			Edit
Receptionist	Edit			Schedule
Receptionist	Edit
Receptionist	Schedule
Billing Admin	Edit
Billing Admin	View

IMHO it should be sorted (as you did in macro) and distinct (without repetition)
but

Originally Posted by JohnTopley

I'll await OP response.

Another formula

Formula:

Please Login or Register  to view this content.

I am going to input these now
I am still trying to attach the file, it says I cannot until I post a few times...(it won't even let me tag anyone) Seems silly for a help forum, but okay.

Originally Posted by Fluff13

Ignore.
Think I realised what the OP was saying & provided a formula later in the thread

Sorry if I wasn't clear.
My dataset is two columns labeled "role" and "permission"
There are over 400 roles with corresponding positions. I need to take the "role" make it a header and have it fill in the corresponding "permissions" underneath.
I know there are many ways to do this, I am just trying to find the simplest one. I do not know macros or pivot tables; my knowledge is very rudimentary.
Formulas I understand for the most part. So io hope this is the one!

it says I cannot until I post a few times

You should be able to. Read the instructions at the top (in yellow banner) closely. It's not intuitive unfortunately.

Trying again

BEAUTIFUL! This worked immediately. Thank you so much!

Glad to help & thanks for the feedback.