lookup Repeated Text and show Data

[DRAGONman]

Hello,
Please Help me to make that Function

There is a Data Like that:

Code	Name	Stocks	Quantity
2551	Ramy	Coca	2000
2551	Ramy	OT	1300
2665	Romio	PNP	1600

i want to make a combo box with names : "Ramy"-"Romio"
when select Name just as "Ramy" show under that name the data with Ramy .. like :

Stocks	Quantity
Coca	2000
OT	1300

Thanks

[brandonlaw]

I have no clue what you are asking, can you be more specific?

[DRAGONman]

i think that i explain well what i want

[Kamal Asharaf]

I think the sprite of the question is that if the look up value is more repeated more than once like "Ramy", how to extract all the corresponding values .
If we will feed the formula like this - Vlookup("Ramy",Target range,Coloumn no,1) ,then the same data which comes first in the order will be displayed each time , now to navigate to find all other data with common lookup cell- Rammy.......
If any one has a clue please provide . It would be my pleasue id u may provide a file as attachment with relevant changes in vlookup formula.

Regards,
Kamal

[DRAGONman]

thanks "Kamal" ..
i need what you explain ..
if you can Help me

[DRAGONman]

HELP ME Please

[Kamal Asharaf]

Actually , i am also looking for the same , so i have explained ur question, let see if anyone could help us ......am also waiting someone to respond.

[Pete_UK]

In a separate column you can have a formula which gives a unique sequential key for each name and entry, like this in E2:

=IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2))

which will give Ramy_1, Ramy_2, Romio_1 etc with that sample data.

Then you can use an INDEX/MATCH formula to extract all the values for Ramy on successive rows.

If you attach a sample file with some representative data in it then I can show you how to set this up.

Hope this helps.

Pete

[navn]

Hi, try with formula below

=INDEX(DataRange, SMALL(IF(OFFSET(DataRange,0,0,ROWS(DataRange),1)=$D$2, ROW(OFFSET(Tools,0,0,ROWS(DataRange),1))-ROW(OFFSET(Tools,0,0,1,1))+1, ROW(OFFSET(DataRange,ROWS(DataRange)-1,0,1,1))+1),Occurance#),index#))

or if you provide Excel sheet with some values, then i will give you VBA solution

[Kamal Asharaf]

am attaching a sample file , with dummy data , kindly guide us .

[DRAGONman]

thank all ..
i attached a Sample Excel Sheet ..

thanks

[Pete_UK]

Hi Dragonman,

see attached file (as you started the thread).

Hope this helps,

Pete

[DRAGONman]

thanks Pete_UK Very Much

i hope you explain it to me

Thanks again

[Pete_UK]

Well, as I said in post #8 that formula when copied down (beyond) your data in Sheet2 gives you a unique key for each record, where it identifies the name as well as a sequential number for each name, like this:

Ramy_1
Ramy_2
Ramy_3
Romio_1
Romio_2
Robert_1
Robert_2
Robert_3
Robert_4

The data does not need to be sorted in any way, although you seem to have sorted by name. The important thing then is to be able to re-generate that sequence together with the name (from your drop-down) in the other sheet, and then you can use a MATCH function to find the row it occurs on and put that in an INDEX function to retrieve corresponding data - you can't use VLOOKUP as the lookup value is not in the first column of the table of data (although, you could put the original formula in column A if you wish to use VLOOKUP).

So, I have used this formula in C6 of Sheet1:

=IFERROR(INDEX(Sheet2!C:C,MATCH($C$2&"_"&ROWS(A$1:A1),Sheet2!$E:$E,0)),"")

and this is copied into D6 to give this:

=IFERROR(INDEX(Sheet2!D:D,MATCH($C$2&"_"&ROWS(B$1:B1),Sheet2!$E:$E,0)),"")

If you look at the first term of the MATCH function, i.e.:

$C$2&"_"&ROWS(A$1:A1)

this is picking up the name from the drop-down in cell C2 and joining on the underscore (_) and a number, which is derived from ROWS(A$1:A1). The column reference here is irrelevant, but this function will initially return 1. When the formula is copied down, however, this part of it will become ROWS(A$1:A2) (returning 2) and then ROWS(A$1:A3) (returning 3), and so on.

So, the first part of the MATCH function will return name_1, name_2, name_3 etc on successive rows. The MATCH function itself will return the row that the particular name_number reference occurs on in Sheet2 column E, but if there is no matching reference (eg name_7) then the ISERROR function will ensure that a blank cell is returned instead of an error.

The INDEX function then returns the corresponding data from Sheet2 column C, whereas the second INDEX function gets the data from column D of Sheet2.

Hope this explains things more clearly.

If you are happy with the solution, please mark the thread as Solved (the FAQ describes how). Also, you can pass on thanks more directly by clicking on the "star" icon in the bottom left corner of any post that has helped you.

Hope this helps.

Pete

[Marcol]

You could also consider an array formula in C6 if you don't want the helper column

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter
Drag across to D6, then Down as required.

In 2007 and above, this could be

Please Login or Register  to view this content.

[Pete_UK]

Go on, then, Marcol - explain that to the OP (!!) <bg>

Pete

[Marcol]

Aye, I'm feart that the dynamic duo might just ask for an explanation ...
Maybe they'll just run away ...

[DRAGONman]

THANKS VERY VERY MUCH Pete_UK & Marcol

can i make that in VBA ?

[DRAGONman]

thanks Pete_UK .. you explain that vey well
can i make that in VBA Form ?

[Pete_UK]

thanks Pete_UK .. you explain that vey well
can i make that in VBA Form ?

Well, you CAN, but I don't think I'm your man for that - I deal mainly with formulae.

Why don't you mark this thread as closed and then post a new thread in the Programming Forum explaining what your problem is (maybe with a link back to this thread)?

Pete