Sort function or macro for the first 4 letters
 

If i have several rows with product data is there a way to get it to search
for hits simular to the first 4 letters ? then of course sumit up to 1 result.

for exampel:
the data sheet:

21 units 200$ candyliqurish
50 units 200$ candyfudge
20 units 300$ candylollypop

the result sheet:

91 units 700$ candy*****

this forum i sthe best !!

thanks to all competent people !

best regards Matz

Suppose your dollars are in column B and the rpoductname in column C, then
use something like this

=SUMPRODUCT(B1:B10,--(LEFT(C1:C10,2)="cand"))

Mangesh




"Matz" wrote in message
...
If i have several rows with product data is there a way to get it to
search
for hits simular to the first 4 letters ? then of course sumit up to 1
result.

for exampel:
the data sheet:

21 units 200$ candyliqurish
50 units 200$ candyfudge
20 units 300$ candylollypop

the result sheet:

91 units 700$ candy*****

this forum i sthe best !!

thanks to all competent people !

best regards Matz

And for the units (which are in columns A) use:

=SUMPRODUCT(A1:A10,--(LEFT(C1:C10,2)="cand"))

Note that the units are plain numbers (21) and not (21 units).

Mangesh



"Mangesh Yadav" wrote in message
...
Suppose your dollars are in column B and the rpoductname in column C, then
use something like this

=SUMPRODUCT(B1:B10,--(LEFT(C1:C10,2)="cand"))

Mangesh




"Matz" wrote in message
...
If i have several rows with product data is there a way to get it to
search
for hits simular to the first 4 letters ? then of course sumit up to 1
result.

for exampel:
the data sheet:

21 units 200$ candyliqurish
50 units 200$ candyfudge
20 units 300$ candylollypop

the result sheet:

91 units 700$ candy*****

this forum i sthe best !!

thanks to all competent people !

best regards Matz

Hi

Ive tryed that but i have to many different products, i would like a macro
that par the first 4 letters without me specify every single one...

thanks for your respone though.

//Matz

"Mangesh Yadav" skrev:

And for the units (which are in columns A) use:

=SUMPRODUCT(A1:A10,--(LEFT(C1:C10,2)="cand"))

Note that the units are plain numbers (21) and not (21 units).

Mangesh



"Mangesh Yadav" wrote in message
...
Suppose your dollars are in column B and the rpoductname in column C, then
use something like this

=SUMPRODUCT(B1:B10,--(LEFT(C1:C10,2)="cand"))

Mangesh




"Matz" wrote in message
...
If i have several rows with product data is there a way to get it to
search
for hits simular to the first 4 letters ? then of course sumit up to 1
result.

for exampel:
the data sheet:

21 units 200$ candyliqurish
50 units 200$ candyfudge
20 units 300$ candylollypop

the result sheet:

91 units 700$ candy*****

this forum i sthe best !!

thanks to all competent people !

best regards Matz

Sorry, I did not understand. Can you give a few examples. Do you mean to say
just see whichever different set of groups having first 4 characters
same...?

Mangesh



"Matz" wrote in message
...
Hi

Ive tryed that but i have to many different products, i would like a macro
that par the first 4 letters without me specify every single one...

thanks for your respone though.

//Matz

"Mangesh Yadav" skrev:

And for the units (which are in columns A) use:

=SUMPRODUCT(A1:A10,--(LEFT(C1:C10,2)="cand"))

Note that the units are plain numbers (21) and not (21 units).

Mangesh



"Mangesh Yadav" wrote in message
...
Suppose your dollars are in column B and the rpoductname in column C,
then
use something like this

=SUMPRODUCT(B1:B10,--(LEFT(C1:C10,2)="cand"))

Mangesh




"Matz" wrote in message
...
If i have several rows with product data is there a way to get it to
search
for hits simular to the first 4 letters ? then of course sumit up to
1
result.

for exampel:
the data sheet:

21 units 200$ candyliqurish
50 units 200$ candyfudge
20 units 300$ candylollypop

the result sheet:

91 units 700$ candy*****

this forum i sthe best !!

thanks to all competent people !

best regards Matz

I would add headers, then use a helper column of cells:

=left(d2,4)
and drag down
(4 or 5 characters????)

Then either sort that data by the helper column and use Data|subototals or
(without sorting) data|pivottable to get my summary.




Matz wrote:

If i have several rows with product data is there a way to get it to search
for hits simular to the first 4 letters ? then of course sumit up to 1 result.

for exampel:
the data sheet:

21 units 200$ candyliqurish
50 units 200$ candyfudge
20 units 300$ candylollypop

the result sheet:

91 units 700$ candy*****

this forum i sthe best !!

thanks to all competent people !

best regards Matz

--

Dave Peterson