Formula, vba, or logic to remove certain occurrences

horees

Board Regular
Joined
Sep 25, 2012
Messages
63
All,
I'm looking for a Formula, vba, or logic to remove certain occurrences of a pattern.

To make life easier, please see the table below.

I want A formula or logic to calculate the column D as shown below. The idea is to get as minimum of records that show x, zz, y, ww without repition.

i.e. to be able to identify the records of (x,zz) and (y,ww).

too hard???

Thank you

ABCD
6/25XZZ1
6/25XWW0
6/25YZZ0
6/25YWW1

<TBODY>
</TBODY>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
too hard ???

only because we can't see a before and after....is this what you want as a result........ or this is what you have to work with.
How did you get the 0 / 1 's in column "D" ?
 
Upvote 0
another scenrio that could happen. I want the same formula to be able to distinguish between the first 2 records (a,a) & (b,b) and the last 2 records (to be able to hide/delete them).

Even harder?
6/16
A
A
6/16
B
B
6/16
A
B
6/16
B
A

<TBODY>
</TBODY>
 
Last edited:
Upvote 0
only because we can't see a before and after....is this what you want as a result........ or this is what you have to work with.
How did you get the 0 / 1 's in column "D" ?

Column D is the result that I want to get. 0 / 1 is what I need to get.
 
Upvote 0
Maybe this



Excel 2003
ABCD
1ABCD
2Jun-25XZZ1
3Jun-25XWW0
4Jun-25YZZ0
5Jun-25YWW1
Sheet2
Cell Formulas
RangeFormula
D2=IF(AND(B2="X",C2="ZZ"),1,IF(AND(B2="Y",C2="WW"),1,0))
D3=IF(AND(B3="X",C3="ZZ"),1,IF(AND(B3="Y",C3="WW"),1,0))
D4=IF(AND(B4="X",C4="ZZ"),1,IF(AND(B4="Y",C4="WW"),1,0))
D5=IF(AND(B5="X",C5="ZZ"),1,IF(AND(B5="Y",C5="WW"),1,0))
 
Upvote 0
Maybe this


Excel 2003
A
B
C
D
1
A
B
C
D
2
Jun-25
X
ZZ
1
3
Jun-25
X
WW
4
Jun-25
Y
ZZ
5
Jun-25
Y
WW
1

<TBODY>
</TBODY>
Sheet2

Worksheet Formulas
Cell
Formula
D2
=IF(AND(B2="X",C2="ZZ"),1,IF(AND(B2="Y",C2="WW"),1,0))
D3
=IF(AND(B3="X",C3="ZZ"),1,IF(AND(B3="Y",C3="WW"),1,0))
D4
=IF(AND(B4="X",C4="ZZ"),1,IF(AND(B4="Y",C4="WW"),1,0))
D5
=IF(AND(B5="X",C5="ZZ"),1,IF(AND(B5="Y",C5="WW"),1,0))

<TBODY>
</TBODY>

<TBODY>
</TBODY>


This gets the same results I asked for, but the problem is that it need it to be dynamic regardless the values in B & C. The values in B & C can be anything not specifically X, Y, ZZ, or WW. but the pattern will be the same...Also, this is only small part of bigger data with different values.
Any recommendations?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top