Comparing multiple fields in an array with multiple fields in a table.

[lisamariechemistry]

I have an array of n records with 2 fields (say, "Name" and "Date"),
and a worksheet table of many rows (eventually thousands) and many
columns, including the two columns in the array. I need to compare
each record in the array to find out if that Name/Date combo already
exists in any row of the spreadsheet table. (...and add it to the
table if not already represented but that's the easy part) Although
the number of rows in the table will become large, n will usually be
<10.

I can think of various ways to proceed (using various permutations of
autofilter, .find, concatenating, etc.) but none are particularly
elegant.

Is there one approach that is more programmatically "correct" than the
others?

I know enough to get the job done but it might not be very pretty.
I'm teaching myself as I go and I'd like to teach myself "right" if
there's a "right" way. Thanks! -Lisa

 

[Tom Ogilvy]

for each row in the array,
search all the cells of the table using the find command for the name or
date which you expect to be present in the fewest number of rows.
for each row that contains that value, use the find command to find the
other value (date or name). Each time a row contains both, do what you need
to do.

For autofilter, use a dummy column put in two countifs - one for name, one
for date - and sum the results. Then autofilter on that column for value of
2.
you could then autofilter on the dummy column. You can have the second
argument of each countif refer to a cell, then enter you name and date as you
loop through the array - applying the autofilter each time.

Try both methods and see which works best.

 

[lisamariechemistry]

Thanks! I'm going with the first method you described. I didn't run
any timed tests but I prefer not to add extra columns when it can be
avoided.

-Lisa