Data cleaning

[mira2020]

hi ,

I have problem as below :

Within each month, if same value cell in column A has different cell value in column B, it will change the cell value to column B .
Example, in sept, there are 4 cel in column A with value "Apple" but different cell value in column B . I want to change all these value to "High".
How to make it flexible such as if there is no "High", all values are "Medium" or "Low" then change to "Medium "

Here is the link to the file : Book2.xlsx

any idea ? Thanks

 

[mehidy1437]

try this,
Limitation, you have to select the range month-wise to get the month-wise result.

Put this code in a module, save the file as *.xlsm & use the formula shown below.

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
'Updateby Extendoffice
Dim rng As Range
Dim xResult As String, xResultFinal As String
xResult = ""
For Each rng In pWorkRng
    If rng = pValue Then
        xResult = xResult & "," & rng.Offset(0, pIndex - 1)
                If InStr(xResult, "High") > 0 Then
                    xResultFinal = "High"
                 ElseIf InStr(xResult, "Medium") > 0 Then
                    xResultFinal = "Medium"
                 Else
                    xResultFinal = "Low"
                End If
    End If
Next
MYVLOOKUP = xResultFinal
End Function

mira2020-Data cleaning.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Fruit	Priority	Month				Fruit	Priority	Month	Result
2	Apple	High	Sept				Apple	High	Sept	High	select the range for sept
3	Apple	High	Sept				Apple	High	Sept	High
4	Orange	Low	Sept				Orange	Low	Sept	Low
5	Banana	Medium	Sept				Banana	Medium	Sept	Medium
6	Apple	Low	Sept				Apple	High	Sept	High
7	Apple	Medium	Sept				Apple	High	Sept	High
8	Orange	High	Oct				Orange	High	Oct	High	select the range for oct
9	Apple	High	Oct				Apple	High	Oct	High
10	Orange	Low	Oct				Orange	High	Oct	High
11	Banana	Medium	Oct				Banana	Medium	Oct	Medium
12	Apple	Low	Oct				Apple	High	Oct	High
Sheet1

Cell Formulas
Range	Formula
J2:J7	J2	=MYVLOOKUP(G2,$A$2:$C$7,2)
J8:J12	J8	=MYVLOOKUP(G8,$A$8:$C$12,2)

 

[Sulprobil]

Why don't you use 1, 2, and 3 as priorities and return the max per fruit per month?

 

[mira2020]

what formula i can use ?

 

[mira2020]

i am using formula in cell D2 : =MAX(IF(A2:A9=A2,IF(B2:B9=B3,C2:C9))) , but it get the max of the whole column, not filtered for apple and sept

thanks.

 

[Sulprobil]

View attachment 23172

i am using formula in cell D2 : =MAX(IF(A2:A9=A2,IF(B2:B9=B3,C2:C9))) , but it get the max of the whole column, not filtered for apple and sept

thanks.

Google for Excel Maxif Contextures, for example:
Excel MIN and MAX Function Examples and Video
Even better could be a Pivot table. Google for Pivot table max per two groups:
How to find max or min value in a group in Excel?

 

[Sulprobil]

One further option, but I just use it as an advanced example for VBA:

sbMiniPivot | Sulprobil

Previous name was Pstat

berndplumhoff.gitbook.io

 

[mira2020]

thanks the pivot option is simple and great