Request for formula to refer some letters from Cell

P

PERANISH

COL-A COL-B
00XA10010253 5
00XXXA10012267 1
00XA20045682 2
00XXXA23564457 4
00XAA1000253 1
00XAA200000 3
00XXA106402A01 5

I WANT IF COL-A = X , SUM COL-B
XX , SUM COL-B
XXX , SUM COL-B
PLEASE NOTE, I WANT AFTER THER LETTERS -BETWEEN FIRST 2DIGIT NUMBER(00) AND
EXCEPT -A
EXAMPLE IF 00XXXA200202A01 MEANS - I WANT" XXX "

AWAITNG YOUR HELP PLESE

-PER ANISH
 
G

Gary''s Student

In cell C1 enter:
=LEN(A1)-LEN(SUBSTITUTE(A1,"X","")) and copy down. We see:

00XA10010253 5 1
00XXXA10012267 1 3
00XA20045682 2 1
00XXXA23564457 4 3
00XAA1000253 1 1
00XAA200000 3 1
00XXA106402A01 5 2

Column C "counts" the number of X's in column A. Then, elsewhere:

=SUMPRODUCT(B1:B7,--(C1:C7=1)) sum column B single X's
=SUMPRODUCT(B1:B7,--(C1:C7=2)) sum column B double X's
=SUMPRODUCT(B1:B7,--(C1:C7=3)) sum column B three X's
 
R

Ron Rosenfeld

COL-A COL-B
00XA10010253 5
00XXXA10012267 1
00XA20045682 2
00XXXA23564457 4
00XAA1000253 1
00XAA200000 3
00XXA106402A01 5

I WANT IF COL-A = X , SUM COL-B
XX , SUM COL-B
XXX , SUM COL-B
PLEASE NOTE, I WANT AFTER THER LETTERS -BETWEEN FIRST 2DIGIT NUMBER(00) AND
EXCEPT -A
EXAMPLE IF 00XXXA200202A01 MEANS - I WANT" XXX "

AWAITNG YOUR HELP PLESE

-PER ANISH
Click to expand...

It is not entirely clear to me what your requirements are.

If you only need to count the X's in a string, and if they only occur in one
place in the string, then Gary's students solution will work just fine.

However, if X's can also occur elsewhere in the string, it will fail.

What I see from your description is that you want to count the first instance
of X's that are preceded by two digits and followed by an A.

I would use a UDF, utilizing regular expressions, to do that.

This UDF will return TRUE or FALSE depending on if there is a pattern match.
This way you can vary the pattern to suit.

To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use the UDF, enter a formula of the type:

=ReComp(cell_ref,pattern) into some cell and fill down as required.

One or more of the following patterns, or a variation may be useful:

Two digits followed by a single "X" followed by an "A"

"\d\dXA"

Two digits followed by two "X"'s followed by an "A"

"\d\dXXS"

Two digits followed by one capital letter that is not "A", then followed by an
A:

"\d\d[B-Z]A"

Two digits followed by two capital letters that are not "A", then followed by
an A:

"\d\d[B-Z]{2}A"

Two digits followed by two capital letters that are not "A", then followed by
an A:

"\d\d[B-Z]{3}A"

If the above patterns will only occur at the beginning of a line, then prepend
a "^" to the patterns. eg:

"^\d\dXA"
"^\d\dXXS"
"^\d\d[B-Z]A"
"^\d\d[B-Z]{2}A"

