Formula Problem
Posted by Mo on April 27, 2001 1:34 PM
Hello, I have 2 columns. In one column I have names, in the other column I have goods corresponding to the names. Each name has several goods to its name, on different positions e.g
A B
1 John radio
2 Richard Stereo
3 John Toy Gun
4 Matt printer
5 Richard Scanner
6 John monitor
What I want is a form of Vlookup, or anything for that matter that will enable me to type a persons name in a different cell, and the corresponding goods to appear. Ideally they will all be in the same cell, separated by a comma. However I dont mind if the goods appear in different cells. I will concatenate them myself.
Going back to the previous example, I should be able to type John and radio, toy gun, and monitor to appear.
Any input will be appreciated.
Thankyou
Posted by Dave Hawley on April 27, 2001 9:05 PM
Hi Mo
Sounds like an ideal job for a Pivot Table, I have sent an example.
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on April 28, 2001 11:58 PM
Mo,
At least 3 choices that I see.
1) Pivot Tables (This has been already suggested.)
2) An array-formula.
Assuming that your example data occupy the range A1:B6,
in D1 enter: a name of interest (say, John)
in E1 enter: =IF((A1:A6)=D1,B1:B6,""),
select, while still in E1, E1:E6 (a range having an equal number of rows as your data), go to the formula bar, then hit CONTROL+SHIFT+ENTER at the same time.
You'll get the following one column table as result:
{"monitor";"";"radio";"";"";"Toy Gun"}
I'll leave it to you how to concatenate the relevant items in the result into a single value in a cell.
3) A system of formulas as the one that follows:
Insert 2 columns before the first column of your data.
In A1 enter: =COUNTIF($C$1:C1,C1) [ copy down this to A6 ]
In B1 enter: =C1&"-"&A1 [ copy down this to B6 ]
Select B1:B6 and name this range NAMES via the Name Box.
In E1 enter: =COUNTIF(C1:$C$6,C1) [ copy down this to E6 ]
Select B1:E6 and named this range DATA via the Name Box.
At this point, the data with additions look like this:
{1,"John-1","John","monitor",3;1,"Matt-1","Matt","printer",1;2,"John-2","John","radio",2;1,"Richard-1","Richard","Scanner",2;2,"Richard-2","Richard","Stereo",1;3,"John-3","John","Toy Gun",1}
In G1 enter: a name of interest (say, John) [ if you want, enter more names from G1 on in G. ]
In H1 enter: =IF(LEN($G1)>0,IF(ISNUMBER(MATCH($G1&-(COLUMN()-7),NAMES,0)),VLOOKUP($G1&-(COLUMN()-7),DATA,3,0),""),"")
Copy the formula across (say, up to M1) and then down.
The result that you get is:
{"john","monitor","radio","Toy Gun","","","";"matt","printer","","","","","";"richard","Scanner","Stereo","","","","";0,"","","","","","";0,"","","","","","";0,"","","","","",""}
0's are also empty/blank cells.
Aladin
PS. If you'd like a copy of the workbook, just drop me a line.
==========================================
