PDA

View Full Version : Grocery lists



Tvercetti
10-16-2007, 10:55 AM
I have two grocery lists (in 2 columns) and have captured these in two arrays.
However, there are some items in the lists that appear in both list 1 and list 2.
How do I get the items that are in both lists seperated, maybe even copy and paste them on the sheet?
Is this possible?

Thanks

Bob Phillips
10-16-2007, 12:19 PM
What exactly do you want to do with the duplicates? The answer will affect the suggested solution.

Tvercetti
10-16-2007, 12:22 PM
Transfer the duplicates to another column on the same sheet if that's possible

Tvercetti
10-16-2007, 12:22 PM
by transfer I mean copy and paste, not cut existing data

Bob Phillips
10-16-2007, 12:31 PM
You could add a formula in another column like so

=COUNTIF(B:B,A2)

copy down and autofilter the new column for values > 1, then copy the visble cells in column A.

Tvercetti
10-16-2007, 12:46 PM
Sorry if for being too vague, will try to be more specific.
what I am hoping to achieve is have 3 arrays at the end of the sub. So array 1 would be the items in list 1 only, array 2 would be the items in list 2 only and array 3 would be the duplicates, i then want to show the 3 arrays in 2 columns beside the 2 lists.

lucas
10-16-2007, 01:56 PM
so you basically want a list of the duplicates as you already have the first two lists, right?

Tvercetti
10-16-2007, 02:24 PM
I have an array with the two full lists, but not sure how to get 3 arrays with the two lists exluding the duplicates and and one with the duplicates.
I want to use arrays for this problem.

lucas
10-16-2007, 02:28 PM
You keep talking about arrays.....how about posting some code....I'm confused.

Quickbeam
10-16-2007, 03:23 PM
You could use the find method to search for matches:

You would have the values in your first grocery list in B3:b10 and the 2nd list in C3:C20 and any matching items would go into column D:

You could search through the 2nd list with the items in your first list. Everytime you find a match you could add that item to your list in column D

You can look up the Find Method in the Excel Macro help for more information on how to use the method.