database
Posted by rob on April 07, 2001 8:22 PM
I have two worksheets in the same workbook. Both sheets have a list of customers who have bought a different type of product. I would like to compare the lists to determine if one customer from, say worksheet A has ever bought the product from worksheet B. Hopefully I could then create a new list of those customers who appeared in both worksheets. Thanks in advance for your help.
Posted by Dave Hawley on April 07, 2001 10:29 PM
Hi Rob
This would require some serious VBA, or the use of a Pivot Table with "multiple consolidated ranges" or maybe the "Consolidate.." both can be found under Data on the menubar. I also have the address for an add-in that may interest you, but you will have to wait 6 hours before I can get it.
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on April 08, 2001 5:07 AM
Hi Rob
As I understand it, you want to create a customers list of customers common to 2 different lists.
I have no VBA code on offer, just a system of formulas that purports to do the job. If interested in a formula-based solution, please continue reading.
On Sheet1, select all cells containing names or identifiers of customers (excluding the label or column heading) and name the selected range 'namesA' via the Name Box. [The procedure just described creates a named range of fixed size: If desired, it's possible to create a dynamic named range involving the names or identifiers of customers.]
On Sheet2, apply the preceeding procedure to create the named range 'namesB.'
On Sheet3,
in A1 enter: Shortest List (a label)
in A2 enter: =IF(COUNTA(namesA)<=COUNTA(namesB),"namesA","namesB")
in A4 enter: Longest List
in A5 enter: =IF(COUNTA(namesA)>COUNTA(namesB),"namesA","namesB")
in A7 enter: Length
in A8 enter: =COUNTA(INDIRECT(A2))
in A10 enter: Ref Unordered List
in A11 enter: =ADDRESS(ROW(C2),COLUMN(C2))&":"&ADDRESS(A8+1,COLUMN(C2))
in A13 enter: Ref New List
in A14 enter: =ADDRESS(ROW(E2),COLUMN(E2))&":"&ADDRESS(A8+1,COLUMN(E2))
in C1 enter: Unordered List
in C2 enter: =IF(ROW()-1<=$A$8,IF(NOT(ISNA(VLOOKUP(INDIRECT($A$2),INDIRECT($A$5),1,0))),INDIRECT($A$2),0),0) [ Copy down this formula as far as needed/desired, say up to row 101 ]
in E1 enter: New List
in E2 array-enter: =IF(ROW()-ROW(INDIRECT($A$11))+1>ROWS(INDIRECT($A$11))-COUNTIF(INDIRECT($A$11),0),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT($A$11)<>0,ROW(INDIRECT($A$11)),ROW()+ROWS(INDIRECT($A$11)))),ROW()-ROW(INDIRECT($A$14))+1),COLUMN(INDIRECT($A$11))))) [ To array-enter a formula you need to hit CONTROL+SHIFT+ENTER at the same time; Copy down this formula as far as needed/desired, say up to row 101 ]
Note. If you made it up here, you can drop me a line to get the workbook that includes the machinery described above.
Cheers.
Aladin
Posted by K. Cabatic on April 09, 2001 4:53 PM
Here's another way that involves a bit of manipulation but only one formula.
1.Paste the SheetA names to a new sheet
2.Sort
3.Sub-total by name
4.Collapse to level 2
5.Select visible cells only
6.Copy and paste to a new sheet(SheetC) in columnA - this gives the list of names excluding duplicates. (Note: If SheetA does not contain any duplicate names, steps 1 to 5 can be eliminated.)
7.Repeat steps 1 to 6 for SheetB
8.On SheetC (which now has the names from SheetA and SheetB starting at A2), enter in B2 and fill down:-
=COUNTIF($A$2:$A$10000,A2)
9.Auto filter columnB and select "2" - which gives you the names that appear both on SheetA and on SheetB
