sort equal groups of data

  • Thread starter Thread starter Luís Miguel Ramires Vieira Reis RAM
  • Start date Start date
L

Luís Miguel Ramires Vieira Reis RAM

hi,

i need a help in this, plz

i got this list,

id like to check the common fields (in 2 and 3 columns) like this,
p.ex
(so later, i could delete them, and keep and analize the other groups
of data)

LG-0060-0703 MT 6
LG-0060-0703 CX 6
LG-0060-0703 MT 1


LG-0060-1003 MT 6
LG-0060-1003 CX 6
LG-0060-1003 MT 1


LG-0060-1203 MT 6
LG-0060-1203 CX 6
LG-0060-1203 MT 1

LG-0060-2003 MT 6
LG-0060-2003 CX 6
LG-0060-2003 MT 1
LG-0060-3003 MT 6
LG-0060-3003 CX 6
LG-0060-3003 MT 1
LG-0060-4003 MT 6
LG-0060-4003 CX 6
LG-0060-4003 MT 1
LG-0100018 UN 1


i appreciate your help


thanks
 
Not certain what you mean by checking the common fields, because your example
just reads p.ex
But maybe all you need is a filter on colums 2 and 3, to do this, select
Columns 2 and 3 and from the main menu click on Data -> Filter -> AutoFilter
You will see a dropdown arrow appear on the columns from there select the
data you want to filter on and do your deleting.
 
Not certain what you mean by checking the common fields, because your example
just reads p.ex
But maybe all you need is a filter on colums 2 and 3, to do this, select
Columns 2 and 3 and from the main menu click on Data -> Filter -> AutoFilter
You will see a dropdown arrow appear on the columns from there select the
data you want to filter on and do your deleting.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

:













- Mostrar texto citado -
Click to expand...

thanks but...

imagine i just want to see this kind of group of data

MT 6
CX 6
MT 1


i can filter 1 row at a time,
but can i filter a pattern of data?

my goal is to find all the data that match this criteria:

MT 6
CX 6
MT 1

(so later i can delete)
 
What you can do is add another column and in that column set an IF statement
then filter on it. Something like this:
=IF(OR(B1="MT",B1="CX",C1=6,C1=1),"Match Pattern","No Match")
In my formula I am assuming the LG-0060-0703 data is in column A, and the MT
data is in column B and the integer is in column C. My formula therefore is
in D1
Then I set up an autofilter as I mentioned in my previous post and Select
"Match Pattern" as my filtering Criteria.
If you need the pattern to be restricted to match all 3 criterias rather
than only one, like in my formula, then change the OR for AND.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.
 
And Data->Filter->Advanced Filter isn't doing this for you?

In a data set like

fld1 fld2 fld3
LG-0060-0703 MT 6
LG-0060-0703 CX 6
LG-0060-0703 MT 1
LG-0060-1003 MT 6
LG-0060-1003 CX 6
LG-0060-1003 MT 1
LG-0060-1203 MT 6
LG-0060-1203 CX 6
LG-0060-1203 MT 1
LG-0060-2003 MT 6
LG-0060-2003 CX 6
LG-0060-2003 MT 1
LG-0060-3003 MT 6
LG-0060-3003 CX 6
LG-0060-3003 MT 1
LG-0060-4003 MT 6
LG-0060-4003 CX 6
LG-0060-4003 MT 1
LG-0100018 UN 1

where the Advanced Filter Criteria is (in a separate set of cells on
the worksheet):

fld1 fld2 fld3
MT 6
CX 6
MT 1

It will show all records EXCEPT that last row in the dataset. Or am I
missing something here?
 
What you can do is add another column and in that column set an IF statement
then filter on it. Something like this:
=IF(OR(B1="MT",B1="CX",C1=6,C1=1),"Match Pattern","No Match")
In my formula I am assuming the LG-0060-0703 data is in column A, and theMT
data is in column B and the integer is in column C. My formula therefore is
in D1
Then I set up an autofilter as I mentioned in my previous post and Select
"Match Pattern" as my filtering Criteria.
If you need the pattern to be restricted to match all 3 criterias rather
than only one, like in my formula, then change the OR for AND.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

:











- Mostrar texto citado -
Click to expand...

thanks,
im gonna try,
im in this all afternoon

ill be back.
 
Hi,

Here is another way:

Enter your criteria in a range, say you put the following into F1:G3

MT 6
CX 6
MT 1

Then in an empty column next to your data enter the formula
=SUMPRODUCT(--(B4&C4=$F$1:$F$3&$G$1:$G$3))
Where B4 contains the first cell of your data with one of the MT/CX entries
and C4 contains the first number entry. Copy the formula down. All cells
with 1's match one of the criteria all others do not. This formula can be
extended to handle as may conditions as you want.

You can use AutoFilter on this new column to display only the 1's.

NOTE: With the example data you gave us you could just use AutoFilter on
the MT/CX column and pick Custom and choose equal and enter MT, click the Or
choice and choose equal and enter CX.

But in general AutoFilter will not solve anything more complex than that,
instead you would need Advanced Filter.

If this helps, please click the Yes button.

cheers,
Shane Devenshire
 
Back
Top