Comparing data in Excel with VBA

[Merlin63]

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!

 

[Guest]

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!

 

[Peter Atherton]

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

 

[Peter Atherton]

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

 

[Merlin63]

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!

 

[MWE]

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

 

[Merlin63]

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.