Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
I want to do a function similar to a vlookup. I want to input a key into a cell and have the relevant names listed. The problem I have is the vlookup only shows one of the multiple names. Is there a way to list all of the names?
Input Key: ____ Key Name 1414 BRODRENE DAHL A/S 1880 MAN FERROSTAAL AG 2356 HEITON BUCKLEY LIMITED 2356 HEITON BUCKLEY LIMITED1 2356 HEITON BUCKLEY LIMITED2 2356 HEITON BUCKLEY LIMITED3 3867 STAVANGER RORHANDEL A/S 4367 CLEANAWAY LTD 4618 ALUKONIGSTAHL GMBH 4618 ALUKONIGSTAHL GMBH1 4979 MARMON/KEYSTONE ANBUMA N.V. Ideal Output Input Key 2356 HEITON BUCKLEY LIMITED //all brought up by a formula HEITON BUCKLEY LIMITED1 HEITON BUCKLEY LIMITED2 HEITON BUCKLEY LIMITED3 I would appreciate any assistance. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
You might take a look at Data Filter AutoFilter.........it does something
similar to what you describe. Vaya con Dios, Chuck, CABGx3 "GarToms" wrote: I want to do a function similar to a vlookup. I want to input a key into a cell and have the relevant names listed. The problem I have is the vlookup only shows one of the multiple names. Is there a way to list all of the names? Input Key: ____ Key Name 1414 BRODRENE DAHL A/S 1880 MAN FERROSTAAL AG 2356 HEITON BUCKLEY LIMITED 2356 HEITON BUCKLEY LIMITED1 2356 HEITON BUCKLEY LIMITED2 2356 HEITON BUCKLEY LIMITED3 3867 STAVANGER RORHANDEL A/S 4367 CLEANAWAY LTD 4618 ALUKONIGSTAHL GMBH 4618 ALUKONIGSTAHL GMBH1 4979 MARMON/KEYSTONE ANBUMA N.V. Ideal Output Input Key 2356 HEITON BUCKLEY LIMITED //all brought up by a formula HEITON BUCKLEY LIMITED1 HEITON BUCKLEY LIMITED2 HEITON BUCKLEY LIMITED3 I would appreciate any assistance. Thanks. -- GarToms |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
http://office.microsoft.com/en-us/as...orrespond ing This is what you are after, but I have spent many frustrating hours trying to get it to work, I am still desperate for this result myself. Copy & Paste the whole of the address into your address bar. If you get this to work, please can you email it to me Thanks Gavin -- Gavin1969 ------------------------------------------------------------------------ Gavin1969's Profile: http://www.excelforum.com/member.php...o&userid=30551 View this thread: http://www.excelforum.com/showthread...hreadid=502001 |
#4
|
|||
|
|||
I have found this formula that does a similar function to what I require however I am unable to edit it to A1:C1000. Does anyone know how to amend this to work?
=INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Quote:
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
Without passing judgement on whether this is the best way to meet your end
objective (vs. using a pivottable or simple auto-filter)... The formula referenced in the article below works fine. Since the result set can be an array up to the same size as the list, you will need to copy the formula into the same number of rows as the list (i.e. if you have 500 rows in your list, the formula should reside in 500 rows otherwise you may truncate your result). Place the formula in the first row of where you want your resultant set (remembering to use shift+ctrl+enter since it is an array formula) and then autofill the formula into the remaining rows for the result set. This will ensure that the Row(1:1) is incremented to Row(2:2) etc. on subsequent rows. Replace $A$1:$B$7 with the data range; replace $A$1:$A$7 with the range that you are testing; replace $A$10 with the reference to the cell that has the value you are testing for. =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) Good luck! John Top of Page "Gavin1969" wrote: http://office.microsoft.com/en-us/as...orrespond ing This is what you are after, but I have spent many frustrating hours trying to get it to work, I am still desperate for this result myself. Copy & Paste the whole of the address into your address bar. If you get this to work, please can you email it to me Thanks Gavin -- Gavin1969 ------------------------------------------------------------------------ Gavin1969's Profile: http://www.excelforum.com/member.php...o&userid=30551 View this thread: http://www.excelforum.com/showthread...hreadid=502001 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
Without passing judgement on whether this is the best way to meet your end
objective (vs. using a pivottable or simple auto-filter)... I use these types of formulas every day. This particular formula can be shortened a little and also made a little more efficient. The big difference between using these types of formulas versus pivot tables and filters is that the formula method is dynamic! Biff "John M." wrote in message ... Without passing judgement on whether this is the best way to meet your end objective (vs. using a pivottable or simple auto-filter)... The formula referenced in the article below works fine. Since the result set can be an array up to the same size as the list, you will need to copy the formula into the same number of rows as the list (i.e. if you have 500 rows in your list, the formula should reside in 500 rows otherwise you may truncate your result). Place the formula in the first row of where you want your resultant set (remembering to use shift+ctrl+enter since it is an array formula) and then autofill the formula into the remaining rows for the result set. This will ensure that the Row(1:1) is incremented to Row(2:2) etc. on subsequent rows. Replace $A$1:$B$7 with the data range; replace $A$1:$A$7 with the range that you are testing; replace $A$10 with the reference to the cell that has the value you are testing for. =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) Good luck! John Top of Page "Gavin1969" wrote: http://office.microsoft.com/en-us/as...orrespond ing This is what you are after, but I have spent many frustrating hours trying to get it to work, I am still desperate for this result myself. Copy & Paste the whole of the address into your address bar. If you get this to work, please can you email it to me Thanks Gavin -- Gavin1969 ------------------------------------------------------------------------ Gavin1969's Profile: http://www.excelforum.com/member.php...o&userid=30551 View this thread: http://www.excelforum.com/showthread...hreadid=502001 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
What are trying to do?
Biff "Gavin1969" wrote in message ... http://office.microsoft.com/en-us/as...orrespond ing This is what you are after, but I have spent many frustrating hours trying to get it to work, I am still desperate for this result myself. Copy & Paste the whole of the address into your address bar. If you get this to work, please can you email it to me Thanks Gavin -- Gavin1969 ------------------------------------------------------------------------ Gavin1969's Profile: http://www.excelforum.com/member.php...o&userid=30551 View this thread: http://www.excelforum.com/showthread...hreadid=502001 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
Bookmark
"GarToms" wrote in message ... I want to do a function similar to a vlookup. I want to input a key into a cell and have the relevant names listed. The problem I have is the vlookup only shows one of the multiple names. Is there a way to list all of the names? Input Key: ____ Key Name 1414 BRODRENE DAHL A/S 1880 MAN FERROSTAAL AG 2356 HEITON BUCKLEY LIMITED 2356 HEITON BUCKLEY LIMITED1 2356 HEITON BUCKLEY LIMITED2 2356 HEITON BUCKLEY LIMITED3 3867 STAVANGER RORHANDEL A/S 4367 CLEANAWAY LTD 4618 ALUKONIGSTAHL GMBH 4618 ALUKONIGSTAHL GMBH1 4979 MARMON/KEYSTONE ANBUMA N.V. Ideal Output Input Key 2356 HEITON BUCKLEY LIMITED //all brought up by a formula HEITON BUCKLEY LIMITED1 HEITON BUCKLEY LIMITED2 HEITON BUCKLEY LIMITED3 I would appreciate any assistance. Thanks. -- GarToms |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
Take a look at this sample file:
http://s19.yousendit.com/d.aspx?id=1...03CJM84T9Z306Y Biff "Gavin1969" wrote in message ... http://office.microsoft.com/en-us/as...orrespond ing This is what you are after, but I have spent many frustrating hours trying to get it to work, I am still desperate for this result myself. Copy & Paste the whole of the address into your address bar. If you get this to work, please can you email it to me Thanks Gavin -- Gavin1969 ------------------------------------------------------------------------ Gavin1969's Profile: http://www.excelforum.com/member.php...o&userid=30551 View this thread: http://www.excelforum.com/showthread...hreadid=502001 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
Take a look at this sample file:
http://s19.yousendit.com/d.aspx?id=1...03CJM84T9Z306Y Biff "GarToms" wrote in message ... I have found this formula that does a similar function to what I require however I am unable to edit it to A1:C1000. Does anyone know how to amend this to work? =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) GarToms Wrote: I want to do a function similar to a vlookup. I want to input a key into a cell and have the relevant names listed. The problem I have is the vlookup only shows one of the multiple names. Is there a way to list all of the names? Input Key: ____ Key Name 1414 BRODRENE DAHL A/S 1880 MAN FERROSTAAL AG 2356 HEITON BUCKLEY LIMITED 2356 HEITON BUCKLEY LIMITED1 2356 HEITON BUCKLEY LIMITED2 2356 HEITON BUCKLEY LIMITED3 3867 STAVANGER RORHANDEL A/S 4367 CLEANAWAY LTD 4618 ALUKONIGSTAHL GMBH 4618 ALUKONIGSTAHL GMBH1 4979 MARMON/KEYSTONE ANBUMA N.V. Ideal Output Input Key 2356 HEITON BUCKLEY LIMITED //all brought up by a formula HEITON BUCKLEY LIMITED1 HEITON BUCKLEY LIMITED2 HEITON BUCKLEY LIMITED3 I would appreciate any assistance. Thanks. -- GarToms |
#12
|
|||
|
|||
Biff and all,
I wonder if you could assist me further with something using the formula you suggested. Currently a table of data is transfered to a sheet using (INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$2 0=$C$2,ROW($1:$20)),ROW(1:1)) The rows search the source data in sheet 1 by a key (column A) and display a name (column B), a value (column C) and a digit in column D decides under where the value (column C) will go. The column C value needs to go under column E, F, G, H, I, and to make it more complecated these column headings are formulas and change when the data is updated. The table transfers all the data but i cannot make it sort the value in column C to be in the correct column in my table. Anyone have any ideas? I was thinking there may be an if statement or something i could use. I would be very great full for any assistance with this. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
Hi!
Currently a table of data is transfered to a sheet using (INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$2 0=$C$2,ROW($1:$20)),ROW(1:1)) As written that formula will not work properly. You'll either get errors or possibly incorrect results. The size of the array that is indexed: sheet1!$C$2:$C$20 Must be the same size as: ROW($1:$20) sheet1!$C$2:$C$20 = 19 ROW($1:$20) = 20 The "least" confusing way to make sure these arrays are the same size is to use the same sized range reference in the ROW function as you do the INDEX and then subtract the offset: ROW(C$2:C$20)-ROW(C$2)+1 The purpose of the expression: ROW(C$2:C$20)-ROW(C$2)+1 is to return an array that is the same size as the indexed array So: sheet1!$C$2:$C$20 = 19 (1:19) ROW(C$2:C$20)-ROW(C$2)+1 = 19 (1:19) Ok, now, as far as your latest question goes, I'm not following you!!!! I don't understand what you're trying to do! Biff "GarToms" wrote in message ... Biff and all, I wonder if you could assist me further with something using the formula you suggested. Currently a table of data is transfered to a sheet using (INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$2 0=$C$2,ROW($1:$20)),ROW(1:1)) The rows search the source data in sheet 1 by a key (column A) and display a name (column B), a value (column C) and a digit in column D decides under where the value (column C) will go. The column C value needs to go under column E, F, G, H, I, and to make it more complecated these column headings are formulas and change when the data is updated. The table transfers all the data but i cannot make it sort the value in column C to be in the correct column in my table. Anyone have any ideas? I was thinking there may be an if statement or something i could use. I would be very great full for any assistance with this. -- GarToms |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want Vlookup to list multiple items with the same key?
Is there any way to solve this with a pivot table? -- Dark_Templar ------------------------------------------------------------------------ Dark_Templar's Profile: http://www.excelforum.com/member.php...o&userid=33279 View this thread: http://www.excelforum.com/showthread...hreadid=502001 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why won't vlookup work in a long list | Excel Worksheet Functions | |||
how do I use vlookup for multiple occurrences of the same value | Excel Worksheet Functions | |||
Items in a List | Excel Discussion (Misc queries) | |||
Transfer Items to a list with no duplicates | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |