Detecting all the incidences of a value

**scienceonstage** · 10-25-2010, 02:12 PM

Hello,

I've been working with pivot tables to try and visualize my excel sheet in a different way, but I can't get the output to look like I want it. The source spreadsheet is in 5 columns, each containing a large number of unique values. Although all the values in a column are unique, many values are in more than one column. I would like to reorganize the data so that each row contains one unique value and, in the columns to the right, a listing of the original column headings under which that value was found. Does this make sense?

For example, say I had a list of 10 individuals who went out and bought fruit. I have a table with several kinds of fruit across the column headings, and various combinations of 5 out of the 10 individuals listed in each column, corresponding to the people who bought that type of fruit. What I want is a table with all 10 people listed down a single column, and beside them, the types of fruit that each bought.

It's hard to type out anything here that would look right in terms of formatting, so hopefully my description makes sense. Apologies and many thanks in advance - I'm new to this site :-)

**NBVC** · 10-25-2010, 02:13 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**scienceonstage** · 10-25-2010, 02:35 PM

Thank you for the quick response, and again, my apologies for not appropriately illustrating the issue. I've attached an example workbook with what I tried to explain above, which conceptually mirrors my actual workbook. Thanks again for any help!

**NBVC** · 10-25-2010, 03:11 PM

If you have your names listed in A13:A20 per your sample, then in B13 try:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($A$1:$D$1,SMALL(IF($A$3:$D$7=$A13,COLUMN($A$3:$D$7)-COLUMN($A$3)+1),COLUMNS($A$1:A$1)))))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down and across the columns.

adjust ranges to suit and re-confirm with CSE keys and copy across the matrix.