how ??

[pierre]

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 ???

 

[mikebres]

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?

 

[pierre]

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

 

[PCLIVE]

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

 

[T. Valko]

Try this:

C1 = axe

Enter this formula in D1:

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

Enter this array formula** in D2:

=IF(ROWS(D$22)<=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$22))),"")

Copy down until you get blanks.

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

 

[mikebres]

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

 

[pierre]

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$22)<=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$22))),"")

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$22))),"")

THANKS A LOT

 

[T. Valko]

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.

 

[pierre]

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$22)<=D$1;INDEX(A$1:A$5;SMALL(IF(ISNUMBER(SEARCH(C1;A$1:A$5));ROW(A$1:A$5));ROWS(D$22)));"")}....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

 

[T. Valko]

Change the formula in D1 to:

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

Change the array formula** in D2 to:

=IF(ROWS(D$22)<=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$22))),"")

** 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.

 

[pierre]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!