=====================================
Option Explicit
Function ReComp(str As String, sPattern As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Global = False
re.Pattern = sPattern
ReComp = re.Test(str)
End Function
=================================
--ron
 
R

Ron Rosenfeld

COL-A COL-B
00XA10010253 5
00XXXA10012267 1
00XA20045682 2
00XXXA23564457 4
00XAA1000253 1
00XAA200000 3
00XXA106402A01 5

I WANT IF COL-A = X , SUM COL-B
XX , SUM COL-B
XXX , SUM COL-B
PLEASE NOTE, I WANT AFTER THER LETTERS -BETWEEN FIRST 2DIGIT NUMBER(00) AND
EXCEPT -A
EXAMPLE IF 00XXXA200202A01 MEANS - I WANT" XXX "

AWAITNG YOUR HELP PLESE

-PER ANISH
Click to expand...

It is not entirely clear to me what your requirements are.

If you only need to count the X's in a string, and if they only occur in one
place in the string, then Gary's students solution will work just fine.

However, if X's can also occur elsewhere in the string, it will fail.

What I see from your description is that you want to count the first instance
of X's that are preceded by two digits and followed by an A.

I would use a UDF, utilizing regular expressions, to do that.

This UDF will return TRUE or FALSE depending on if there is a pattern match.
This way you can vary the pattern to suit.

To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use the UDF, enter a formula of the type:

=ReComp(cell_ref,pattern) into some cell and fill down as required.

One or more of the following patterns, or a variation may be useful:

Two digits followed by a single "X" followed by an "A"

"\d\dXA"

Two digits followed by two "X"'s followed by an "A"

"\d\dXXS"

Two digits followed by one capital letter that is not "A", then followed by an
A:

"\d\d[B-Z]A"

Two digits followed by two capital letters that are not "A", then followed by
an A:

"\d\d[B-Z]{2}A"

Two digits followed by two capital letters that are not "A", then followed by
an A:

"\d\d[B-Z]{3}A"

If the above patterns will only occur at the beginning of a line, then prepend
a "^" to the patterns. eg:

"^\d\dXA"
"^\d\dXXS"
"^\d\d[B-Z]A"
"^\d\d[B-Z]{2}A"

=====================================
Option Explicit
Function ReComp(str As String, sPattern As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Global = False
re.Pattern = sPattern
ReComp = re.Test(str)
End Function
=================================
--ron
Click to expand...


And I forgot to add that once you have a column showing TRUE or FALSE for the
requisite number of X's, you can SUM Column B based on that using the SUMIF
worksheet function.

For example, if your data is in A2:A8, and the "ReComp" formula in C2:C8, then
a formula such as:

=SUMIF(C2:C8,TRUE,B2:B8)

will add up all the values in B2:B8 that have the requisite pattern.

You could also do this entirely within the UDF. And that can be done easily
once you provide more specific information.
--ron
 
P

PERANISH

Thanks for your reply & working is good & result will be vary.Once again, i
am giving more example to enable to help me.

COL-A COL-B COL-C
1 00ABA362101201 S 5
2 00ABA362101201 S 2
3 00ABA362101201 F 3
4 00CCA423100602 S 2
5 00CCA423100602 S 4

6 00ABA362101201 F 3
7 00DXBA4226012E01 F 1.5
8 00DXBA4226012E01 S 2

I WANT FORMULA IF COL-B = AB & COL-C=S, SUM COL-D
EXAMPLE FROM COL-B1,B2,B3 - "AB" TO BE SELECT & COL-B4,B5 -"CC" TO BE
SELECT, COL-B7,B8 - "DXB" TO BE SELECT

I WANT RESULT IF "AB" IN COL-B & "S" IN COL-C, SUM- D WITH RESPECT.

ONCE AGAIN SORRY FOR THE INCONVEIENCE.
AWAITNG YOUR HELP PLEASE.
-PERANISH
 
P

PERANISH

Thanks for your reply & working is good & result will be vary.But by data
letters will be variable. I am giving more example to enable to help me.

COL-A COL-B COL-C
1 00ABA362101201 S 5
2 00ABA362101201 S 2
3 00ABA362101201 F 3
4 00CCA423100602 S 2
5 00CCA423100602 S 4

6 00ABA362101201 F 3
7 00DXBA4226012E01 F 1.5
8 00DXBA4226012E01 S 2

I WANT FORMULA IF COL-B = AB & COL-C=S, SUM COL-D
EXAMPLE FROM COL-B1,B2,B3 - "AB" TO BE SELECT & COL-B4,B5 -"CC" TO BE
SELECT, COL-B7,B8 - "DXB" TO BE SELECT

I WANT RESULT IF "AB" IN COL-B & "S" IN COL-C, SUM- D WITH RESPECT.

ONCE AGAIN SORRY FOR THE INCONVEIENCE.
AWAITNG YOUR HELP PLEASE.
-PERANISH
 
P

PERANISH

Thanks for your reply & YES, what you told that my datas having variable
letters.Once again, i am giving more example to enable to help me.

COL-A COL-B COL-C
1 00ABA362101201 S 5
2 00ABA362101201 S 2
3 00ABA362101201 F 3
4 00CCA423100602 S 2
5 00CCA423100602 S 4

6 00ABA362101201 F 3
7 00DXBA4226012E01 F 1.5
8 00DXBA4226012E01 S 2

I WANT FORMULA IF COL-B = AB & COL-C=S, SUM COL-D
EXAMPLE FROM COL-B1,B2,B3 - "AB" TO BE SELECT & COL-B4,B5 -"CC" TO BE
SELECT, COL-B7,B8 - "DXB" TO BE SELECT

I WANT RESULT IF "AB" IN COL-B & "S" IN COL-C, SUM- D WITH RESPECT.

ONCE AGAIN SORRY FOR THE INCONVEIENCE.
AWAITNG YOUR HELP PLEASE.

-peranish


Ron Rosenfeld said:
COL-A COL-B
00XA10010253 5
00XXXA10012267 1
00XA20045682 2
00XXXA23564457 4
00XAA1000253 1
00XAA200000 3
00XXA106402A01 5

I WANT IF COL-A = X , SUM COL-B
XX , SUM COL-B
XXX , SUM COL-B
PLEASE NOTE, I WANT AFTER THER LETTERS -BETWEEN FIRST 2DIGIT NUMBER(00) AND
EXCEPT -A
EXAMPLE IF 00XXXA200202A01 MEANS - I WANT" XXX "

AWAITNG YOUR HELP PLESE

-PER ANISH
Click to expand...

It is not entirely clear to me what your requirements are.

If you only need to count the X's in a string, and if they only occur in one
place in the string, then Gary's students solution will work just fine.

However, if X's can also occur elsewhere in the string, it will fail.

What I see from your description is that you want to count the first instance
of X's that are preceded by two digits and followed by an A.

I would use a UDF, utilizing regular expressions, to do that.

This UDF will return TRUE or FALSE depending on if there is a pattern match.
This way you can vary the pattern to suit.

To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use the UDF, enter a formula of the type:

=ReComp(cell_ref,pattern) into some cell and fill down as required.

One or more of the following patterns, or a variation may be useful:

Two digits followed by a single "X" followed by an "A"

"\d\dXA"

Two digits followed by two "X"'s followed by an "A"

"\d\dXXS"

Two digits followed by one capital letter that is not "A", then followed by an
A:

"\d\d[B-Z]A"

Two digits followed by two capital letters that are not "A", then followed by
an A:

"\d\d[B-Z]{2}A"

Two digits followed by two capital letters that are not "A", then followed by
an A:

"\d\d[B-Z]{3}A"

If the above patterns will only occur at the beginning of a line, then prepend
a "^" to the patterns. eg:

"^\d\dXA"
"^\d\dXXS"
"^\d\d[B-Z]A"
"^\d\d[B-Z]{2}A"

=====================================
Option Explicit
Function ReComp(str As String, sPattern As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Global = False
re.Pattern = sPattern
ReComp = re.Test(str)
End Function
=================================
--ron
Click to expand...


And I forgot to add that once you have a column showing TRUE or FALSE for the
requisite number of X's, you can SUM Column B based on that using the SUMIF
worksheet function.

For example, if your data is in A2:A8, and the "ReComp" formula in C2:C8, then
a formula such as:

=SUMIF(C2:C8,TRUE,B2:B8)

will add up all the values in B2:B8 that have the requisite pattern.

You could also do this entirely within the UDF. And that can be done easily
once you provide more specific information.
--ron
Click to expand...
 
R

Ron Rosenfeld

Thanks for your reply & YES, what you told that my datas having variable
letters.Once again, i am giving more example to enable to help me.

COL-A COL-B COL-C
1 00ABA362101201 S 5
2 00ABA362101201 S 2
3 00ABA362101201 F 3
4 00CCA423100602 S 2
5 00CCA423100602 S 4

6 00ABA362101201 F 3
7 00DXBA4226012E01 F 1.5
8 00DXBA4226012E01 S 2

I WANT FORMULA IF COL-B = AB & COL-C=S, SUM COL-D
EXAMPLE FROM COL-B1,B2,B3 - "AB" TO BE SELECT & COL-B4,B5 -"CC" TO BE
SELECT, COL-B7,B8 - "DXB" TO BE SELECT

I WANT RESULT IF "AB" IN COL-B & "S" IN COL-C, SUM- D WITH RESPECT.

ONCE AGAIN SORRY FOR THE INCONVEIENCE.
AWAITNG YOUR HELP PLEASE.

-peranish
Click to expand...

It's still not clear to me exactly what you want.

It would be better if you could put your specifications into words, instead of
trying to give examples which seem to have errors and be incomplete.

For example, in your description above you write "if COL-B = AB & COL-C=S", but
your COL-B seems to have either S or F; and your COL-C has numbers.

You give no data for a COL-D, but you write "SUM-D WITH RESPECT". I don't know
what that means.

You also don't show how you want the results displayed.

Here's one idea which assumes that the codes which you want to extract begin at
position 3 and end with the last "A".

Given your data as above, but in Cols A, B and C:

COL-A COL-B COL-C
00ABA362101201 S 5
00ABA362101201 S 2
00ABA362101201 F 3
00CCA423100602 S 2
00CCA423100602 S 4
00ABA362101201 F 3
00DXBA4226012E01 F 1.5
00DXBA4226012E01 S 2

Enter this UDF in the manner I described in my previous post :

==========================
Option Explicit
Function ReSub(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^\d\d([A-Z]+)A.*"
ReSub = re.Replace(str, "$1")
End Function
============================

Then enter:

D1: EXTRACT
D2: =ReSub(A2)

Select D2 and fill down as far as required.

Then select some cell in this table and create a Pivot Table (should be on the
Data Menu)

For the Pivot table,
COL-B for the Column Labels
Select only the "S"
EXTRACT for the Row Labels
COL-C to the Data or Values area
This should do the SUM; if it does not, edit the DATA or VALUE
field settings.

I would remove the Grand Totals from the Rows and format attractively.

This can give you a table that looks like:

Sum of COL-C
S
AB 7
CC 6
DXB 2
Grand Total 15

Or, if you format and make your selections slightly differently, you could get
a Pivot Table showing:

Sum of COL-C
F S Grand Total
AB 6 7 13
CC 6 6
DXB 1.5 2 3.5
Grand Total 7.5 15 22.5

Again, you need to be specific as to what you want to do.

--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

To criteria with Reference from Cell 3
REQUEST FORMULA FOR COUNTING & SUM IN DUPLICATES 8
Time punch data from rows to columns 3
Write formula for simple copy and paste to another cell 1
MACRO TO DELET ROWS DEPENDING ON CELL VALUES 6
refer to a cell using a formula? 1
refer to cell from result??? 3
Array functions and cell formatting problem 2

Top