Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
I've checked thru the Discussion group and found this subject has been asked
several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
If VLOOKUP isn't working I don't think VBA will be any better. There must be
something different with the data for VLOOKUP not to work. I would check the Tools - options - Calculation menu to see what the number of Iterations is set to. If the iteration n umber is set too low or you are set to manual calculation this may be the cause of the problem. "mathel" wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
If the Interation is different on different PCs, could it be a factor? IE: I
verified the Options on the PC I'm using in the office, it shows Maximum 100, I would have to check my PC that I use from home to see if there is a difference. On the other hand, there is 1 row I know is in both columns, and while VLookUp did not find the row, I found the following piece of VB that 'showed' the data. However, this code changes the text, rather than highlighting the cell. Unfortunately I know nothing about VBA, so don't know how to modify it so it would work to hightlight only - This example compares Column A to B in the same worksheet: Sub Replace_TExt() For i = 1 To ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row If Trim(ActiveSheet.Range("B" & i)) < "" Then ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End If Next i End Sub -- Linda "Joel" wrote: If VLOOKUP isn't working I don't think VBA will be any better. There must be something different with the data for VLOOKUP not to work. I would check the Tools - options - Calculation menu to see what the number of Iterations is set to. If the iteration n umber is set too low or you are set to manual calculation this may be the cause of the problem. "mathel" wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
I don't see how using VBA would help find an exact match if you can't do it
using formulas. I'd look for differences in the data. Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble If that doesn't help, what are you matching on--simple text, whole numbers, times, dates, fractions, long strings???? mathel wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
I found the problem! The data we are adding to worksheet 'Exposed' on a
daily basis is a 20 digit number, so the column in the worksheet is formatted to 'Text'. The data is being copied from a Word document, and we use 'Paste-Special-Text' into the spreadsheet. What I found was that if I double click the number in the Word doc, there is a space at the end of it On all numbers where there is a space at the end, VLOOKUP will not do a match. That being said, I did find a piece of code that found the number whether it had a space at the end of it or not. The problem is, this code is to replace the text in the found row. I do not know anything about VBA, so don't know how to modify it so that it would, lets say, change the cell interior to Yellow. The code is as follows: Sub Replace_TExt() For i = 1 To ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row If Trim(ActiveSheet.Range("B" & i)) < "" Then ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End If Next i End Sub Is it possible to have this code 'modified' to change the cell vs replace text? THANK YOU so much for all you help/comments in finding what the problem was with my ws. -- Linda "Dave Peterson" wrote: I don't see how using VBA would help find an exact match if you can't do it using formulas. I'd look for differences in the data. Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble If that doesn't help, what are you matching on--simple text, whole numbers, times, dates, fractions, long strings???? mathel wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
After reading both your comments and Dave Peterson's, I found the problem
with my data. When adding more data to the WS, it is copied from a Word doc. Some people (I think me mostly), double click the data to be copied, vs dragging across the info to hightlight & copy. The double click method will include a 'space' at the end of the data. VLOOKUP will not match/find any of the rows where the data has a space at the end. Any work-around in the formula, or different formula that can be used that will recognize the data with or without the space? The formula now used is: =VLOOKUP(A1,'Daily Transactions'!$A$:$A$19801,1,FALSE) The type of data I am using is a 20 digit number, the ws has the column(s) formatted as Text, and when copied from Word to the ws, we us Paste-special-text. Thanks -- Linda "Joel" wrote: If VLOOKUP isn't working I don't think VBA will be any better. There must be something different with the data for VLOOKUP not to work. I would check the Tools - options - Calculation menu to see what the number of Iterations is set to. If the iteration n umber is set too low or you are set to manual calculation this may be the cause of the problem. "mathel" wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
I'm not sure if this might be what you're looking for, but you can also
consider matching two arrays with each array being based on your two spreadsheet ranges. Compare each element of the first array to each element of the second array, and if it matches put the value in the spreadsheet. Option Explicit Sub MatchArrays() Dim arrExposed, arrTransactions As Variant With Sheets("Exposed") arrExposed = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value End With With Sheets("Transactions") arrTransactions = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value End With Dim i, j As Long For i = LBound(arrExposed, 1) To UBound(arrExposed, 1) For j = LBound(arrTransactions, 1) To UBound(arrTransactions, 1) If arrExposed(i, 1) = arrTransactions(j, 1) Then Debug.Print (arrExposed(i, 1)) Sheets("Exposed").Cells(i, 2).Value = arrExposed(i, 1) End If Next Next End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
Hi Linda,
Is Edit/Replace find:<space replace:<nothing not the easiest way to do? Wkr, JP "mathel" wrote in message ... I found the problem! The data we are adding to worksheet 'Exposed' on a daily basis is a 20 digit number, so the column in the worksheet is formatted to 'Text'. The data is being copied from a Word document, and we use 'Paste-Special-Text' into the spreadsheet. What I found was that if I double click the number in the Word doc, there is a space at the end of it On all numbers where there is a space at the end, VLOOKUP will not do a match. That being said, I did find a piece of code that found the number whether it had a space at the end of it or not. The problem is, this code is to replace the text in the found row. I do not know anything about VBA, so don't know how to modify it so that it would, lets say, change the cell interior to Yellow. The code is as follows: Sub Replace_TExt() For i = 1 To ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row If Trim(ActiveSheet.Range("B" & i)) < "" Then ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End If Next i End Sub Is it possible to have this code 'modified' to change the cell vs replace text? THANK YOU so much for all you help/comments in finding what the problem was with my ws. -- Linda "Dave Peterson" wrote: I don't see how using VBA would help find an exact match if you can't do it using formulas. I'd look for differences in the data. Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble If that doesn't help, what are you matching on--simple text, whole numbers, times, dates, fractions, long strings???? mathel wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
I ran this, unfortunately, for some reason it did not work, none of the rows
showed as a match. However, based on some other comments, I did find the problem with the data in the worksheet that VLookup wouldn't find a match, didn't realize it was so sensative. IE, some data had a space at the end of it (which would cause a #N/A result), whereas if it was exact, no problem, it matched it. I'm trying to find a workaround to the formula now to find a match whether there is a space at the end of it or not. THANK YOU FOR YOUR HELP! -- Linda "arjen van..." wrote: I'm not sure if this might be what you're looking for, but you can also consider matching two arrays with each array being based on your two spreadsheet ranges. Compare each element of the first array to each element of the second array, and if it matches put the value in the spreadsheet. Option Explicit Sub MatchArrays() Dim arrExposed, arrTransactions As Variant With Sheets("Exposed") arrExposed = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value End With With Sheets("Transactions") arrTransactions = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value End With Dim i, j As Long For i = LBound(arrExposed, 1) To UBound(arrExposed, 1) For j = LBound(arrTransactions, 1) To UBound(arrTransactions, 1) If arrExposed(i, 1) = arrTransactions(j, 1) Then Debug.Print (arrExposed(i, 1)) Sheets("Exposed").Cells(i, 2).Value = arrExposed(i, 1) End If Next Next End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
Yep, done this, and it resolved the problem. I guess we will have to do this
on a daily basis to ensure none of the users pasted data with a space included at the end. Thanks again for all your help. This website has to be one of the best sources of information and help the I have used to date! -- Linda "JP Ronse" wrote: Hi Linda, Is Edit/Replace find:<space replace:<nothing not the easiest way to do? Wkr, JP "mathel" wrote in message ... I found the problem! The data we are adding to worksheet 'Exposed' on a daily basis is a 20 digit number, so the column in the worksheet is formatted to 'Text'. The data is being copied from a Word document, and we use 'Paste-Special-Text' into the spreadsheet. What I found was that if I double click the number in the Word doc, there is a space at the end of it On all numbers where there is a space at the end, VLOOKUP will not do a match. That being said, I did find a piece of code that found the number whether it had a space at the end of it or not. The problem is, this code is to replace the text in the found row. I do not know anything about VBA, so don't know how to modify it so that it would, lets say, change the cell interior to Yellow. The code is as follows: Sub Replace_TExt() For i = 1 To ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row If Trim(ActiveSheet.Range("B" & i)) < "" Then ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End If Next i End Sub Is it possible to have this code 'modified' to change the cell vs replace text? THANK YOU so much for all you help/comments in finding what the problem was with my ws. -- Linda "Dave Peterson" wrote: I don't see how using VBA would help find an exact match if you can't do it using formulas. I'd look for differences in the data. Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble If that doesn't help, what are you matching on--simple text, whole numbers, times, dates, fractions, long strings???? mathel wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
Hi Arjen,
This is also a possible solution but in my opinion it will take more time to go through the routine. Suppose you have twice a range of 5000 entries and the half is matching, means that you have to go through the second array for 2500 times. This results already in 5000 x 2500 = 12.500.000 comparisons to go to through the second array because no match can be found. The other 2500 will statistical take 2500 x 2500 compares (6.250.000) In my first reply to this request, I've attached a zipped workbook with the use of a collection. Have a look and please let me know if this was of any help. To be honest, I've ran your code on my model and your code takes about 3 seconds, which is fast. The collection sub only needs 1 second. If you want to know more about collections, I can recommend reading Dermot Balson pages: http://www.westnet.net.au/balson/ModellingExcel/ Wkr, JP "arjen van..." wrote in message ... I'm not sure if this might be what you're looking for, but you can also consider matching two arrays with each array being based on your two spreadsheet ranges. Compare each element of the first array to each element of the second array, and if it matches put the value in the spreadsheet. Option Explicit Sub MatchArrays() Dim arrExposed, arrTransactions As Variant With Sheets("Exposed") arrExposed = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value End With With Sheets("Transactions") arrTransactions = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value End With Dim i, j As Long For i = LBound(arrExposed, 1) To UBound(arrExposed, 1) For j = LBound(arrTransactions, 1) To UBound(arrTransactions, 1) If arrExposed(i, 1) = arrTransactions(j, 1) Then Debug.Print (arrExposed(i, 1)) Sheets("Exposed").Cells(i, 2).Value = arrExposed(i, 1) End If Next Next End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
If all you're missing is that additional trailing space character, maybe you
could change your =vlookup() formula: =vlookup(a1&" ",sheet2!a:b,2,false) I hate "fixing" the problem in the formula, though. I would never remember to "fix" it all the other formulas. Maybe you could insert a new column and use a worksheet function like: =trim(a1) to remove the trailing space. Or in code: Dim myRng as range dim myCell as range dim wks as worksheet set wks = activesheet with wks set myrng = .range("b1",.cells(.rows.count,"B").end(xlup)) end with myrng.numberformat = "@" 'text for each mycell in myrng.cells mycell.value = trim(mycell.value) next mycell =============== Doing the edit|replace (even in code) will make those 20 digit text numbers into real numbers--and excel only keeps track of 15 significant digits. You may find that you have 5 zeros at the end of your number. mathel wrote: I found the problem! The data we are adding to worksheet 'Exposed' on a daily basis is a 20 digit number, so the column in the worksheet is formatted to 'Text'. The data is being copied from a Word document, and we use 'Paste-Special-Text' into the spreadsheet. What I found was that if I double click the number in the Word doc, there is a space at the end of it On all numbers where there is a space at the end, VLOOKUP will not do a match. That being said, I did find a piece of code that found the number whether it had a space at the end of it or not. The problem is, this code is to replace the text in the found row. I do not know anything about VBA, so don't know how to modify it so that it would, lets say, change the cell interior to Yellow. The code is as follows: Sub Replace_TExt() For i = 1 To ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row If Trim(ActiveSheet.Range("B" & i)) < "" Then ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End If Next i End Sub Is it possible to have this code 'modified' to change the cell vs replace text? THANK YOU so much for all you help/comments in finding what the problem was with my ws. -- Linda "Dave Peterson" wrote: I don't see how using VBA would help find an exact match if you can't do it using formulas. I'd look for differences in the data. Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble If that doesn't help, what are you matching on--simple text, whole numbers, times, dates, fractions, long strings???? mathel wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
Sorry, I thought the 'Find & Replace' resolved the problem, it did remove the
space, but the data in the rows (which are 20 digit numbers) reverted to something like 5.8834E+18 (on the Edit line, the last 4 digits are all zeros, so unfortunately, the problem is not yet resolved. -- Linda "mathel" wrote: Yep, done this, and it resolved the problem. I guess we will have to do this on a daily basis to ensure none of the users pasted data with a space included at the end. Thanks again for all your help. This website has to be one of the best sources of information and help the I have used to date! -- Linda "JP Ronse" wrote: Hi Linda, Is Edit/Replace find:<space replace:<nothing not the easiest way to do? Wkr, JP "mathel" wrote in message ... I found the problem! The data we are adding to worksheet 'Exposed' on a daily basis is a 20 digit number, so the column in the worksheet is formatted to 'Text'. The data is being copied from a Word document, and we use 'Paste-Special-Text' into the spreadsheet. What I found was that if I double click the number in the Word doc, there is a space at the end of it On all numbers where there is a space at the end, VLOOKUP will not do a match. That being said, I did find a piece of code that found the number whether it had a space at the end of it or not. The problem is, this code is to replace the text in the found row. I do not know anything about VBA, so don't know how to modify it so that it would, lets say, change the cell interior to Yellow. The code is as follows: Sub Replace_TExt() For i = 1 To ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row If Trim(ActiveSheet.Range("B" & i)) < "" Then ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End If Next i End Sub Is it possible to have this code 'modified' to change the cell vs replace text? THANK YOU so much for all you help/comments in finding what the problem was with my ws. -- Linda "Dave Peterson" wrote: I don't see how using VBA would help find an exact match if you can't do it using formulas. I'd look for differences in the data. Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble If that doesn't help, what are you matching on--simple text, whole numbers, times, dates, fractions, long strings???? mathel wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
Hi JP,
Thanks for the tip. There's some good material there. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
Hi Dave, you were right, removing the trailing space reverted the data back
to a number (like 5.88834E+18) making my workbook almost unusable. I tried both formulas you suggested, =trim(a1), does nothing and I have no idea why, the formula actually shows exactly as input as if it was text. The 1st formula "=vlookup(a1&" ",sheet2!a:b,2,false)", unfortunatly does not work either. It will not match any data, whether there is a space or not. I'm still trying to find a work-around the problem! -- Linda "Dave Peterson" wrote: If all you're missing is that additional trailing space character, maybe you could change your =vlookup() formula: =vlookup(a1&" ",sheet2!a:b,2,false) I hate "fixing" the problem in the formula, though. I would never remember to "fix" it all the other formulas. Maybe you could insert a new column and use a worksheet function like: =trim(a1) to remove the trailing space. Or in code: Dim myRng as range dim myCell as range dim wks as worksheet set wks = activesheet with wks set myrng = .range("b1",.cells(.rows.count,"B").end(xlup)) end with myrng.numberformat = "@" 'text for each mycell in myrng.cells mycell.value = trim(mycell.value) next mycell =============== Doing the edit|replace (even in code) will make those 20 digit text numbers into real numbers--and excel only keeps track of 15 significant digits. You may find that you have 5 zeros at the end of your number. mathel wrote: I found the problem! The data we are adding to worksheet 'Exposed' on a daily basis is a 20 digit number, so the column in the worksheet is formatted to 'Text'. The data is being copied from a Word document, and we use 'Paste-Special-Text' into the spreadsheet. What I found was that if I double click the number in the Word doc, there is a space at the end of it On all numbers where there is a space at the end, VLOOKUP will not do a match. That being said, I did find a piece of code that found the number whether it had a space at the end of it or not. The problem is, this code is to replace the text in the found row. I do not know anything about VBA, so don't know how to modify it so that it would, lets say, change the cell interior to Yellow. The code is as follows: Sub Replace_TExt() For i = 1 To ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row If Trim(ActiveSheet.Range("B" & i)) < "" Then ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End If Next i End Sub Is it possible to have this code 'modified' to change the cell vs replace text? THANK YOU so much for all you help/comments in finding what the problem was with my ws. -- Linda "Dave Peterson" wrote: I don't see how using VBA would help find an exact match if you can't do it using formulas. I'd look for differences in the data. Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble If that doesn't help, what are you matching on--simple text, whole numbers, times, dates, fractions, long strings???? mathel wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
Glad you appreciate
"arjen van..." wrote in message ... Hi JP, Thanks for the tip. There's some good material there. |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
My bet is that they're not really trailing spaces...
Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.aspx Then you can use this kind of formula: =substitute(a1,char(##),"") mathel wrote: Hi Dave, you were right, removing the trailing space reverted the data back to a number (like 5.88834E+18) making my workbook almost unusable. I tried both formulas you suggested, =trim(a1), does nothing and I have no idea why, the formula actually shows exactly as input as if it was text. The 1st formula "=vlookup(a1&" ",sheet2!a:b,2,false)", unfortunatly does not work either. It will not match any data, whether there is a space or not. I'm still trying to find a work-around the problem! -- Linda "Dave Peterson" wrote: If all you're missing is that additional trailing space character, maybe you could change your =vlookup() formula: =vlookup(a1&" ",sheet2!a:b,2,false) I hate "fixing" the problem in the formula, though. I would never remember to "fix" it all the other formulas. Maybe you could insert a new column and use a worksheet function like: =trim(a1) to remove the trailing space. Or in code: Dim myRng as range dim myCell as range dim wks as worksheet set wks = activesheet with wks set myrng = .range("b1",.cells(.rows.count,"B").end(xlup)) end with myrng.numberformat = "@" 'text for each mycell in myrng.cells mycell.value = trim(mycell.value) next mycell =============== Doing the edit|replace (even in code) will make those 20 digit text numbers into real numbers--and excel only keeps track of 15 significant digits. You may find that you have 5 zeros at the end of your number. mathel wrote: I found the problem! The data we are adding to worksheet 'Exposed' on a daily basis is a 20 digit number, so the column in the worksheet is formatted to 'Text'. The data is being copied from a Word document, and we use 'Paste-Special-Text' into the spreadsheet. What I found was that if I double click the number in the Word doc, there is a space at the end of it On all numbers where there is a space at the end, VLOOKUP will not do a match. That being said, I did find a piece of code that found the number whether it had a space at the end of it or not. The problem is, this code is to replace the text in the found row. I do not know anything about VBA, so don't know how to modify it so that it would, lets say, change the cell interior to Yellow. The code is as follows: Sub Replace_TExt() For i = 1 To ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row If Trim(ActiveSheet.Range("B" & i)) < "" Then ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End If Next i End Sub Is it possible to have this code 'modified' to change the cell vs replace text? THANK YOU so much for all you help/comments in finding what the problem was with my ws. -- Linda "Dave Peterson" wrote: I don't see how using VBA would help find an exact match if you can't do it using formulas. I'd look for differences in the data. Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble If that doesn't help, what are you matching on--simple text, whole numbers, times, dates, fractions, long strings???? mathel wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare columns
Hi Linda,
Based on the latest feedback... In transactions, following sample data: Row 1: 1 Row 2: 2 Row 3: =3 & " " & CHAR(9) Row 4: 4 .... Row 7: =7 & " " & CHAR(10) .... In Exposed: Row 1: 1 Row 2: 2 Row 3: 3 .... Row 9: =9 & " " & CHAR(12) The macro CompareData gives in column B 1 1 2 3 3 4 5 5 6 7 7 8 9 9 10 It has also found the 'duplicates' for 3, 7 and 9. Wkr, JP Sub CompareData() Dim colTransactions As Collection Dim varExposed As Variant Dim lngRow As Long Dim lngCount As Long ''' read the data Dim Timer As Date Dim varRes As Variant Timer = Now() Application.ScreenUpdating = False Set colTransactions = New Collection With Sheets("Transactions") .Activate lngRow = .Cells(1, 1).CurrentRegion.Rows.Count varExposed = .Range(Cells(1, 1), Cells(lngRow, 1)) For lngCount = 1 To lngRow colTransactions.Add Item:=varExposed(lngCount, 1), Key:=Trim(Application.Clean(CStr(varExposed(lngCou nt, 1)))) Next lngCount End With With Sheets("Exposed") .Activate .Columns("B").ClearContents lngRow = .Cells(1, 1).CurrentRegion.Rows.Count varExposed = .Range(Cells(1, 1), Cells(lngRow, 1)) End With On Error GoTo Eror_CompareData For lngCount = 1 To lngRow varRes = colTransactions(Trim(Application.Clean(CStr(varExp osed(lngCount, 1))))) ''' value iin Exposed found in Tranasactions Cells(lngCount, 2) = colTransactions(Trim(Application.Clean(CStr(varExp osed(lngCount, 1))))) Next_Exposed: Next lngCount Exit_CompareData: Debug.Print Now() - Timer Exit Sub Eror_CompareData: Resume Next_Exposed End Sub "mathel" wrote in message ... After reading both your comments and Dave Peterson's, I found the problem with my data. When adding more data to the WS, it is copied from a Word doc. Some people (I think me mostly), double click the data to be copied, vs dragging across the info to hightlight & copy. The double click method will include a 'space' at the end of the data. VLOOKUP will not match/find any of the rows where the data has a space at the end. Any work-around in the formula, or different formula that can be used that will recognize the data with or without the space? The formula now used is: =VLOOKUP(A1,'Daily Transactions'!$A$:$A$19801,1,FALSE) The type of data I am using is a 20 digit number, the ws has the column(s) formatted as Text, and when copied from Word to the ws, we us Paste-special-text. Thanks -- Linda "Joel" wrote: If VLOOKUP isn't working I don't think VBA will be any better. There must be something different with the data for VLOOKUP not to work. I would check the Tools - options - Calculation menu to see what the number of Iterations is set to. If the iteration n umber is set too low or you are set to manual calculation this may be the cause of the problem. "mathel" wrote: I've checked thru the Discussion group and found this subject has been asked several times, but can't find something I can use. I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and 'Transactions'. I need to compare Column A in ws 'Transactions' to Column a in ws 'Exposed'. If there is a match, have the data show on the same row in Column b on ws 'Exposed'. The number of rows on both ws can vary anywhere from 500 rows to as many as 5000. I know the simple answer is to use VLOOKUP, unfortunately, there are multiple users (and PCs) using this wb and for whatever reason the formula does NOT always work and the data is being missed so I am looking for VBA. Even if the data in Column A could not be put into Column B, I would be happy if the cell was highlighted. Thanks -- Linda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare columns | Excel Programming | |||
Compare two columns | Excel Programming | |||
Compare Two Columns | Excel Worksheet Functions | |||
Compare columns | Excel Discussion (Misc queries) | |||
Compare all following columns when value is same | Excel Programming |