Hi all,
Would like to find out how to do I use the Excel built-in functions to
- compare following item A amp; 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, quot;Qty matchedquot;, quot;Less
Qtyquot;, quot;Item A not foundquot;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 1006thanks.
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 = quot;H1:I1quot;
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, quot;Jquot;).Value = Me.Cells(.Row, quot;Hquot;).Value / _
Me.Cells(.Row, quot;Iquot;).Value
Me.Cells(.Row, quot;Jquot;).NumberFormat = quot;# / #quot;
sTmp = Replace(Me.Cells(.Row, quot;Jquot;).Text, quot; / quot;, quot;:quot;)
Me.Cells(.Row, quot;Jquot;).NumberFormat = quot;@quot;
Me.Cells(.Row, quot;Jquot;).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)
quot;CSquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; Would like to find out how to do I use the Excel built-in functions to
gt; - compare following item A amp; B columns data, if found matching, further
gt; comparing their Qty data
gt; -Qty B could be less than Qty A
gt; - thus display the result in new column with values eg, quot;Qty matchedquot;,
quot;Less
gt; Qtyquot;, quot;Item A not foundquot;
gt;
gt;
gt; item A Qty A item B Qty B
gt; BBF345 1248 BBF345 1248
gt; BGF378 1255 BGF378 1255
gt; HCZ733 1206 HCZ733 1206
gt; HCZ123 1241 HCZ123 1241
gt; HCZ267 1256 HCZ267 1256
gt; HJP300 1258 HJP300 1250
gt; HUP005 1258 HUP005 1258
gt; TRY001 1260 TRY001 1260
gt; HIT888 1259 HIT888 1208
gt; GOOD33 1259 GOOD33 1235
gt; WHY99 1234 WHY099 1178
gt; AAA876 1006
gt;
gt;
gt; thanks.
gt;
gt;
Sorry ignore that response, it was to another question entirely.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Bob Phillipsquot; gt; wrote in message
...
gt; Here is one way that works on values in two cells with the ratio in a
third,
gt; GF1,H1 and I1
gt;
gt; '-----------------------------------------------------------------
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; '-----------------------------------------------------------------
gt; Const WS_RANGE As String = quot;H1:I1quot;
gt; Dim sTmp As String
gt;
gt; On Error GoTo ws_exit:
gt; Application.EnableEvents = False
gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; With Target
gt; Me.Cells(.Row, quot;Jquot;).Value = Me.Cells(.Row, quot;Hquot;).Value / _
gt; Me.Cells(.Row, quot;Iquot;).Value
gt; Me.Cells(.Row, quot;Jquot;).NumberFormat = quot;# / #quot;
gt; sTmp = Replace(Me.Cells(.Row, quot;Jquot;).Text, quot; / quot;, quot;:quot;)
gt; Me.Cells(.Row, quot;Jquot;).NumberFormat = quot;@quot;
gt; Me.Cells(.Row, quot;Jquot;).Value = sTmp
gt; End With
gt; End If
gt;
gt; ws_exit:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;CSquot; gt; wrote in message
gt; ...
gt; gt; Hi all,
gt; gt;
gt; gt; Would like to find out how to do I use the Excel built-in functions to
gt; gt; - compare following item A amp; B columns data, if found matching, further
gt; gt; comparing their Qty data
gt; gt; -Qty B could be less than Qty A
gt; gt; - thus display the result in new column with values eg, quot;Qty matchedquot;,
gt; quot;Less
gt; gt; Qtyquot;, quot;Item A not foundquot;
gt; gt;
gt; gt;
gt; gt; item A Qty A item B Qty B
gt; gt; BBF345 1248 BBF345 1248
gt; gt; BGF378 1255 BGF378 1255
gt; gt; HCZ733 1206 HCZ733 1206
gt; gt; HCZ123 1241 HCZ123 1241
gt; gt; HCZ267 1256 HCZ267 1256
gt; gt; HJP300 1258 HJP300 1250
gt; gt; HUP005 1258 HUP005 1258
gt; gt; TRY001 1260 TRY001 1260
gt; gt; HIT888 1259 HIT888 1208
gt; gt; GOOD33 1259 GOOD33 1235
gt; gt; WHY99 1234 WHY099 1178
gt; gt; AAA876 1006
gt; gt;
gt; gt;
gt; gt; thanks.
gt; gt;
gt; gt;
gt;
gt;
To answer your question lt;vbggt;
=IF(ISNA(MATCH(C2,A:A,0)),quot;Item not
foundquot;,IF(INDEX(B:B,MATCH(C2,A:A,0))=D2,quot;Qty matchedquot;,quot;Less Qtyquot;))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;CSquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; Would like to find out how to do I use the Excel built-in functions to
gt; - compare following item A amp; B columns data, if found matching, further
gt; comparing their Qty data
gt; -Qty B could be less than Qty A
gt; - thus display the result in new column with values eg, quot;Qty matchedquot;,
quot;Less
gt; Qtyquot;, quot;Item A not foundquot;
gt;
gt;
gt; item A Qty A item B Qty B
gt; BBF345 1248 BBF345 1248
gt; BGF378 1255 BGF378 1255
gt; HCZ733 1206 HCZ733 1206
gt; HCZ123 1241 HCZ123 1241
gt; HCZ267 1256 HCZ267 1256
gt; HJP300 1258 HJP300 1250
gt; HUP005 1258 HUP005 1258
gt; TRY001 1260 TRY001 1260
gt; HIT888 1259 HIT888 1208
gt; GOOD33 1259 GOOD33 1235
gt; WHY99 1234 WHY099 1178
gt; AAA876 1006
gt;
gt;
gt; thanks.
gt;
gt;
quot;CSquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; Would like to find out how to do I use the Excel built-in functions to
gt; - compare following item A amp; B columns data, if found matching, further
gt; comparing their Qty data
gt; -Qty B could be less than Qty A
gt; - thus display the result in new column with values eg, quot;Qty matchedquot;,
gt; quot;Less Qtyquot;, quot;Item A not foundquot;
gt;
gt;
gt; item A Qty A item B Qty B
gt; BBF345 1248 BBF345 1248
gt; BGF378 1255 BGF378 1255
gt; HCZ733 1206 HCZ733 1206
gt; HCZ123 1241 HCZ123 1241
gt; HCZ267 1256 HCZ267 1256
gt; HJP300 1258 HJP300 1250
gt; HUP005 1258 HUP005 1258
gt; TRY001 1260 TRY001 1260
gt; HIT888 1259 HIT888 1208
gt; GOOD33 1259 GOOD33 1235
gt; WHY99 1234 WHY099 1178
gt; AAA876 1006
gt;
gt;
gt; thanks.
gt;
Anyone can provide some help on this ?
thanks,
CS
I did
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;CSquot; gt; wrote in message
...
gt;
gt; quot;CSquot; gt; wrote in message
gt; ...
gt; gt; Hi all,
gt; gt;
gt; gt; Would like to find out how to do I use the Excel built-in functions to
gt; gt; - compare following item A amp; B columns data, if found matching, further
gt; gt; comparing their Qty data
gt; gt; -Qty B could be less than Qty A
gt; gt; - thus display the result in new column with values eg, quot;Qty matchedquot;,
gt; gt; quot;Less Qtyquot;, quot;Item A not foundquot;
gt; gt;
gt; gt;
gt; gt; item A Qty A item B Qty B
gt; gt; BBF345 1248 BBF345 1248
gt; gt; BGF378 1255 BGF378 1255
gt; gt; HCZ733 1206 HCZ733 1206
gt; gt; HCZ123 1241 HCZ123 1241
gt; gt; HCZ267 1256 HCZ267 1256
gt; gt; HJP300 1258 HJP300 1250
gt; gt; HUP005 1258 HUP005 1258
gt; gt; TRY001 1260 TRY001 1260
gt; gt; HIT888 1259 HIT888 1208
gt; gt; GOOD33 1259 GOOD33 1235
gt; gt; WHY99 1234 WHY099 1178
gt; gt; AAA876 1006
gt; gt;
gt; gt;
gt; gt; thanks.
gt; gt;
gt;
gt; Anyone can provide some help on this ?
gt;
gt; thanks,
gt; CS
gt;
gt;
Hi Bob,
you mean you have replied me with some advice? I don't get it.
thanks,
CS
quot;Bob Phillipsquot; gt; wrote in message
...
gt;I did
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;CSquot; gt; wrote in message
gt; ...
gt;gt;
gt;gt; quot;CSquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi all,
gt;gt; gt;
gt;gt; gt; Would like to find out how to do I use the Excel built-in functions to
gt;gt; gt; - compare following item A amp; B columns data, if found matching, further
gt;gt; gt; comparing their Qty data
gt;gt; gt; -Qty B could be less than Qty A
gt;gt; gt; - thus display the result in new column with values eg, quot;Qty matchedquot;,
gt;gt; gt; quot;Less Qtyquot;, quot;Item A not foundquot;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; item A Qty A item B Qty B
gt;gt; gt; BBF345 1248 BBF345 1248
gt;gt; gt; BGF378 1255 BGF378 1255
gt;gt; gt; HCZ733 1206 HCZ733 1206
gt;gt; gt; HCZ123 1241 HCZ123 1241
gt;gt; gt; HCZ267 1256 HCZ267 1256
gt;gt; gt; HJP300 1258 HJP300 1250
gt;gt; gt; HUP005 1258 HUP005 1258
gt;gt; gt; TRY001 1260 TRY001 1260
gt;gt; gt; HIT888 1259 HIT888 1208
gt;gt; gt; GOOD33 1259 GOOD33 1235
gt;gt; gt; WHY99 1234 WHY099 1178
gt;gt; gt; AAA876 1006
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; thanks.
gt;gt; gt;
gt;gt;
gt;gt; Anyone can provide some help on this ?
gt;gt;
gt;gt; thanks,
gt;gt; CS
gt;gt;
gt;gt;
gt;
gt;
Yes I do. I posted this response 2 days ago
To answer your question lt;vbggt;
=IF(ISNA(MATCH(C2,A:A,0)),quot;Item not
foundquot;,IF(INDEX(B:B,MATCH(C2,A:A,0))=D2,quot;Qty matchedquot;,quot;Less Qtyquot;))--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;CSquot; gt; wrote in message
...
gt; Hi Bob,
gt; you mean you have replied me with some advice? I don't get it.
gt;
gt; thanks,
gt; CS
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt;I did
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;CSquot; gt; wrote in message
gt; gt; ...
gt; gt;gt;
gt; gt;gt; quot;CSquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Hi all,
gt; gt;gt; gt;
gt; gt;gt; gt; Would like to find out how to do I use the Excel built-in functions
to
gt; gt;gt; gt; - compare following item A amp; B columns data, if found matching,
further
gt; gt;gt; gt; comparing their Qty data
gt; gt;gt; gt; -Qty B could be less than Qty A
gt; gt;gt; gt; - thus display the result in new column with values eg, quot;Qty
matchedquot;,
gt; gt;gt; gt; quot;Less Qtyquot;, quot;Item A not foundquot;
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; item A Qty A item B Qty B
gt; gt;gt; gt; BBF345 1248 BBF345 1248
gt; gt;gt; gt; BGF378 1255 BGF378 1255
gt; gt;gt; gt; HCZ733 1206 HCZ733 1206
gt; gt;gt; gt; HCZ123 1241 HCZ123 1241
gt; gt;gt; gt; HCZ267 1256 HCZ267 1256
gt; gt;gt; gt; HJP300 1258 HJP300 1250
gt; gt;gt; gt; HUP005 1258 HUP005 1258
gt; gt;gt; gt; TRY001 1260 TRY001 1260
gt; gt;gt; gt; HIT888 1259 HIT888 1208
gt; gt;gt; gt; GOOD33 1259 GOOD33 1235
gt; gt;gt; gt; WHY99 1234 WHY099 1178
gt; gt;gt; gt; AAA876 1006
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; thanks.
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt; Anyone can provide some help on this ?
gt; gt;gt;
gt; gt;gt; thanks,
gt; gt;gt; CS
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
can anyone tell me whether I didn't explain it clearly or it's too tough ?
thanks,
quot;CSquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; Would like to find out how to do I use the Excel built-in functions to
gt; - compare following item A amp; B columns data, if found matching, further
gt; comparing their Qty data
gt; -Qty B could be less than Qty A
gt; - thus display the result in new column with values eg, quot;Qty matchedquot;,
gt; quot;Less Qtyquot;, quot;Item A not foundquot;
gt;
gt;
gt; item A Qty A item B Qty B
gt; BBF345 1248 BBF345 1248
gt; BGF378 1255 BGF378 1255
gt; HCZ733 1206 HCZ733 1206
gt; HCZ123 1241 HCZ123 1241
gt; HCZ267 1256 HCZ267 1256
gt; HJP300 1258 HJP300 1250
gt; HUP005 1258 HUP005 1258
gt; TRY001 1260 TRY001 1260
gt; HIT888 1259 HIT888 1208
gt; GOOD33 1259 GOOD33 1235
gt; WHY99 1234 WHY099 1178
gt; AAA876 1006
gt;
gt;
gt; thanks.
gt;
Tell me what is wrong with the response I have given you twice?
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;CSquot; gt; wrote in message
...
gt; can anyone tell me whether I didn't explain it clearly or it's too tough ?
gt;
gt; thanks,
gt;
gt; quot;CSquot; gt; wrote in message
gt; ...
gt; gt; Hi all,
gt; gt;
gt; gt; Would like to find out how to do I use the Excel built-in functions to
gt; gt; - compare following item A amp; B columns data, if found matching, further
gt; gt; comparing their Qty data
gt; gt; -Qty B could be less than Qty A
gt; gt; - thus display the result in new column with values eg, quot;Qty matchedquot;,
gt; gt; quot;Less Qtyquot;, quot;Item A not foundquot;
gt; gt;
gt; gt;
gt; gt; item A Qty A item B Qty B
gt; gt; BBF345 1248 BBF345 1248
gt; gt; BGF378 1255 BGF378 1255
gt; gt; HCZ733 1206 HCZ733 1206
gt; gt; HCZ123 1241 HCZ123 1241
gt; gt; HCZ267 1256 HCZ267 1256
gt; gt; HJP300 1258 HJP300 1250
gt; gt; HUP005 1258 HUP005 1258
gt; gt; TRY001 1260 TRY001 1260
gt; gt; HIT888 1259 HIT888 1208
gt; gt; GOOD33 1259 GOOD33 1235
gt; gt; WHY99 1234 WHY099 1178
gt; gt; AAA876 1006
gt; gt;
gt; gt;
gt; gt; thanks.
gt; gt;
gt;
gt;
留言列表
