**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have 2 columns of data. Column A with a list of items (say computer SN) and Column B with a list of Sub items (accessories). An Item (column A) can have multiple sub items (column B) and vice versa.
ColumnA ColumnB
Item1 SubItem1
Item2 Subitem3
Item1 Subitem3
Item3 Subitem1
There are about 250 entries.
I want to create a table with unique entries.
SubItem1 SubItem2 Subitem3
Item1 YES No Yes
Item2 No Yes Yes
Item3 Yes Yes No
Not sure how to use vlookup or Index/Match.

Solved by C. C. in 37 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
24/01/2018 - 12:52

Hello

User
24/01/2018 - 12:53

ok

Excelchat Expert
24/01/2018 - 12:53

can you give an example to better understand the requirement

Excelchat Expert
24/01/2018 - 12:54

how do we connect the 2 column's ..

User
24/01/2018 - 12:54

ok.. give me a min

Excelchat Expert
24/01/2018 - 12:54

sure

User
24/01/2018 - 12:57

Column A is has several products

User
24/01/2018 - 12:57

Col B has accessories that go into these product

Excelchat Expert
24/01/2018 - 12:57

ok

User
24/01/2018 - 12:58

A product can have multiple accessories.

User
24/01/2018 - 12:58

and a accessory can be used in multiple product

Excelchat Expert
24/01/2018 - 12:58

ok.. how do we know which accessories belong to which product?

Excelchat Expert
24/01/2018 - 12:58

is there another table ?

User
24/01/2018 - 12:58

they are aligned.

User
24/01/2018 - 12:59

as in product in A1 uses B2 as an accessory

User
24/01/2018 - 12:59

A2

User
24/01/2018 - 12:59

not B2

User
24/01/2018 - 12:59

A1=B1

User
24/01/2018 - 12:59

A2=B2

Excelchat Expert
24/01/2018 - 01:00

so where there is NA in column A it me that means all of them belong to previous

Excelchat Expert
24/01/2018 - 01:00

right

User
24/01/2018 - 01:00

N/A product code not yet assigned

Excelchat Expert
24/01/2018 - 01:00

ok...

User
24/01/2018 - 01:00

and same for blank

User
24/01/2018 - 01:00

we can leave out the blank for now

Excelchat Expert
24/01/2018 - 01:01

sure..

Excelchat Expert
24/01/2018 - 01:01

what do you want now?

User
24/01/2018 - 01:01

give me 1 min

User
24/01/2018 - 01:03

A Table with "YES" or "NO"

Excelchat Expert
24/01/2018 - 01:04

ok..

Excelchat Expert
24/01/2018 - 01:04

so like a matrix.. will all product and all accesories

User
24/01/2018 - 01:04

yes

Excelchat Expert
24/01/2018 - 01:04

yes if the conbination is there and no if it isnt?

User
24/01/2018 - 01:04

exactly

Excelchat Expert
24/01/2018 - 01:04

got it.. give me a min to work on it

User
24/01/2018 - 01:11

let me know if you need additional info

Excelchat Expert
24/01/2018 - 01:11

No I'm working on the solution.. few more min

User
24/01/2018 - 01:11

ok

User
24/01/2018 - 01:25

can you email me the solution.

Excelchat Expert
24/01/2018 - 01:25

No its against the policies to use external means of communication..I'll share an excel here

User
24/01/2018 - 01:25

ok

Excelchat Expert
24/01/2018 - 01:25

1 min.. just verifying the solution

Excelchat Expert
24/01/2018 - 01:26

[Uploaded an Excel file]

Excelchat Expert
24/01/2018 - 01:27

This is an array based matrix solution.. Do let me know if this looks fine

User
24/01/2018 - 01:27

checking

Excelchat Expert
24/01/2018 - 01:27

I have verified from my end..

User
24/01/2018 - 01:29

yes. works fine

Excelchat Expert
24/01/2018 - 01:29

Please click on end session and leave you feedback..

User
24/01/2018 - 01:29

thank you.

Excelchat Expert
24/01/2018 - 01:29

:)

Excelchat Expert
24/01/2018 - 01:29

Have a nice day

User
24/01/2018 - 01:30

will do

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*