Text formula
 

Hi
I am trying to find out how should I proceed with the folowing problem.
I have a group category of data in sheet 1 as folows:

Sheet1
COLUMN A:A
Group category
PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

In sheet2 I have different charges that I need to sumarize, but the problem
is, that descriptions can contain more information and I can't just simply
use a vlookup formula. I need to apply a formula that will recognize group
category in the text

SHEET2

Description amount formula
PCM AAAA AAAA AAAAA 111 =pcm
AAA CMT AAAAA 223 =cmt
AAAA GROUP NETWORK 2131 =group network
ttttttt 654 =other

Thanks in advance
Eva

Text formula
 

With this in Sheet1 A2:A5

PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

With this in Sheet2 A2:A5

PCM AAAA AAAA AAAAA
AAA CMT AAAAA
AAAA GROUP NETWORK
ttttttt

Enter this formula in Sheet2 C2 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(ISNA(MATCH(1,--ISNUMBER(SEARCH(Sheet1!A$2:A$5,A2)),0)),"other",IN DEX(Sheet1!A$2:A$5,MATCH(1,--ISNUMBER(SEARCH(Sheet1!A$2:A$5,A2)),0)))

Copy down as needed.

Biff

"Eva" wrote in message
...
Hi
I am trying to find out how should I proceed with the folowing problem.
I have a group category of data in sheet 1 as folows:

Sheet1
COLUMN A:A
Group category
PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

In sheet2 I have different charges that I need to sumarize, but the
problem
is, that descriptions can contain more information and I can't just simply
use a vlookup formula. I need to apply a formula that will recognize group
category in the text

SHEET2

Description amount formula
PCM AAAA AAAA AAAAA 111 =pcm
AAA CMT AAAAA 223 =cmt
AAAA GROUP NETWORK 2131 =group network
ttttttt 654 =other

Thanks in advance
Eva

Text formula
 

You can use wildcards

=VLOOKUP("*PCM*",Lookup_Range,2,0)

or if you total for all PCM

=SUMIF(A2:A300,"*PCM*",B2:B300)


as an example


--
Regards,

Peo Sjoblom



"Eva" wrote in message
...
Hi
I am trying to find out how should I proceed with the folowing problem.
I have a group category of data in sheet 1 as folows:

Sheet1
COLUMN A:A
Group category
PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

In sheet2 I have different charges that I need to sumarize, but the
problem
is, that descriptions can contain more information and I can't just simply
use a vlookup formula. I need to apply a formula that will recognize group
category in the text

SHEET2

Description amount formula
PCM AAAA AAAA AAAAA 111 =pcm
AAA CMT AAAAA 223 =cmt
AAAA GROUP NETWORK 2131 =group network
ttttttt 654 =other

Thanks in advance
Eva

Text formula
 

T. Valko you are genious!!! it's work! Thanks
Eva

"T. Valko" wrote:

With this in Sheet1 A2:A5

PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

With this in Sheet2 A2:A5

PCM AAAA AAAA AAAAA
AAA CMT AAAAA
AAAA GROUP NETWORK
ttttttt

Enter this formula in Sheet2 C2 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(ISNA(MATCH(1,--ISNUMBER(SEARCH(Sheet1!A$2:A$5,A2)),0)),"other",IN DEX(Sheet1!A$2:A$5,MATCH(1,--ISNUMBER(SEARCH(Sheet1!A$2:A$5,A2)),0)))

Copy down as needed.

Biff

"Eva" wrote in message
...
Hi
I am trying to find out how should I proceed with the folowing problem.
I have a group category of data in sheet 1 as folows:

Sheet1
COLUMN A:A
Group category
PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

In sheet2 I have different charges that I need to sumarize, but the
problem
is, that descriptions can contain more information and I can't just simply
use a vlookup formula. I need to apply a formula that will recognize group
category in the text

SHEET2

Description amount formula
PCM AAAA AAAA AAAAA 111 =pcm
AAA CMT AAAAA 223 =cmt
AAAA GROUP NETWORK 2131 =group network
ttttttt 654 =other

Thanks in advance
Eva

