Search,Index,Match help

[GerryK]

Hi,
My situation is that I'd like to enter a text string in C2
and find out how many task codes from column B appear
associated with the entered string at C2.

This formula searches for my input text string, then
totals from column J.
SUMPRODUCT(--(ISNUMBER(SEARCH(C2,R3:R9999))),J3:J9999)

This formula (array entered and copied down) gives me an
incremental list of used task codes (there are only 8
codes possible) from the entire column B. List produced
identifies that a code was used only, and as the records
increase so does the list until all 8 are displayed.
INDEX($B$3:$B$9999,MATCH(0,COUNTIF
($I$8:$I8,$B$3:$B$9999),0))

Is there someway to merge the search concept with a unique
listing returned from B?

TIA and thanks to FK for help so far!

 

[Frank Kabel]

Hi
why not place your arry formula in C2, copy it down and then use the
SUMPRODUCT formula.

BUT you may also consider using a pivot table for this (would do this
all in one step)

 

[GerryK]

Thanks Frank,
I have to enter a text string in C2 therefore I'd like a
similar formula to be placed along side my list of codes
that tells me what is associated with the data. The latter
formula (first post)works great as I can see the progress
of code use but I'd like to see the same thing to compare
for the text string entered into C2
I'm working on your idea but also cannot get my 'blended'
formula to work.

=INDEX($B$3:$B$9999,MATCH(0,COUNTIF('Sheet1'!
$J$8:$J8,SUMPRODUCT(--(ISNUMBER(SEARCH(C2,R3:R9999)))))))

Is this off base?

 

[Frank Kabel]

Hi
not really sure what you're trying to do. Maybe post some example rows
of your data (plain text please)

 

[GerryK]

Hi,
In column B any one of these activity numbers is possible
(Example B3=10, B4=17 etc.)
10,11,12,13,14,15,16,17

In column R is text location information. Examples are:
R3=behind shed
R4=north side of house
R5=adjacent to 2nd utility pole
R6=beside tool shed

Final look might be:
B3=10, R3=behind shed
B4=17, R4=north side of house
B5=10, R5=adjacent to 2nd utility pole
B6=13, R6=beside tool shed

Other columns are not relevant.

In C2, if I enter shed, I'd like for a formula (copied
down eight cells) to return, somewhere on my worksheet,
let's say at X3 and X4, that 10 and 13 were used to date
for shed.
X7 on would stay blank.

Any ideas?

 

[Frank Kabel]

Hi
enter the following in X3:
=IF(ISNUMBER(SEARCH($C$2,R3)),"used: " & B3,"")
and copy down

 

[GerryK]

Thank you!!!

-----Original Message-----
Hi
enter the following in X3:
=IF(ISNUMBER(SEARCH($C$2,R3)),"used: " & B3,"")
and copy down


--
Regards
Frank Kabel
Frankfurt, Germany



.