In column A I enter purchase order numbers, in B part numbers and descriptions in C. I need to copy the data in columns B and C without the duplicates. I have many duplicates in B and C due to having the same part numbers/descriptions on various purchase orders. I'm going to continue adding data to these 3 columns. I haven't successfully created a formula to copy the contents of columns B and C without the duplicates. Any ideas?

Hi,

Am Mon, 8 Jul 2013 11:41:13 +0100 schrieb DConstance:

In column A I enter purchase order numbers, in B part numbers and
descriptions in C. I need to copy the data in columns B and C without
the duplicates. I have many duplicates in B and C due to having the same
part numbers/descriptions on various purchase orders. I'm going to
continue adding data to these 3 columns. I haven't successfully created
a formula to copy the contents of columns B and C without the
duplicates. Any ideas?

try advanced filter without duplicates.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

DConstance Wrote in message:

In column A I enter purchase order numbers, in B part numbers and
descriptions in C. I need to copy the data in columns B and C without
the duplicates. I have many duplicates in B and C due to having the same
part numbers/descriptions on various purchase orders. I'm going to
continue adding data to these 3 columns. I haven't successfully created
a formula to copy the contents of columns B and C without the
duplicates. Any ideas?

I think there is no Chance to do this by formula only.
After copying by formula you will have to remove duplicates by the
menufunction in menu's "Data" section
I suppose, dringend this by VBA would bei mich easyer
--

In column A I enter purchase order numbers, in B part numbers an
descriptions in C. I need to copy the data in columns B and C without
the duplicates. I have many duplicates in B and C due to having the same
part numbers/descriptions on various purchase orders. I'm going to
continue adding data to these 3 columns. I haven't successfully created
a formula to copy the contents of columns B and C without the
duplicates.

One way is to use helper columns. Below, E and F are helper columns. The results are columns G and H; namely, the unique pairs of part numbers and descriptions, with no gaps. I'm assuming the original data starts in row 2.

In E2, put
=B2& " ~ " & C2
This concatenates the part numbers and descriptions for detecting duplicates.

In F1 put the number 1 (one).

In F2 put
=IF(COUNTIF(E$1:E1,E2)=0,MAX(F$1:F1)+1,"")
This flags the first of each unique pair, and numbers them sequentially.

In G2 put
=IF(ROW()MAX(F:F),"",INDEX(B:B,MATCH(ROW(),F:F,0) ))
This gets the sequential flagged part number corresponding to this row.

In H2 put
=IF(ROW()MAX(F:F),"",INDEX(C:C,MATCH(ROW(),F:F,0) ))
This gets the sequential flagged description corresponding to this row.

Select E2:H2 and copy down past the end of the data.

Columns F and G can be hidden to eliminate clutter.

Modify as needed. Hope this helps getting started.