Text formula
 

Hi Peo
Thanks for your help. You formula is good, but I would have to each time
input the value I am looking for, but still, I like the formula and I am
going to use it for the other purpose I am working on right now.
Thanks
Eva

"Peo Sjoblom" wrote:

You can use wildcards

=VLOOKUP("*PCM*",Lookup_Range,2,0)

or if you total for all PCM

=SUMIF(A2:A300,"*PCM*",B2:B300)


as an example


--
Regards,

Peo Sjoblom



"Eva" wrote in message
...
Hi
I am trying to find out how should I proceed with the folowing problem.
I have a group category of data in sheet 1 as folows:

Sheet1
COLUMN A:A
Group category
PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

In sheet2 I have different charges that I need to sumarize, but the
problem
is, that descriptions can contain more information and I can't just simply
use a vlookup formula. I need to apply a formula that will recognize group
category in the text

SHEET2

Description amount formula
PCM AAAA AAAA AAAAA 111 =pcm
AAA CMT AAAAA 223 =cmt
AAAA GROUP NETWORK 2131 =group network
ttttttt 654 =other

Thanks in advance
Eva

Text formula
 

You can still put the value to look for in another cell:

=VLOOKUP("*"&a1&"*",Lookup_Range,2,0)

or if you total for all PCM

=SUMIF(A2:A300,"*"&a1&"*",B2:B300)

Eva wrote:

Hi Peo
Thanks for your help. You formula is good, but I would have to each time
input the value I am looking for, but still, I like the formula and I am
going to use it for the other purpose I am working on right now.
Thanks
Eva

"Peo Sjoblom" wrote:

You can use wildcards

=VLOOKUP("*PCM*",Lookup_Range,2,0)

or if you total for all PCM

=SUMIF(A2:A300,"*PCM*",B2:B300)


as an example


--
Regards,

Peo Sjoblom



"Eva" wrote in message
...
Hi
I am trying to find out how should I proceed with the folowing problem.
I have a group category of data in sheet 1 as folows:

Sheet1
COLUMN A:A
Group category
PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

In sheet2 I have different charges that I need to sumarize, but the
problem
is, that descriptions can contain more information and I can't just simply
use a vlookup formula. I need to apply a formula that will recognize group
category in the text

SHEET2

Description amount formula
PCM AAAA AAAA AAAAA 111 =pcm
AAA CMT AAAAA 223 =cmt
AAAA GROUP NETWORK 2131 =group network
ttttttt 654 =other

Thanks in advance
Eva





--

Dave Peterson

Text formula
 

Hi
Actual you are right, I didn't think about this option
thanks
Eva

"Dave Peterson" wrote:

You can still put the value to look for in another cell:

=VLOOKUP("*"&a1&"*",Lookup_Range,2,0)

or if you total for all PCM

=SUMIF(A2:A300,"*"&a1&"*",B2:B300)

Eva wrote:

Hi Peo
Thanks for your help. You formula is good, but I would have to each time
input the value I am looking for, but still, I like the formula and I am
going to use it for the other purpose I am working on right now.
Thanks
Eva

"Peo Sjoblom" wrote:

You can use wildcards

=VLOOKUP("*PCM*",Lookup_Range,2,0)

or if you total for all PCM

=SUMIF(A2:A300,"*PCM*",B2:B300)


as an example


--
Regards,

Peo Sjoblom



"Eva" wrote in message
...
Hi
I am trying to find out how should I proceed with the folowing problem.
I have a group category of data in sheet 1 as folows:

Sheet1
COLUMN A:A
Group category
PCM
GLOBAL PURCHASING
CMT
GROUP NETWORK

In sheet2 I have different charges that I need to sumarize, but the
problem
is, that descriptions can contain more information and I can't just simply
use a vlookup formula. I need to apply a formula that will recognize group
category in the text

SHEET2

Description amount formula
PCM AAAA AAAA AAAAA 111 =pcm
AAA CMT AAAAA 223 =cmt
AAAA GROUP NETWORK 2131 =group network
ttttttt 654 =other

Thanks in advance
Eva





--

Dave Peterson