# Excel: List all values from column B by matching sibling in column A? **URL:** \ **Category:** Factual Questions\ **Created:** [April 12, 2012, 4:21pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573 "2012-04-12T16:21:23Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [April 12, 2012, 4:21pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/1 "2012-04-12T16:21:23Z")
Let’s say I have a spreadsheet: ```auto 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: ```auto Jane | John | Ron ----------------------------- Peanuts | Carrots | Eggs Milk | Cookies | Snakes | | ``` Is this possible? ---
**Author:** ![Meltdown](https://avatars.discourse-cdn.com/v4/letter/m/45deac/32.png) [@Meltdown](https://boards.straightdope.com/u/Meltdown)\ **Post date:** [April 12, 2012, 4:32pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/2 "2012-04-12T16:32:12Z")
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: ```auto Jane Peanuts Milk Snakes John Carrots Cookies Ron Eggs ``` It’s not exactly the format you want, but it gives the information. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [April 12, 2012, 4:41pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/3 "2012-04-12T16:41:42Z")
Actually, I forgot that this was in a Google Doc spreadsheet, which doesn’t seem to have pivot tables ☹ However, I found a waaaaaaaaay cooler function. It actually has [SQL-like support](https://support.google.com/docs/bin/answer.py?hl=en&answer=1388882)! I got it to work using this amazing formula: ```auto =query(A1:B10,"select B where A contains 'John'",1) ``` Bam! Does Excel have anything similar, just for future reference? ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [April 12, 2012, 4:49pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/4 "2012-04-12T16:49:14Z")
From the MS Office point of view, that’s more of a job for Access. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [April 12, 2012, 5:39pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/5 "2012-04-12T17:39:11Z")
> [@Darth\_Panda](#): > > From the MS Office point of view, that’s more of a job for Access. 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 ☹ ---
**Author:** ![BigT](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bigt/32/12044_2.png) [@BigT](https://boards.straightdope.com/u/BigT)\ **Post date:** [April 13, 2012, 2:25am UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/6 "2012-04-13T02:25:41Z")
> [@Reply](#): > > 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 ☹ 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. ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [April 13, 2012, 1:18pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/7 "2012-04-13T13:18:21Z")
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. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [April 13, 2012, 2:48pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/8 "2012-04-13T14:48:45Z")
> [@redtail23](#): > > 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. 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? ---
**Author:** ![Nava](https://avatars.discourse-cdn.com/v4/letter/n/da6949/32.png) [@Nava](https://boards.straightdope.com/u/Nava)\ **Post date:** [April 13, 2012, 2:52pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/9 "2012-04-13T14:52:45Z")
The first spreadsheet is the database’s data input, the second database is its output. Plug S1 into database, produce report as S2. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [April 13, 2012, 3:04pm UTC](https://boards.straightdope.com/t/excel-list-all-values-from-column-b-by-matching-sibling-in-column-a/618573/10 "2012-04-13T15:04:43Z")
> [@Nava](#): > > The first spreadsheet is the database’s data input, the second database is its output. Plug S1 into database, produce report as S2. 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.