CODEWITHSUNDEEP
excelexcel-formula
Wilson
Wilson

Reputation: 231

Lookup and append based on value

I am trying to accomplish the following:

Table 1: Data inputs with names with fruits

Apple  | John | Peter | Chia <br>
Orange | Peter | Lim    <br>
Pear   | Peter | Tan    <br>
Banana | Tan | James    <br>
Kiwi   | Wee        <br>

The code will read the above table and generates the following table:

John | Apple <br>
Peter | Apple, Orange, Pear <br>
Chia | Apple <br>
Lim | Orange <br>
Tan | Pear, Banana <br>
Jame | Banana <br>
Wee | Kiwi

This is to identify some form of resource conflict using excel. Is there any way to do it without vba? If there isn't, is there anyway to do it with loops?

Thanks!

Upvotes: 0

Views: 44

Answers (1)

Kazschuri
Kazschuri

Reputation: 620

as Tim suggested, it is possible with a simple pivot table (in blue on the right):

resulting Pivottable and Data for it

The only problem that I see right now, is getting your Input Data into the format that pivot tables need ... the area to the left of the pivot table.

But maybe your source can supply the data in single rows instead?

Kaz

Upvotes: 3

Related Questions