| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I compare,differentiate few columns data?
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
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) "CS" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
Sorry ignore that response, it was to another question entirely.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bob Phillips" wrote in message ... 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) "CS" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
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" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
"CS" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
I did
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "CS" wrote in message ... "CS" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
Hi Bob,
you mean you have replied me with some advice? I don't get it. thanks, CS "Bob Phillips" wrote in message ... I did -- HTH Bob Phillips (remove nothere from email address if mailing direct) "CS" wrote in message ... "CS" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
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" wrote in message ... Hi Bob, you mean you have replied me with some advice? I don't get it. thanks, CS "Bob Phillips" wrote in message ... I did -- HTH Bob Phillips (remove nothere from email address if mailing direct) "CS" wrote in message ... "CS" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
can anyone tell me whether I didn't explain it clearly or it's too tough ?
thanks, "CS" wrote in message ... 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
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" wrote in message ... can anyone tell me whether I didn't explain it clearly or it's too tough ? thanks, "CS" wrote in message ... 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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I compare,differentiate few columns data?
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 "Bob Phillips" wrote in message ... 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" wrote in message ... can anyone tell me whether I didn't explain it clearly or it's too tough ? thanks, "CS" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drawing data from specific columns of a dynamic range | Setting up and Configuration of Excel | |||
| MATCH UP DATA IN COLUMNS | Excel Worksheet Functions | |||
| how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
| How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
| How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
Hybrid Mode
