How do I compare,differentiate few columns data?

[CS]

Hi all,

Would like to find out how to do I use the Excel built-in functions to
- compare following item A & B columns data, if found matching, further
comparing their Qty data
-Qty B could be less than Qty A
- thus display the result in new column with values eg, "Qty matched", "Less
Qty", "Item A not found"


item A Qty A item B Qty B
BBF345 1248 BBF345 1248
BGF378 1255 BGF378 1255
HCZ733 1206 HCZ733 1206
HCZ123 1241 HCZ123 1241
HCZ267 1256 HCZ267 1256
HJP300 1258 HJP300 1250
HUP005 1258 HUP005 1258
TRY001 1260 TRY001 1260
HIT888 1259 HIT888 1208
GOOD33 1259 GOOD33 1235
WHY99 1234 WHY099 1178
AAA876 1006


thanks.

 

[Bob Phillips]

Here is one way that works on values in two cells with the ratio in a third,
GF1,H1 and I1

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:I1"
Dim sTmp As String

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "J").Value = Me.Cells(.Row, "H").Value / _
Me.Cells(.Row, "I").Value
Me.Cells(.Row, "J").NumberFormat = "# / #"
sTmp = Replace(Me.Cells(.Row, "J").Text, " / ", ":")
Me.Cells(.Row, "J").NumberFormat = "@"
Me.Cells(.Row, "J").Value = sTmp
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Sorry ignore that response, it was to another question entirely.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

To answer your question <vbg>

=IF(ISNA(MATCH(C2,A:A,0)),"Item not
found",IF(INDEX(B:B,MATCH(C2,A:A,0))=D2,"Qty matched","Less Qty"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[CS]

Hi all,

Would like to find out how to do I use the Excel built-in functions to
- compare following item A & B columns data, if found matching, further
comparing their Qty data
-Qty B could be less than Qty A
- thus display the result in new column with values eg, "Qty matched",
"Less Qty", "Item A not found"


item A Qty A item B Qty B
BBF345 1248 BBF345 1248
BGF378 1255 BGF378 1255
HCZ733 1206 HCZ733 1206
HCZ123 1241 HCZ123 1241
HCZ267 1256 HCZ267 1256
HJP300 1258 HJP300 1250
HUP005 1258 HUP005 1258
TRY001 1260 TRY001 1260
HIT888 1259 HIT888 1208
GOOD33 1259 GOOD33 1235
WHY99 1234 WHY099 1178
AAA876 1006


thanks.

Anyone can provide some help on this ?

thanks,
CS

 

[Bob Phillips]

I did

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[CS]

Hi Bob,
you mean you have replied me with some advice? I don't get it.

thanks,
CS

 

[Bob Phillips]

Yes I do. I posted this response 2 days ago

To answer your question <vbg>

=IF(ISNA(MATCH(C2,A:A,0)),"Item not
found",IF(INDEX(B:B,MATCH(C2,A:A,0))=D2,"Qty matched","Less Qty"))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[CS]

can anyone tell me whether I didn't explain it clearly or it's too tough ?

thanks,

 

[Bob Phillips]

Tell me what is wrong with the response I have given you twice?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[CS]

Hi Bob,

I have sent you email and wish to get your response through email. I don't
know why I can read this response from you but without able to see your
solution to my question.

thanks very much,
CS