formula & function - Need Help

Question

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&nbsp; &nbsp; &nbsp; &nbsp;ColumnB
Item1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SubItem1
Item2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Subitem3
Item1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Subitem3
Item3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Subitem1
There are about 250 entries.&nbsp;
&nbsp;
I want to create a table with unique entries.
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SubItem1&nbsp; &nbsp;SubItem2&nbsp; Subitem3
Item1&nbsp; &nbsp; &nbsp; YES&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;No&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes
Item2&nbsp; &nbsp; &nbsp; No&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes
Item3&nbsp; &nbsp; &nbsp; Yes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Yes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;No
&nbsp;
Not sure how to use vlookup or Index/Match.
&nbsp;
&nbsp;

willy lau · Answer

Do you consider using PivotTable? Item for Row Label, SubItem for Column Label.  Count of SubItem for Values.  PivotTable can also give you the figure about the grand total.

Forum Discussion

formula & function - Need Help

1 Reply

Resources