how ??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pierre - 21 May 2008 22:07 GMT

HELLO GUYS....HELP
given:

A B
1 axe red 10
2 axe blue 20
3 axe green 30
4 axe pink 40
5 table 50

what should i do if i would want to type in cell C1 (as a search string)
the word "AXE" in order to get ALL results about it ??? vlookup ?? or
index(...,match(...)?? or aother ways ???

Reply to this Message

mikebres - 21 May 2008 22:22 GMT

That would depend on the results you want. Did you want everything with the
word Axe in it to be summed? Or did you want a list of everything with the
word Axe?

> HELLO GUYS....HELP
> given:
[quoted text clipped - 9 lines]
> the word "AXE" in order to get ALL results about it ??? vlookup ?? or
> index(...,match(...)?? or aother ways ???

Reply to this Message

pierre - 21 May 2008 22:26 GMT

can you PLEASE give me both ???? ....i would be VERy thankfull

> > A B
> > 1 axe red 10
[quoted text clipped - 6 lines]
> > the word "AXE" in order to get ALL results about it ??? vlookup ?? or
> > index(...,match(...)?? or aother ways ???

Reply to this Message

mikebres - 21 May 2008 22:41 GMT

Okay, to get a list the easiest way is to use the Data>Filter>AutoFilter.
Put your cursor in the block of data and select AutoFilter from the menu.
Then click on the drop down arrow for the column with the name in it and
choose either Begin or Contains from the left drop down box in the dialog.
In the right hand box type in 'axe' without the quotes, click OK and you have
your list.

For the second you will need to create a summary formula using sumproduct,
sumif, array formulas etc. Example using sumproduct:
A1 Name B1 Value
A2 axe red B2 10
A3 axe blue B3 20
A4 Chair B4 5

D2 axe E2=sumproduct(--(left(Name),3=D2),Value)
D3 Chair E3=sumproduct(--(left(Name),3=D2),Value)

Which will give you the sum of 30 in E2 and 5 in E3. Name is a named range
for the labels in column A and Value is a named range for column B.

Mike

> can you PLEASE give me both ???? ....i would be VERy thankfull
>
[quoted text clipped - 8 lines]
> > > the word "AXE" in order to get ALL results about it ??? vlookup ?? or
> > > index(...,match(...)?? or aother ways ???

Reply to this Message

PCLIVE - 21 May 2008 22:28 GMT

What results and how do you want to return it?
Do you want a count of the items that include the string "axe" in column A?
=COUNTIF(A1:A5,"*axe*")

Do you want a sum of Column B where column A includes the string "axe"?
=SUMIF(A1:A5,"*axe*",B1:B5)

or are you looking to filter out everything that does not include the string
"axe"? If so, then you'd probably have to use a custom filter.

HTH,
Paul

> HELLO GUYS....HELP
> given:
[quoted text clipped - 9 lines]
> the word "AXE" in order to get ALL results about it ??? vlookup ?? or
> index(...,match(...)?? or aother ways ???

Reply to this Message

T. Valko - 21 May 2008 22:32 GMT

Try this:

C1 = axe

Enter this formula in D1:

=COUNTIF(A1:A5,"*"&C1&"*")

Enter this array formula** in D2:

=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$5,SMALL(IF(ISNUMBER(SEARCH(C1,A$1:A$5)),ROW(A$1:A$5)-MIN(ROW(A$1:A$5))+1),ROWS(D$2:D2))),"")

Copy down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Signature

Biff
Microsoft Excel MVP

> HELLO GUYS....HELP
> given:
[quoted text clipped - 9 lines]
> the word "AXE" in order to get ALL results about it ??? vlookup ?? or
> index(...,match(...)?? or aother ways ???

Reply to this Message

pierre - 21 May 2008 22:52 GMT

MR t valko.....thanks it worked BUT........

what does this formula mean ?? : =COUNTIF(A1:A5,"*"&C1&"*")

is there a way to use the formula you gave me
=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$5,SMALL(IF(ISNUMBER(SEARCH(C1,A$1:A$5)),ROW(A$1:A$5)-MIN(ROW(A$1:A$5))+1),ROWS(D$2:D2))),"")

WITHOUT FINDING FIRST : =COUNTIF(A1:A5,"*"&C1&"*")

in another way i mean , can i start from
INDEX(A$1:A$5,SMALL(IF(ISNUMBER(SEARCH(C1,A$1:A$5)),ROW(A$1:A$5)-MIN(ROW(A$1:A$5))+1),ROWS(D$2:D2))),"")

THANKS A LOT

> Try this:
>
[quoted text clipped - 26 lines]
> > the word "AXE" in order to get ALL results about it ??? vlookup ?? or
> > index(...,match(...)?? or aother ways ???

Reply to this Message

T. Valko - 21 May 2008 23:02 GMT

>what does this formula mean ?? :
>=COUNTIF(A1:A5,"*"&C1&"*")

That formula returns the count of items in column A that contain the word
held in C1.

This is used as an error trap in the other formula.

> is there a way to use the formula you gave me :
> WITHOUT FINDING FIRST :
>=COUNTIF(A1:A5,"*"&C1&"*")

Yeah, you could do without that but try it and see what happens. Once all
the relevant data has been extracted from column A you'll get #NUM! errors.

Signature

Biff
Microsoft Excel MVP

> MR t valko.....thanks it worked BUT........
>
[quoted text clipped - 41 lines]
>> > the word "AXE" in order to get ALL results about it ??? vlookup ?? or
>> > index(...,match(...)?? or aother ways ???

Reply to this Message

pierre - 23 May 2008 05:33 GMT

to MR t VALKO....please read accurately....thanks

given :

A
1 pen a
2 pen b
3 pen c
4 table a
5 table B

my goal here is to type in search string B1 the world "pen" in order to get
all results related to " pen " like (pen a , pen b , pen c)
so i am doing the following :

in C1 : =COUNTIF(A1:A5;"*"&C1&"*")

in D1 :
{=IF(ROWS(D$2:D2)<=D$1;INDEX(A$1:A$5;SMALL(IF(ISNUMBER(SEARCH(C1;A$1:A$5));ROW(A$1:A$5));ROWS(D$2:D2)));"")}....AND I DRAG DOWN

NOW MY QUESTION HERE :

WHAT SHOULD I DO IN ORDER TO TYPE THE WORD "PEN" IN SEARCH STRING CELL C1
AND THE WORD "TABLE" IN SEARCH STRING CELL C2 IN ORDER TO GET ALL THEIR
CORESPONDENT RESULTS ????

THANK YOU SIR

Reply to this Message

T. Valko - 23 May 2008 06:40 GMT

Change the formula in D1 to:

=SUMPRODUCT(COUNTIF(A1:A5,"*"&C1:C2&"*"))

Change the array formula** in D2 to:

=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$5,SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE(C$1:C$2),A$1:A$5)),ROW(A$1:A$5)),ROWS(D$2:D2))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Note that the formula will extract the data as it is listed. If your real
data is not sorted (unlike your sample data) it won't extract all pens first
then all tables next.

Signature

Biff
Microsoft Excel MVP

> to MR t VALKO....please read accurately....thanks
>
[quoted text clipped - 26 lines]
>
> THANK YOU SIR

Reply to this Message

pierre - 23 May 2008 16:52 GMT

THANK YOU VERY MUCH......
i really appreciate it......i am very gratefull to you....
thanks to you i have learned something new today .
many thanks to you :)

Reply to this Message

T. Valko - 23 May 2008 17:35 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> THANK YOU VERY MUCH......
> i really appreciate it......i am very gratefull to you....
> thanks to you i have learned something new today .
> many thanks to you :)

Reply to this Message