| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
matching multiple rows
i have a spreadsheet with 2 columns, cust id and author.
how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
I would insert a cheater column to the left so you have
A B C 1 Cheater CustID Author 2 3 and enter this formula in cell A2 OR(C2="Dickens",C2="Twain") copy down as far as necessary. then use autofilter (Tools/Filter/Autofilter) and filter the TRUE values in column A. "ronb" wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
The expression evaluates as true if custid purchased from either author, not
both authors "JMB" wrote: I would insert a cheater column to the left so you have A B C 1 Cheater CustID Author 2 3 and enter this formula in cell A2 OR(C2="Dickens",C2="Twain") copy down as far as necessary. then use autofilter (Tools/Filter/Autofilter) and filter the TRUE values in column A. "ronb" wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
Sorry. Change OR to AND.
"ronb" wrote: The expression evaluates as true if custid purchased from either author, not both authors "JMB" wrote: I would insert a cheater column to the left so you have A B C 1 Cheater CustID Author 2 3 and enter this formula in cell A2 OR(C2="Dickens",C2="Twain") copy down as far as necessary. then use autofilter (Tools/Filter/Autofilter) and filter the TRUE values in column A. "ronb" wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
This still doesn't evaluate correctly.
wouldn't the AND operator in this case check that the same cell contains two values? My spreadsheet is like this: CustID Author 123 Dickens 145 Dickens 123 Twain 145 Shakespeare I would like to return 2 rows for custID 123 "JMB" wrote: Sorry. Change OR to AND. "ronb" wrote: The expression evaluates as true if custid purchased from either author, not both authors "JMB" wrote: I would insert a cheater column to the left so you have A B C 1 Cheater CustID Author 2 3 and enter this formula in cell A2 OR(C2="Dickens",C2="Twain") copy down as far as necessary. then use autofilter (Tools/Filter/Autofilter) and filter the TRUE values in column A. "ronb" wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
Maybe just apply Data|filter|autofilter to the range.
filter to show Dickens or Twain If you need more, you could try Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ronb wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
My apologies - I'm a little slow tonight.
I would use the advanced filter to generate a listing of unique customer id's. Data/Filters/Advanced Filter Select Copy to another location List Range - select the column containing customer id , A1:A whatever Copy To - Select an empty column - I picked cell D1 Check unique records only. I got something like this D E 1 CustID 2 123 3 456 Then in cell E2 I entered =(SUMPRODUCT(($A$2:$A$8=D2)*($B$2:$B$8="Dickens")) 0)+(SUMPRODUCT(($A$2:$A$8=D2)*($B$2:$B$8="Twain") )0)=2 This will return True/False. Cut and paste this table to another sheet if you like and use Autofilter to find the True values in the second column. "ronb" wrote: This still doesn't evaluate correctly. wouldn't the AND operator in this case check that the same cell contains two values? My spreadsheet is like this: CustID Author 123 Dickens 145 Dickens 123 Twain 145 Shakespeare I would like to return 2 rows for custID 123 "JMB" wrote: Sorry. Change OR to AND. "ronb" wrote: The expression evaluates as true if custid purchased from either author, not both authors "JMB" wrote: I would insert a cheater column to the left so you have A B C 1 Cheater CustID Author 2 3 and enter this formula in cell A2 OR(C2="Dickens",C2="Twain") copy down as far as necessary. then use autofilter (Tools/Filter/Autofilter) and filter the TRUE values in column A. "ronb" wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
those are the lines along which I was thinking, but some additional work
would be needed to identify the customer id's w/both Dickens and Twain. filtering w/this criteria will result in customer id's that purchased either author. "Dave Peterson" wrote: Maybe just apply Data|filter|autofilter to the range. filter to show Dickens or Twain If you need more, you could try Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ronb wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
Ahhhh. The OP wants just the customers that have bought both author--not either
author. I think I'd create a pivottable (with authors in the column field if they fit) and then filter by those columns to show 0 for both columns (dickens and twain). JMB wrote: those are the lines along which I was thinking, but some additional work would be needed to identify the customer id's w/both Dickens and Twain. filtering w/this criteria will result in customer id's that purchased either author. "Dave Peterson" wrote: Maybe just apply Data|filter|autofilter to the range. filter to show Dickens or Twain If you need more, you could try Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ronb wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
a much faster solution. I've never used pivottables much.
"Dave Peterson" wrote: Ahhhh. The OP wants just the customers that have bought both author--not either author. I think I'd create a pivottable (with authors in the column field if they fit) and then filter by those columns to show 0 for both columns (dickens and twain). JMB wrote: those are the lines along which I was thinking, but some additional work would be needed to identify the customer id's w/both Dickens and Twain. filtering w/this criteria will result in customer id's that purchased either author. "Dave Peterson" wrote: Maybe just apply Data|filter|autofilter to the range. filter to show Dickens or Twain If you need more, you could try Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ronb wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
Depends on the amount of data, too.
If you've sorted and used data|subtotal to get subtotals, then you'll be surprised how easy your life will become after an hour playing with pivottables. There's lots of links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx JMB wrote: a much faster solution. I've never used pivottables much. "Dave Peterson" wrote: Ahhhh. The OP wants just the customers that have bought both author--not either author. I think I'd create a pivottable (with authors in the column field if they fit) and then filter by those columns to show 0 for both columns (dickens and twain). JMB wrote: those are the lines along which I was thinking, but some additional work would be needed to identify the customer id's w/both Dickens and Twain. filtering w/this criteria will result in customer id's that purchased either author. "Dave Peterson" wrote: Maybe just apply Data|filter|autofilter to the range. filter to show Dickens or Twain If you need more, you could try Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ronb wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
thanks Dave!
"Dave Peterson" wrote: Depends on the amount of data, too. If you've sorted and used data|subtotal to get subtotals, then you'll be surprised how easy your life will become after an hour playing with pivottables. There's lots of links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx JMB wrote: a much faster solution. I've never used pivottables much. "Dave Peterson" wrote: Ahhhh. The OP wants just the customers that have bought both author--not either author. I think I'd create a pivottable (with authors in the column field if they fit) and then filter by those columns to show 0 for both columns (dickens and twain). JMB wrote: those are the lines along which I was thinking, but some additional work would be needed to identify the customer id's w/both Dickens and Twain. filtering w/this criteria will result in customer id's that purchased either author. "Dave Peterson" wrote: Maybe just apply Data|filter|autofilter to the range. filter to show Dickens or Twain If you need more, you could try Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ronb wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
Thanks Dave,
I never considered pivot tables. Like JMB, i haven't really worked with them. They always kind of scared me. I guess i may as well bite the bullet and give it a try. thanks again "Dave Peterson" wrote: Depends on the amount of data, too. If you've sorted and used data|subtotal to get subtotals, then you'll be surprised how easy your life will become after an hour playing with pivottables. There's lots of links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx JMB wrote: a much faster solution. I've never used pivottables much. "Dave Peterson" wrote: Ahhhh. The OP wants just the customers that have bought both author--not either author. I think I'd create a pivottable (with authors in the column field if they fit) and then filter by those columns to show 0 for both columns (dickens and twain). JMB wrote: those are the lines along which I was thinking, but some additional work would be needed to identify the customer id's w/both Dickens and Twain. filtering w/this criteria will result in customer id's that purchased either author. "Dave Peterson" wrote: Maybe just apply Data|filter|autofilter to the range. filter to show Dickens or Twain If you need more, you could try Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ronb wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
Thanks, JMB.
I really appreciate the effort you put into this. "JMB" wrote: thanks Dave! "Dave Peterson" wrote: Depends on the amount of data, too. If you've sorted and used data|subtotal to get subtotals, then you'll be surprised how easy your life will become after an hour playing with pivottables. There's lots of links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx JMB wrote: a much faster solution. I've never used pivottables much. "Dave Peterson" wrote: Ahhhh. The OP wants just the customers that have bought both author--not either author. I think I'd create a pivottable (with authors in the column field if they fit) and then filter by those columns to show 0 for both columns (dickens and twain). JMB wrote: those are the lines along which I was thinking, but some additional work would be needed to identify the customer id's w/both Dickens and Twain. filtering w/this criteria will result in customer id's that purchased either author. "Dave Peterson" wrote: Maybe just apply Data|filter|autofilter to the range. filter to show Dickens or Twain If you need more, you could try Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ronb wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching multiple rows
you're welcome. good luck w/your project.
"ronb" wrote: Thanks, JMB. I really appreciate the effort you put into this. "JMB" wrote: thanks Dave! "Dave Peterson" wrote: Depends on the amount of data, too. If you've sorted and used data|subtotal to get subtotals, then you'll be surprised how easy your life will become after an hour playing with pivottables. There's lots of links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx JMB wrote: a much faster solution. I've never used pivottables much. "Dave Peterson" wrote: Ahhhh. The OP wants just the customers that have bought both author--not either author. I think I'd create a pivottable (with authors in the column field if they fit) and then filter by those columns to show 0 for both columns (dickens and twain). JMB wrote: those are the lines along which I was thinking, but some additional work would be needed to identify the customer id's w/both Dickens and Twain. filtering w/this criteria will result in customer id's that purchased either author. "Dave Peterson" wrote: Maybe just apply Data|filter|autofilter to the range. filter to show Dickens or Twain If you need more, you could try Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm ronb wrote: i have a spreadsheet with 2 columns, cust id and author. how can i find all cust id's that have purchased from 2 specific authors such as Dickens and Twain -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sum all rows with multiple matching criteria | Excel Worksheet Functions | |||
| Matching Rows | Excel Discussion (Misc queries) | |||
| VLookup - Multiple Rows with Exact Matching Column 1 | Excel Discussion (Misc queries) | |||
| Find rows matching Max value | Excel Discussion (Misc queries) | |||
| return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Linear Mode
