Excel: List all values from column B by matching sibling in column A?

Factual Questions
1

Let’s say I have a spreadsheet:


Name | Allergies
----------------
Jane | Peanuts
John | Carrots
Jane | Milk
Ron  | Eggs
Jane | Snakes
John | Cookies

That goes on for several dozen name-allergy pairs. On a separate worksheet, I want to be able to have a column for Jane that automatically lists all her allergies, another column for John, etc., like:



Jane     | John     | Ron
-----------------------------
Peanuts  | Carrots  | Eggs
Milk     | Cookies  |
Snakes   |          |

Is this possible?

2

You could do a Pivot Table, and then Pivot with both name and allergies as rows in the second sheet. It should end up looking like:


           Jane
                Peanuts
                Milk
                Snakes
           John
                Carrots
                Cookies
           Ron
                Eggs

It’s not exactly the format you want, but it gives the information.

3

Actually, I forgot that this was in a Google Doc spreadsheet, which doesn’t seem to have pivot tables :frowning:

However, I found a waaaaaaaaay cooler function. It actually has SQL-like support!

I got it to work using this amazing formula:



=query(A1:B10,"select B where A contains 'John'",1)

Bam! Does Excel have anything similar, just for future reference?

4

From the MS Office point of view, that’s more of a job for Access.

5

Heh, it would be such a dream to actually work with databases instead of this heterogeneous mess of spreadsheets… but I doubt I could convince the rest of my team :frowning:

6

I used to often create Access databases that import an Excel file and then use queries to make it function like a normal database. That way I can do database stuff on Excel data.

It’s been a while, so i don’t remember if you had to save your results in a table and export it as a Excel file or not. I know I did that sometimes, but I don’t know if I figured out another way.

7

You can export Access queries directly to an Excel spreadsheet.

I also do a lot of that. Import various text/Excel files into Access, crunch it around, export it back out into the format that other people need/are comfortable with, all in code so they just click a button.

8

Wait, so where does the data live? In an Access database? Does that mean that the spreadsheet you pass around is just a table of values (as opposed to a working spreadsheet), or is it actually connected to the live DB somehow?

9

The first spreadsheet is the database’s data input, the second database is its output. Plug S1 into database, produce report as S2.

10

I see. While intriguing, I suppose that’s not exactly elegant or portable. I don’t know anyone who uses or even has Access anymore. Maybe it’s just the circles I run in.