| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel:Get concatenated text to be recognised as formula not text? | Excel Discussion (Misc queries) | |||
| Copy formula so destination displays formula as text | Excel Discussion (Misc queries) | |||
| Formula to count text and alert me if a text appears more than twi | Excel Discussion (Misc queries) | |||
| formula is displayed as literal text instead of formula result | Excel Discussion (Misc queries) | |||
| match cell text with text in formula | Excel Worksheet Functions | |||
Hybrid Mode
