Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in Excel with VBA
Hi all,
I was wondering if there was a way to possibly do the following items in Excel with VBA: 1) With two rows of data in excel (i.e. Column A being an item number and Column B being a quantity), is there a way to say if Column B (the quantity) is equal to zero, then either copy/export etc. the contents to the left of that quantity (i.e. if the quantity in column B3 was zero, then it would refer to A3) would be copied to a text file or possibly another excel sheet? 2) Another thing I worked on was how to do a comparison of items that I have. Let's say I have an item list from today, and one from yesterday. Yesterday's list has some new and missing items compared to today's list. To compare the two, I do this: My spreadsheet has two columns, one that I put yesterday's item number into (A) and one that I put today's numbers into (B). The third column (C) has (for 3500 rows) the fomula "=VLOOKUP(B2,C:C,1,FALSE)" which will cause the spreadsheet to look in column C for the value located in that particular cell. If it finds the value, it will display that value in the cell. If it does not find that value, it displays "#N/A" in the cell. I have one or two other formulas to make a virtual "marker" that the number is missing from the other sheet. This also makes it difficult since I have to scroll through the list to see which items no longer exist. Is there an easier way of doing all of this? I'm assuming with VBA, but I cannot find the resources I'm looking for anywhere I look. If you anyone needs more detail, please let me know. Also, if anyone knows a resource with this information already outlined, that would be great also. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in Excel with VBA
Hi
1) The following will scroll down the list of values and if it is 0, then it will output the value from column A into column E. While this is on the same sheet, this can be modified to put into a different column, a different sheet in the same workbook, or a different workbook. It is possible to put to a text file, but as you are working in Excel, may as well keep it there. Sub aaa() countt = 0 outer = Range("e1").Address Range("b1").Select While Not IsEmpty(ActiveCell) If ActiveCell = 0 Then Range(outer).Offset(countt, 0).Value = ActiveCell.Offset(0, -1) countt = countt + 1 End If ActiveCell.Offset(1, 0).Select Wend End Sub 2) Not sure of your column notations here. I've assumed that yesterday's listing is in column A, todays is in column B and the vlookup is in column C checking yesterday and today. The code below will scroll down Column C and if it finds the error, it will put the value from A into column E. Very similar to the code above. Sub bbb() countt = 0 outer = Range("e1").Address Range("c1").Select While Not IsEmpty(ActiveCell) If WorksheetFunction.IsNA(ActiveCell) Then Range(outer).Offset(countt, 0).Value = ActiveCell.Offset(0, -2) countt = countt + 1 End If ActiveCell.Offset(1, 0).Select Wend End Sub Tony ----- Merlin63 wrote: ----- Hi all, I was wondering if there was a way to possibly do the following items in Excel with VBA: 1) With two rows of data in excel (i.e. Column A being an item number and Column B being a quantity), is there a way to say if Column B (the quantity) is equal to zero, then either copy/export etc. the contents to the left of that quantity (i.e. if the quantity in column B3 was zero, then it would refer to A3) would be copied to a text file or possibly another excel sheet? 2) Another thing I worked on was how to do a comparison of items that I have. Let's say I have an item list from today, and one from yesterday. Yesterday's list has some new and missing items compared to today's list. To compare the two, I do this: My spreadsheet has two columns, one that I put yesterday's item number into (A) and one that I put today's numbers into (B). The third column (C) has (for 3500 rows) the fomula "=VLOOKUP(B2,C:C,1,FALSE)" which will cause the spreadsheet to look in column C for the value located in that particular cell. If it finds the value, it will display that value in the cell. If it does not find that value, it displays "#N/A" in the cell. I have one or two other formulas to make a virtual "marker" that the number is missing from the other sheet. This also makes it difficult since I have to scroll through the list to see which items no longer exist. Is there an easier way of doing all of this? I'm assuming with VBA, but I cannot find the resources I'm looking for anywhere I look. If you anyone needs more detail, please let me know. Also, if anyone knows a resource with this information already outlined, that would be great also. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in Excel with VBA
Hello
The answer to the first part is a macro - this copies data from sheet2 to sheet3 (change to suit) Sub CopyZeros() Dim c Dim i As Long, nr As Long, r As Long Dim rng As Range, dest As Range nr = Sheets("Sheet2").UsedRange.Rows.Count Set rng = Range(Cells(2, 2), Cells(nr, 2)) For Each c In rng If IsEmpty(c) Or c = 0 Then r = Application.WorksheetFunction.CountA(Worksheets (3) _.Range("A:A")) + 1 Set dest = Worksheets(3).Cells(r + 1, 1) c.Offset(, -1).Copy dest End If Next c End Sub -----Original Message----- Hi all, I was wondering if there was a way to possibly do the following items in Excel with VBA: 1) With two rows of data in excel (i.e. Column A being an item number and Column B being a quantity), is there a way to say if Column B (the quantity) is equal to zero, then either copy/export etc. the contents to the left of that quantity (i.e. if the quantity in column B3 was zero, then it would refer to A3) would be copied to a text file or possibly another excel sheet? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in Excel with VBA
Hi
I'm not sure what you want to do when comparing the data as you are likely to have a lot of items that are either new or not applicable for todays list. It is possible to copy the list of these items within the same sheet or to another. Presumably, with would make it easier to view? Remove nothere when replying direct Peter Atherton -----Original Message----- Hi all, I was wondering if there was a way to possibly do the following items in Excel with VBA: 1) With two rows of data in excel (i.e. Column A being an item number and Column B being a quantity), is there a way to say if Column B (the quantity) is equal to zero, then either copy/export etc. the contents to the left of that quantity (i.e. if the quantity in column B3 was zero, then it would refer to A3) would be copied to a text file or possibly another excel sheet? 2) Another thing I worked on was how to do a comparison of items that I have. Let's say I have an item list from today, and one from yesterday. Yesterday's list has some new and missing items compared to today's list. To compare the two, I do this: My spreadsheet has two columns, one that I put yesterday's item number into (A) and one that I put today's numbers into (B). The third column (C) has (for 3500 rows) the fomula "=VLOOKUP (B2,C:C,1,FALSE)" which will cause the spreadsheet to look in column C for the value located in that particular cell. If it finds the value, it will display that value in the cell. If it does not find that value, it displays "#N/A" in the cell. I have one or two other formulas to make a virtual "marker" that the number is missing from the other sheet. This also makes it difficult since I have to scroll through the list to see which items no longer exist. Is there an easier way of doing all of this? I'm assuming with VBA, but I cannot find the resources I'm looking for anywhere I look. If you anyone needs more detail, please let me know. Also, if anyone knows a resource with this information already outlined, that would be great also. Thanks! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in Excel with VBA
To copy it to another sheet would be great. The only problem I found
is that if I copy the data to another sheet, there is a possibility that the column with all of the data (that would contain the items not found) would have quite a few empty cells in it. I would need to have another macro that would take all of the blank cells, remove them and condense the results in the one column (obviously making it easier to view). How hard is this to do with VBA? Also, thanks very much for the help of everyone... I never would have guessed that I would have received this much of a response! On Mon, 19 Jan 2004 19:01:22 -0800, "Peter Atherton" wrote: It is possible to copy the list of these items within the same sheet or to another. Presumably, with would make it easier to view? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in Excel with VBA
I've been actually using that name for quite awhile now. Basically
pulled from the "Merlin" that was an advisor/magician/prophet for King Arthur. On Tue, 20 Jan 2004 04:49:55 -0800, "MWE" wrote: Merlin63: it looks like you already have an answer to your quetsion. My reply is really a question for you. Why the "name" Merlin63? Everytime I encounter a "Merlin" I try to find out why the person chose that name? regards -----Original Message----- Hi all, I was wondering if there was a way to possibly do the following items in Excel with VBA: 1) With two rows of data in excel (i.e. Column A being an item number and Column B being a quantity), is there a way to say if Column B (the quantity) is equal to zero, then either copy/export etc. the contents to the left of that quantity (i.e. if the quantity in column B3 was zero, then it would refer to A3) would be copied to a text file or possibly another excel sheet? 2) Another thing I worked on was how to do a comparison of items that I have. Let's say I have an item list from today, and one from yesterday. Yesterday's list has some new and missing items compared to today's list. To compare the two, I do this: My spreadsheet has two columns, one that I put yesterday's item number into (A) and one that I put today's numbers into (B). The third column (C) has (for 3500 rows) the fomula "=VLOOKUP (B2,C:C,1,FALSE)" which will cause the spreadsheet to look in column C for the value located in that particular cell. If it finds the value, it will display that value in the cell. If it does not find that value, it displays "#N/A" in the cell. I have one or two other formulas to make a virtual "marker" that the number is missing from the other sheet. This also makes it difficult since I have to scroll through the list to see which items no longer exist. Is there an easier way of doing all of this? I'm assuming with VBA, but I cannot find the resources I'm looking for anywhere I look. If you anyone needs more detail, please let me know. Also, if anyone knows a resource with this information already outlined, that would be great also. Thanks! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing data in excel | Excel Worksheet Functions | |||
Comparing Data in Excel 2003 | Excel Worksheet Functions | |||
Comparing two Excel data ranges for chnages... | Excel Discussion (Misc queries) | |||
Excel data comparing | Excel Worksheet Functions | |||
Comparing Data from two excel sheets! | Excel Worksheet Functions |