Finding new entries in list

[Greg]

Hi
I have made a list with a web query.
It is made up of columns A,B,C,D and E.
I wish to update the web query every couple of days
and paste the new list into the next available columns F,G,H,I and J.
I understand how to find the next available column with a VB script,
which I have seen in other posts, but how to find and paste A into F, B
into G etc for the next 5?

I then wish to compare the two lists ie ABCDE and FGHIJ for new entries
and then paste them to a new sheet. The
=if(countif($B:$B,A1)=0,"Only in A","")seems to be only for comparing
two columns to each other. Can it be modified to check the first 5
columns to the next 5 columns.
Thanks
Greg

 

[Tom Ogilvy]

Doesn't make much sense Greg. Maybe you should try explaining again.
If you build a new 5 column web query, why are you copying over from the
last one.

Do you mean to copy A:E over to F:J as an archive, then refreshing the web
query so the new data is in A:E

columns(1).Resize(,5).copy
Range("F1").PasteSpecial xlValues

What constitutes a new entry - or what constitutes a match (opposite of a
new entry)? All five columns must match?

 

[Greg]

Hi Tom
I will try to explain what I mean,
Yes I wish to copy A:E to F:J as an archive and compare all five
columns so that each of the five must match the other five. eg in this
case the record I wish to find is in row 3 it is the one that doesn't
have a match from the previous web query.
A B C D E F G H I J
Row 1 12 ca to 4 ta 22 df re 3 sa
Row 2 22 df re 3 sa 12 ca to 4 ta
Row 3 1 dd fr 7 hs

The web query produces a list in which some entries drop off and are
replaced with new entries. Each time it is refreshed it has a different
number of rows. The entries that drop off come from different parts of
the list e.g .on one day row 5 and row 58 might drop off to be replaced
by new entries which slots into row 35,2,17 and 40.
The next time the web query is refreshed the rows which drop off and
new entries which slot in will again be on different rows. I hope that
this is clearer.
Thanks
Greg

 

[Tom Ogilvy]

I gave you code to copy the date.


Next you could use the next two columns to concatenate the data

L1 for example could be

=A1&B1&C1&D1&E1
M1 would be
=F1&G1&H1&I1&J1

then fill those down for as far as you have values in column A.

Then use a third column to put in your countif formula comparing L to M.
That will identify the rows you need to copy. Once copied, you can delete
these 3 columns. Of course you can do all this in code.

 

[Greg]

Hi Tom
Thankyou once again, I am greatful for your assistance, I have to say
that while the slow process of learning takes place I often find
myself looking at the totally wrong approach to solve problems.....I
look for the long way around and am sometimes blinded to the simple
solutions. It's brilliant that there are so many people in this
environment to look for help beyond my keyboard and scrap pieces of
paper which I use to solve my problems.
Thankyou
Greg

 

[Greg]

Hi Tom
Thankyou once again, I am greatful for your assistance, I have to say
that while the slow process of learning takes place I often find
myself looking at the totally wrong approach to solve problems.....I
look for the long way around and am sometimes blinded to the simple
solutions. It's brilliant that there are so many people in this
environment to look for help beyond my keyboard and scrap pieces of
paper which I use to solve my problems.
Thankyou
Greg